データ分析で役に立つSQLのテクニック

2021年3月17日 17:54

こんにちは。
NOBORIで開発をしている吉田です。PHRアプリの開発に携わっています。

NOBORIではユーザーの使用状況の把握・分析のためにBIツールを導入しています。
今回は、BIツールでデータを可視化する際に知っていると役に立つ、データ分析のためのSQLを紹介します。
データ分析のためとは言っても基本は普通のSQLと同じなのですが、データの並べ方の工夫など普段アプリケーションの開発ではあまり使わないテクニックも多いかと思います。

なお、DBはPostgreSQLを使用するものとします。

1. 集約した値と集約前の値を同時に表示する

ケース設定として、ユーザーが商品を星の数でレビューをするreviewテーブルがあるとします。

SELECT *
FROM review;


| user_id | product_id | score |
|---------|------------|-------|
| 0001    | A101       |     1 |
| 0002    | A102       |     2 |
| 0003    | A101       |     5 |
| 0001    | A104       |     4 |
| 0001    | A103       |     1 |
| 0003    | A102       |     4 |
| 0002    | A101       |     5 |

商品ごとのレビュー数、平均点を求めましょう。COUNT関数、AVG関数を使うことで計算できます。

SELECT
   product_id,
   COUNT(*),
   -- AVG関数の計算結果を小数点第2位で丸めるためにROUND関数を使用
   ROUND(AVG(score), 2) AS avg
FROM
   review
GROUP BY
   product_id;


| product_id | count | avg  |
|------------|-------|------|
| A101       |     3 | 3.67 |
| A102       |     2 | 3.00 |
| A103       |     1 | 1.00 |
| A104       |     1 | 4.00 |

このようなAVGのような関数を集約関数と言います。
集約関数には制約があり、SELECT句に指定するカラムとGROUP BY句に指定するカラムを合わせる必要があります。
上の例の場合、SELECT句にはuser_idを指定することができません。

しかし個別のデータと集約した値を同時に表示させたいときもあります。
それを解決するのがウィンドウ関数です。ウィンドウ関数を使えば、個別の点数と平均点数を簡単に比較できるようになります。

SELECT
   user_id,
   product_id,
   score,
   -- 各プロダクトごとの平均点数
   ROUND(AVG(score) OVER(PARTITION BY product_id), 2) AS avg,
   -- 個別のユーザーによる点数と平均点数との差
   score - ROUND(AVG(score) OVER(PARTITION BY product_id), 2) AS diff
FROM
   review;


| user_id | product_id | score | avg  | diff  |
|---------|------------|-------|------|-------|
| 0001    | A101       |     1 | 3.67 | -2.67 |
| 0002    | A101       |     5 | 3.67 |  1.33 |
| 0003    | A101       |     5 | 3.67 |  1.33 |
| 0002    | A102       |     2 | 3.00 | -1.00 |
| 0003    | A102       |     4 | 3.00 |  1.00 |
| 0001    | A103       |     1 | 1.00 |  0.00 |
| 0001    | A104       |     4 | 4.00 |  0.00 |

ウィンドウ関数は私は開発で使ったことがなく、データ分析で初めて使いました。
ウィンドウ関数を使いこなせるとプログラムを書かなくてもSQLだけでできることがグッと広がります。

2. イベントの時間間隔を確認する

よくあるのがイベントの時間間隔を確認したいことです。例えばどれくらいの間隔でアプリが使われているのか、どれくらいの間隔で商品が購入されているのかといったケースです。

ケースとして各イベントを記録するeventテーブルを想定します。

SELECT *
FROM event;

| event_time          | user_id | event_name |
|---------------------|---------|------------|
| 2021-03-16 12:00:00 | U001    | login      |
| 2021-03-16 13:30:00 | U001    | purchase   |
| 2021-03-17 12:00:00 | U003    | login      |
| 2021-03-17 12:30:00 | U001    | login      |
| 2021-03-17 13:00:00 | U003    | purchase   |
| 2021-03-20 14:00:00 | U001    | login      |
| 2021-03-20 14:10:00 | U001    | purchase   |

