こんにちは, 開発部のはちやです.
今回は, 今やサービスを運営する会社であればどこでも行われているであろうデータ分析について, WEB開発者の方を対象に 「SQL でのデータ分析のススメ」と題してご紹介したいと思います.
SQL でのデータ分析がおすすめな理由
分析技術の進歩によりデータが比較的容易に取得/抽出できるようになった昨今, データ分析が以前に増して活発に行われるようになってきていると感じます. そんなこんなでデータ分析をしたいWEB開発者の方が増えてきているのではないでしょうか(僕はそうでした)
しかし, 「データ分析したいけど, 何を使えばいいのかよくわからない」「何を学習すればいいのかよくわからない」というWEB開発者の方がいらっしゃると想像します(僕がそうでした).
そんな方々に, 個人的には SQL を使ったデータ分析を推したいと思っています！ 理由は以下の 2 つです.
この記事ではこれらの理由の裏付けとして, 分析用途 SQL の紹介及びペロリで実際に行われている SQL でのデータ分析の様子をご紹介したいと思います.
ペロリの分析環境のご紹介
みなさんの会社ではどんなデータベースを利用してデータ分析を行っていますか？ Google BigQuery ですか？ Amazon Redshift ですか？ Teradata ですか？ Hadoop ですか？ MySQL ですか？
ペロリでは Hadoop を利用しています. 分析基盤は DeNA から提供してもらっていて, 分析用データベースは HP Vertica (HP Vertica はANSI SQL99に準拠した標準的なSQLインターフェースを持つ) が採用されています. ちなみにBIツールは内製のものです. このBIツールを用いて, 社内アナリスト, 開発部メンバーなどが日々データ分析を行っています.
※ 現在の構成の背景的なところ
DeNA は2012年頃まで Hadoop MapReduce x Hive QL/Pig で頑張っていました. が, 現場で発生している問題への対処, ならびに一般的な分析技術の向上により2013年から現在の形にシフトしました. その辺りの背景は以下をご参照ください.
分析用途 SQL を活用するために
さて, SQL で実際にデータ分析を行う前に, 普段 OLTP 用途の SQL がメインの WEB 開発者の方は知らない(もしくは普段あまり意識しない)かもしれないトピックスが 2 つあります.
- ウィンドウ関数
- 直積の JOIN
これらは SQL で分析を行う際とても便利なので, １つずつご紹介していきます.
ウィンドウ関数がとても便利
SQL には String 関数, Date/Time 関数, 集約関数, ウィンドウ関数等様々な関数がありますが, ここでは OLTP 用途ではあまり使わないと思われる ウィンドウ関数 )についてご説明します.
ウィンドウ関数はまず実例を見たほうがわかりやすいと思うので, 以下に例を載せます.
以下の例は users_consumed_point_histories という以下のスキーマをもつテーブルにユーザーのポイント消費履歴が管理されているものとし, consumed_point に消費したポイント数が記録されているものとします.
CREATE TABLE `users_consumed_point_histories` (
user_id INT,
consumed_point INT,
created_at DATETIME
)
| user_id | consumed_point |
|---|---|
| 123 | 100 |
| 123 | 200 |
| 123 | 100 |
| 324 | 150 |
| 324 | 500 |
| 324 | 150 |
| 528 | 1000 |
集約関数 SUM を利用した場合
まずは一般的な集約関数としての SUM を用いて集計してみます.
SELECT user_id, SUM(consumed_point) FROM users_consumed_point_histories GROUP BY user_id
| user_id | SUM(consumed_point) |
|---|---|
| 123 | 400 |
| 324 | 800 |
| 528 | 1000 |
user_id ごとに 1 行にまとまってしまっていることがわかります.
ウィンドウ関数 SUM を利用した場合
次にウィンドウ関数 SUM を用いてみます.
SELECT user_id, consumed_point, SUM(consumed_point) OVER (PARTITION BY user_id) FROM users_consumed_point_histories
結果はこんな感じで返されます.
| user_id | consumed_point | SUM(consumed_point) |
|---|---|---|
| 123 | 100 | 400 |
| 123 | 200 | 400 |
| 123 | 100 | 400 |
| 324 | 150 | 800 |
| 324 | 500 | 800 |
| 324 | 150 | 800 |
| 528 | 1000 | 1000 |
ウィンドウ関数を使うと, 行数は変わらず合計が横に追加されているのがわかります. これはつまりウィンドウ関数を利用すると, 集約関数を用いた時のように行が集約されることなく group by で作成したグループの中の行を計算することができるということです. 「各行の値が全体に対してどれくらいの割合を占めるのか？」といったようなケースや「日付別の累積値がどれくらいか？」などを見たいケースなどで役に立ちます.
提供されているウィンドウ関数は各データベース製品によって微妙に異なります. 参考までに よく使われる(であろう)ウィンドウ関数で HP Vertica が提供しているものを以下に挙げておきます.
色々とご自分で試してみてください :D
直積のみを行う CROSS JOIN
次に 直積のみを行う CROSS JOIN について説明します. そのために JOIN は「概念的にどんな処理を行っているのか？」というところを少し説明してみます.
JOIN はテーブル(というかリレーション)の結合を行うのですが, 結合は
- 行の直積(すべての組み合わせの組)
- 制限(WHERE)
の要素に分解できます. これを一般的な以下の3種類の演算で考えると以下の様になります.
- INNER JOIN
- 直積を作った後, on 句にマッチした行のみを抽出します
- OUTER JOIN
- 直積を作った後, 結合しているリレーションの LEFT | RIGHT に on 句にマッチした行があればそれを抽出します
- CROSS JOIN
- 直積のみを行いすべての組み合わせの行を抽出します
さらっと CROSS JOIN の説明も済ませてしまっているのですが, つまり CROSS JOIN とは JOIN の持つ「行の直積」「制限」のうち「行の直積」のみで結合する処理のことです.
参考までに種類ごとのJOINをベン図を使って説明してくれている図がありましたので, 添付しておきます.
Reference: link
この節の最後に CROSS JOIN の分析用途での使い道について簡単にご紹介しようと思います.
例えば, 以下の様な users_items テーブルがあったとします. このテーブルにはユーザーの所持アイテムが4つまで登録できるようになっていて, 1つ目のアイテムを item_id1, 2 つ目のアイテムを item_id2 カラムに格納しているとします.
CREATE TABLE `users_items` (
user_id INT
item_id1 INT
item_id2 INT
item_id3 INT
item_id4 INT
)
INSERT INTO users_items VALUES (10000 120, 130, 140, 150)
CREATE TABLE 'seq` (
id INT
)
INSERT INTO seq VALUES (1) (2), (3), (4)
しかしここで仕様に変更が入り, 5 つまでアイテムを所持できるようになったとしましょう. すると, このようなカラムに情報を追加していく方式だとテーブル定義の変更が必要になってしまいます. レコードが少ないうちであればどうにかなりますが, 多くなってくると ALTER TABLE の負荷が無視できません. よってここいらでカラムを拡張してアイテムを管理するのではなく, アイテムごとに 1 行使って管理する "縦持ち" に変更しておきましょう.
※ もちろん縦持ちのデメリットである同時更新のコスト増は無視できません. 実際の判断は諸々を考慮したうえで慎重に行ってください.
CROSS JOIN を使うと以下のように簡単に変換できます.
SELECT
*
FROM (
SELECT
A.user_id,
case B.id
when 1 then B.item_id1
when 2 then B.item_id2
when 3 then B.item_id3
when 4 then B.item_id4
end as item_id
FROM users_items A CROSS JOIN seq B
) tmp
WHERE item_id IS NOT NULL
結果はこんな感じになります.
| user_id | item_id |
|---|---|
| 10000 | 120 |
| 10000 | 130 |
| 10000 | 140 |
| 10000 | 150 |
実際に分析してみる
最後に, SQL が多様な事業分析ニーズに応えられます, の裏付けとして 1 つだけですが具体例をご紹介したいと思います.
今回は, 例として月額制クーポンサービスを題材にします. 月額制クーポンサービスにおいては, 月額支払ユーザーの満足度を向上させることが, プロダクトにとって重要な指標となるでしょう. これを計測するためにいくつかの先行指標を設定すると思うのですが, この場合「ユーザーのクーポン利用率」はよい先行指標となりそうです.
よって, 「当月におけるユーザーの月内チケット利用率の日別推移」を出してみましょう. ひとまず以下にクエリ全文を掲載します.
WITH
daily_calendar AS (
SELECT
date, month, day
FROM calendar
WHERE DATE(LAST_DAY(NOW() - INTERVAL '1 month') + 1) < date AND date < DATE(NOW())
),
daily_total_valid_users_amount AS (
SELECT
date, month, day, SUM(is_valid) AS valid_subscription_amount
FROM
(
SELECT
A.date, A.month, A.day, B.user_id,
CASE WHEN (DATE(B.created_at) < DATE(A.date) AND (DATE(A.date) < DATE(B.cancelled_at) OR B.cancelled_at IS NULL)) THEN 1 ELSE 0 END AS is_valid
FROM daily_calendar A LEFT OUTER JOIN subscriptions B ON B.created_at::DATE <= A.date::DATE
) daily_valid_users
GROUP BY date, month, day
),
users_monthly_first_issue_date AS (
SELECT
first_date_in_dates_month, user_id, date_trunc('month', first_date_in_dates_month) AS first_date_month
FROM (
SELECT
user_id, MIN(issue_date) OVER (PARTITION BY issue_month, user_id) AS first_date_in_dates_month
FROM (
SELECT
DATE_TRUNC('MONTH' created_at::TIMESTAMP) AS issue_month, created_at::DATE AS issue_date, user_id
FROM users_tickets
) users_tickets_with_month
) users_tickets_with_first_issue_month
GROUP BY first_date_in_dates_month, user_id
),
daily_monthly_total_issue_amount as (
SELECT
date,
month,
day,
first_date_month,
SUM(daily_issue_amount) OVER (PARTITION BY first_date_month ORDER BY first_date_in_dates_month) AS daily_monthly_total_issue_amount
FROM (
SELECT
A.date, A.month, A.day, COUNT(distinct user_id) AS daily_issue_amount, B.first_date_in_dates_month, B.first_date_month
FROM daily_calendar A LEFT OUTER JOIN users_monthly_first_issue_date B ON A.date = B.first_date_in_dates_month
GROUP BY A.date, A.month, A.day, B.first_date_in_dates_month, B.first_date_month
) daily_uniq_users_issue_amount
)
SELECT
A.date, A.month, A.day, A.valid_subscription_amount, B.daily_monthly_total_issue_amount,
ROUND(B.daily_monthly_total_issue_amount/A.valid_subscription_amount 4.0) AS daily_issue_rate
FROM
daily_total_valid_users_amount A
LEFT OUTER JOIN daily_monthly_total_issue_amount B ON A.date = B.date
WITH 句は説明していなかったので, 「?」の方もいらっしゃったかもしれません. これは具体例があったほうが有り難みがわかるだろうと考えとっていたのでした :p
WITH 句を使うと SQL の実行結果を VIEW のように SQL 内部から参照することができます(インラインビューと呼ばれたりしています). 例えば daily_calendar は至るところで参照されていることがわかります.
もし WITH 句がなかったら, 毎回同じコードを書かなければならないですし, 毎回実行されることになるため効率も悪いですよね？
では処理の流れをざっくり説明していきます.
まずいちばん最初の部分です.
WITH
-- `calendar` には日付が格納されているとします
daily_calendar AS (
SELECT
date, month, day
FROM calendar
-- 当月月初から当日まで
WHERE DATE(LAST_DAY(NOW() - INTERVAL '1 month') + 1) < date AND date < DATE(NOW())
),
...
ここでは calendar という日付が格納されているテーブルから当月の月初から現在までの分を取得して daily_calendar としてインラインビューを作成しています.
後のクエリで, 日付の基準を利用してほしいデータを抽出するのに利用されています.
次に daily_total_valid_users_amount です.
daily_total_valid_users_amount AS (
SELECT
date, month, day, SUM(is_valid) AS valid_subscription_amount
FROM
(
SELECT
A.date, A.month, A.day, B.user_id,
CASE WHEN (DATE(B.created_at) < DATE(A.date) AND (DATE(A.date) < DATE(B.cancelled_at) OR B.cancelled_at IS NULL)) THEN 1 ELSE 0 END AS is_valid
FROM daily_calendar A LEFT OUTER JOIN subscriptions B ON B.created_at::DATE <= A.date::DATE
) daily_valid_users
GROUP BY date, month, day
),
subscriptions は月額サービス加入ユーザー情報が格納されているとします.
サブクエリの daily_valid_users では daily_calendar と JOIN してユーザーの日別有効状況を抽出し, それを集約関数 SUM で日別に集計しています.
そして, users_monthly_first_issue_date です.
users_monthly_first_issue_date AS (
SELECT
first_date_in_dates_month, user_id, date_trunc('month', first_date_in_dates_month) AS first_date_month
FROM (
SELECT
user_id, MIN(issue_date) OVER (PARTITION BY issue_month, user_id) AS first_date_in_dates_month
FROM (
SELECT
DATE_TRUNC('MONTH' created_at::TIMESTAMP) AS issue_month, created_at::DATE AS issue_date, user_id
FROM users_tickets
) users_tickets_with_month
) users_tickets_with_first_issue_month
GROUP BY first_date_in_dates_month, user_id
),
users_tickets にはユーザーのチケット利用履歴が格納されているとします.
サブクエリは一番内側から処理されるので, 内側から見ていきます.
1 番内側のサブクエリは users_tickets のカラムを単純に月型に変換しているだけです.
2 番目のサブクエリではそれをウィンドウ関数 MIN を使って 月, user_id ごとに最小の日付を抽出しています.
これは, 会員が月内に2枚以上クーポンを利用することもあるためです. 今知りたいのは「利用率」なので最初の日付のみ抽出したいのです.
こういう時にウィンドウ関数が役に立ちます.
そして外側の SELECT 分でそれらを日付, user_id ごとにまとめて 1 行にして抽出しています.
daily_monthly_total_issue_amount は users_monthly_first_issue_date を daily_total_valid_users_amount と同じように date, month, day で抽出したうえで, 日付時点の月内累計チケット利用者数を算出しています.
ウィンドウ関数 SUM が役立ちます.
daily_monthly_total_issue_amount as (
SELECT
date,
month,
day,
first_date_month,
SUM(daily_issue_amount) OVER (PARTITION BY first_date_month ORDER BY first_date_in_dates_month) AS daily_monthly_total_issue_amount
FROM (
SELECT
A.date, A.month, A.day, COUNT(distinct user_id) AS daily_issue_amount, B.first_date_in_dates_month, B.first_date_month
FROM daily_calendar A LEFT OUTER JOIN users_monthly_first_issue_date B ON A.date = B.first_date_in_dates_month
GROUP BY A.date, A.month, A.day, B.first_date_in_dates_month, B.first_date_month
) daily_uniq_users_issue_amount
),
最後に, 日別の累積有効ユーザー数 daily_total_valid_users_amount と 日別の月内累計利用者数 daily_monthly_total_issue_amount を JOIN し, 日別の月内累計チケット利用率をだします.
SELECT
A.date, A.month, A.day, A.valid_subscription_amount, B.daily_monthly_total_issue_amount,
ROUND(B.daily_monthly_total_issue_amount/A.valid_subscription_amount 4.0) AS daily_issue_rate
FROM
daily_total_valid_users_amount A
LEFT OUTER JOIN daily_monthly_total_issue_amount B ON A.date = B.date
いかがでしょうか. SQL で結構柔軟にデータ分析ができそうであること, 実感いただけたのではないでしょうか.
まとめ
今回は「SQL でのデータ分析のススメ」と題して, WEB 開発者を対象になぜ SQL がおすすめなのかをご説明させていただきました. データ分析に SQL を用いることで, 様々な事業要求に対して WEB 開発者がデータ分析を行うことができると思います.
また今回ご紹介できなかったのですが, SQL には分析上とても役立つ便利な関数が豊富に用意されています. 基本的にデーターベース製品には優れたドキュメントが用意されています. (参考までに HP Vertica のドキュメントを貼っておきます: link)
それらを参考に色々とご自身で試して見ていただけると, SQL の強力さがより実感できると思います.
ちょっとでも興味が湧いた方はこの機会に是非試してみていただけると幸いです.