SQLクライアントはこれひとつでOK！ あらゆるDBMSで使える「SQL Workbench/J」をデータ解析で活用しよう

ビジネスデータ解析のためのSQL入門 第7回

データベース接続とSQLクエリの実行

　データベース接続の設定方法を確認し、SQLクエリを実行する方法を見ていきます。

さまざまなデータベースに接続する

　データベース接続の設定をします。SQL Workbench/Jを起動すると下記のようなDBへの接続情報を求められますので、必要情報を入力していきます。

DB接続プロファイル

　SQL Workbench/J で JDBC 接続を使用する手順は下記のような流れになります。

1. ［Manage Drivers］をクリックしダイアログの［Name］ ボックスにドライバの名前を入力します。
2. ［Library］ボックス横のフォルダアイコンをクリックし、先ほどダウンロードしたJDBCドライバの場所まで移動してそのドライバを選択します。
3. ［Driver］ボックスで、追加したドライバを選択します。
4. ［Username］［Password」にそれぞれDBMS接続のユーザー名/パスワードを入力します。
5. ［Autocommit］ボックスをオンにします。これをオンにすることでDML（UPDATEなど）や一部DDL（CREATE TABLEなど）の実行に必要なDBMSでのcommitが自動で行われます。
6. ［Save profile list］アイコンをクリックしダイアログを終了します。

　SQL Workbench/J単体では、DBMSに接続できないため、DBMSに合わせたJDBCドライバを事前に入手する必要があります。DBMSの種類を選択した際にJDBCドライバを要求する画面が表示されるので、適切なJDBCドライバのファイルの場所を指定してください。

　PostgreSQL以外のDBに接続する場合も同様にJDBCドライバを指定することで接続が可能です。JDBCの入手についてはウェブ上で検索するか、SQL Workbench/Jマニュアル内に情報がまとまっているので参考になります。

• 6. JDBC Drivers

基本的なSQLの実行

　本連載第2回、「SQLでバスケット分析と時系列分析を用いたデータ解析に挑戦する」で用いた接続を実行してみます。クエリは基本的に「Statement」と表示された入力フォームにSQLを記述します。

　ここでは下記のようなテーブルが事前に設定してあるものと仮定します。

購買履歴

　まずは「Statement1」にSQLを入力し実行し、簡単なSQLが実行できることを確認します。select * from sales_history order by date desc;を実行してみます。SQLの実行は左上のボタンをクリックします。

　クエリ結果のテーブルが下の「Message」と表示された領域に、上記と同じ表が表示されたら成功です。

SQL Workbench/JによるSQL分析の流れ

　次に、連載第5回「ユーザーの年齢・性別と購買履歴を活用する「属性」×「行動」分析のSQL」で紹介したユーザー分析のSQLを実行してみましょう。SQLの一部は以下の通りです。

WITH sq_users_int_birthday AS /* ① */
(
  SELECT *,
         20171001 AS benchmark_date,
         CAST(REPLACE(birthday,'-','') AS INTEGER) AS int_birth_date
  FROM customer_with_birthday
),
sq_users_age AS
(
  SELECT
        *,
        floor((benchmark_date - int_birth_date )
    FROM sq_users_int_birthday
),
sq_users_category AS (
    SELECT
        user_id
        ,sex
        ,age
        ,concat(
            CASE
                WHEN 20 <= age THEN sex ELSE ''
            END
            ,CASE
                WHEN age >= 4 AND age <= 12 THEN 'C'
                WHEN age >= 13 AND age <= 19 THEN 'T'
                WHEN age >= 20 AND age <= 34 THEN '1'
                WHEN age >= 35 AND age <= 49 THEN '2'
                WHEN age >= 50 THEN '3'
            END
        )AS age_sex_category
    FROM
        sq_users_age
)
SELECT
    a.item_category
    ,u.age_sex_category
    ,COUNT(*) AS purchase_count
FROM action_history AS a
    JOIN sq_users_category AS u
    ON a.user_id = u.user_id
WHERE a.action_category = 'purchase'
GROUP BY a.item_category, u.age_sex_category
ORDER BY a.item_category, u.age_sex_category
;

　このSQLでは下図のようにテーブルが取得できます。

ユーザー分析SQLの結果

　こういったサブクエリを含む長いSQL文も、実際には作成時はサブクエリごとに段階的に作成しながら進めます。例えば上記SQLの①のサブクエリは、ユーザーの年齢を算出する前処理として生年月日を8桁の整数に変換する部分です。

　まずはこの部分のみSQLを作成し、結果を確認します。

SQL記述タブ

　上記のようにSQLを記述するタブを複数にしておくことで、あとで全体クエリの結果やサブクエリの結果それぞれ参照する際に確認が楽になります。

　タブの追加は「View」＞「Add tab」から行います。