今回は触れませんがNOBORIアプリではFirebase向けGoogleAnalyticsを使っており、GoogleAnalyticsのデータをBigQueryと連携させると、このようなテーブルが作られます。（もっと情報量が多いですが）

ウィンドウ関数を使えば、前回イベントの時間を同じ行に表示できます。

SELECT
   event_time,
   user_id,
   event_name,
   -- 同じユーザーの前回イベント時間
   LAG(event_time) 
   	OVER (PARTITION BY user_id ORDER BY event_time) 
   	AS last_event_time,
   -- 同じユーザー同じイベントの前回イベント時間
   LAG(event_time) 
   	OVER (PARTITION BY user_id, event_name ORDER BY event_time) 
   	AS last_same_event_time
FROM
   event
ORDER BY
   event_time
;

| event_time          | user_id | event_name | last_event_time     | last_same_event_time |
|---------------------|---------|------------|---------------------|----------------------|
| 2021-03-16 12:00:00 | U001    | login      |                     |                      |
| 2021-03-16 13:30:00 | U001    | purchase   | 2021-03-16 12:00:00 |                      |
| 2021-03-17 12:00:00 | U003    | login      |                     |                      |
| 2021-03-17 12:30:00 | U001    | login      | 2021-03-16 13:30:00 | 2021-03-16 12:00:00  |
| 2021-03-17 13:00:00 | U003    | purchase   | 2021-03-17 12:00:00 |                      |
| 2021-03-20 14:00:00 | U001    | login      | 2021-03-17 12:30:00 | 2021-03-17 12:30:00  |
| 2021-03-20 14:10:00 | U001    | purchase   | 2021-03-20 14:00:00 | 2021-03-16 13:30:00  |

LAG関数はパーティション内の1つ上の行を参照する関数です。PARTITION BYでuser_idとevent_nameを指定し、ORDER BYでevent_timeを指定することで「同じユーザー同じイベントの前回時刻」を取得することができます。

3. 縦方向のデータを横方向にして表示する

会員登録の履歴を記録するregisterテーブルを考えます。

SELECT *
FROM register;

| reg_date   | user_id | device  |
|------------|---------|---------|
| 2021-03-16 | U001    | pc      |
| 2021-03-16 | U002    | ios     |
| 2021-03-16 | U003    | android |
| 2021-03-17 | U004    | ios     |
| 2021-03-17 | U005    | pc      |
| 2021-03-17 | U006    | ios     |
| 2021-03-17 | U007    | android |
| 2021-03-17 | U008    | ios     |
| 2021-03-17 | U009    | android |

この例のようにファクトテーブルは縦方向にデータを記録することが多いです。
このようなデータは直感的にわかりづらいため、deviceの情報を横方向へと変換します。

SELECT 
	reg_date,
	COUNT(CASE WHEN device = 'pc' THEN user_id END) AS pc,
	COUNT(CASE WHEN device = 'ios' THEN user_id END) AS ios,
	COUNT(CASE WHEN device = 'android' THEN user_id END) AS android
FROM
   register
GROUP BY
   reg_date
ORDER BY
   reg_date
;


| reg_date   | pc | ios | android |
|------------|----|-----|---------|
| 2021-03-16 |  1 |   1 |       1 |
| 2021-03-17 |  1 |   3 |       2 |

直感的にわかりやすくなりました。またこうすることでBIツールで扱いやすくなります。
この例だとreg_dateを横軸とし、pc、ios、androidをそれぞれ変数として扱うだけでグラフ化することができます。
この方法はあらかじめ対象の列に格納されている値の種類や数がわからなければ使えませんが、覚えておくととても有用です。

以上データ分析時に役に立つSQLテクニックでした。今回紹介した方法は基礎的な文法を使ったものですが、工夫次第でデータを扱いやすくすることができます。
ぜひ使ってみてください。