■■■データ分析でよくある長いSQLの読み方■■■
① 内側のSELECT文から読む
- サブクエリはカッコ()で囲まれているが、数学の数式と同じく、カッコ内は先に処理される。なので、内側から読んでいく。
② SELECT文は句の処理順に読む
以下の順番で処理が進む
- FROM
- JOIN
- ON (JOIN句内)
- WHERE
- GROUP BY
- HAVING
- SELECT
- OVER (ウィンドウ関数)
- ROWS / RANGE (ウィンドウ関数)
- PARTITION BY (OVER句内)
- ORDER BY (OVER句内)
- ORDER BY
- LIMIT
■■■統計量「四分位数」を求めるSQLを読み解く■■■
以下の四分位に振り分けるSQLを読み解いていく
・対象テーブル：store_sales
store_id|sales|
--------|-----|
A | 4350|
B | 550|
C | 400|
D | 420|
E | 450|
F | 420|
G | 400|
H | 320|
I | 320|
J | 400|
・SQL
-- CASE式にて四分位に振り分ける
SELECT
store_id,
sales,
ranking,
CASE
WHEN sa1.ranking <= 0.25 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 1
WHEN sa1.ranking <= 0.5 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 2
WHEN sa1.ranking <= 0.75 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 3
ELSE 4
END AS 四分位
FROM
-- 相関サブクエリと組み合わせてランキングを算出値する
(
SELECT
S1.store_id,
S1.sales,
-- 自分以上の売上を持つ店舗を数える相関サブクエリ
(
SELECT
COUNT(S2.sales)
FROM
store_sales AS S2
WHERE
S2.sales >= S1.sales
) ranking
FROM
store_sales AS S1
) sa1;
・結果
store_id|sales|ranking|四分位|
--------| ----- |------- |--- |
A | 4350| 1| 1|
B | 550| 2| 1|
C | 400| 8| 4|
D | 420| 5| 2|
E | 450| 3| 2|
F | 420| 5| 2|
G | 400| 8| 4|
H | 320| 10| 4|
I | 320| 10| 4|
J | 400| 8| 4|
■SQL全体像と読み解き順序■
確認点
- SELECT文はいくつあるか
- SELECT文はどの順で実行されるか（どれが内側か）
- 登場する句の種類とカッコ（スカラサブクエリ、式や関数）があるか
- 句とカッコがどの順で実行されるか
を、SQL文を読む時にまず確認
■ランキングを算出するための相関サブクエリ■
上記のSQLで、自分以上の売上を持つ店舗を数えるSQLを確認
SELECT
COUNT(*)
FROM
store_sales AS S2
WHERE
S2.sales >= S1.sales;
相関サブクエリを使って外側のクエリを用いて求めていることがわかる。次に、外側のクエリと組み合わせたSQL結果を確認する。
■ランキングを算出するクエリ■
相関サブクエリと組み合わせランキングを算出するクエリの確認
・対象SQL：SQL9-2-3
SELECT
S1.store_id,
S1.sales,
(
SELECT
COUNT(S2.sales)
FROM
store_sales AS S2
WHERE
S2.sales >= S1.sales
) ranking
FROM
store_sales AS S1;
・結果
store_id|sales|ranking|
--------|-----|-------|
A | 4350| 1|
B | 550| 2|
C | 400| 8|
D | 420| 5|
E | 450| 3|
F | 420| 5|
G | 400| 8|
H | 320| 10|
I | 320| 10|
J | 400| 8|
ランキングが求められている。
これを外側のクエリで用いることで、四分位が求められる。最後に、一番外側のクエリを読む
■ランキングを元に四分位に振り分ける■
・SQL
SELECT
store_id,
sales,
ranking,
-- CASE式にて四分位に振り分ける
CASE
WHEN sa1.ranking <= 0.25 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 1
WHEN sa1.ranking <= 0.5 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 2
WHEN sa1.ranking <= 0.75 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 3
ELSE 4
END AS 四分位
FROM
(
-- SQL9-2-3
) sa1 ;
以上で四分位範囲が求められるが、ウィンドウ関数で簡単に出せるので、実際にこれを使うことはないっぽい・・・
■■■バスケット分析のSQLを読み解く■■■
■バスケット分析とは■
ある商品を購入された際の買い物かご（バスケット）、つまり同一レシートで一緒に買われている商品が何かを分析する手法。
補：アソシエーション分析
バスケット分析は、「アソシエーション分析」の分析手法の一つ。アソシエーション分析は、統計的に見て強い関係を持つ商品間の関係（ルール）を抽出する手法。例えば、同時に購入される関係性が強い商品の組み合わせやその割合などを算出する。
アソシエーション分析では、以下の３つの指標を算出する
- 信頼度：商品Xを買った顧客が商品Yも一緒に買う確率
- 支持度：商品Xと商品Yが同時に買われる確率
- リフト値：「信頼度」÷商品Yが買われる確率
このバスケット分析では、単純に同時購入された（同じレシート内で登場する）商品ペアの頻度（信頼度）を求めている。
■データとSQLの確認■
・対象データ（購買履歴テーブル）：sales_logテーブル
item_name|item_category|sales_date|store_place|user_id|price|
---------|-------------|----------|-----------|-------|-----|
まぐろ |food |2019-03-29|Store A |UID0001| 250|
しょう油 |food |2019-03-29|Store A |UID0001| 250|
しょう油 |food |2019-03-30|Store A |UID0001| 200|
まぐろ |food |2019-03-30|Store A |UID0001| 350|
卵 |food |2019-03-29|Store A |UID0001| 200|
まぐろ |food |2019-03-30|Store A |UID0001| 350|
とり肉 |food |2019-04-01|Store A |UID0001| 450|
とり肉 |food |2019-04-02|Store A |UID0001| 450|
深皿 |zakka |2019-04-03|Store A |UID0001| 980|
ジャガイモ |food |2019-04-04|Store A |UID0001| 250|
しょう油 |food |2019-04-04|Store A |UID0001| 250|
ジャガイモ |food |2019-04-05|Store A |UID0001| 250|
ジャガイモ |food |2019-04-06|Store A |UID0001| 250|
・求めたい結果
item_name | item_name2 | confidence
ジャガイモ | しょう油 | 0.33
しょう油 | ジャガイモ | 0.33
しょう油 | まぐろ | 0.67
しょう油 | 卵 | 0.33
まぐろ | しょう油 | 1.00
まぐろ | 卵 | 0.50
卵 | しょう油 | 1.00
卵 | まぐろ | 1.00
バスケット分析では、ある２つの商品の組み合わせが購入される確率をそれぞれ算出したい場合を考える。例えば、上記求めたい結果の１行目を見ると、「ジャガイモを買った顧客がしょう油も買う確率」である信頼度(confidence)は0.33なので、約33％であるとわかる。
以下が、今回読み解くバスケット分析のクエリ
--「商品が同時に購入される頻度を算出する」
SELECT
combi_count.item_name,
combi_count.item_name2,
-- 信頼度を求める
CAST(combi_count.order_count AS UNSIGNED) / item_count.order_count AS confidence
FROM
-- 「商品ごとの購入回数」を求める
(
SELECT
item_name,
COUNT(DISTINCT sales_date) AS order_count
FROM
sales_log
GROUP BY
item_name
) item_count
INNER JOIN
-- 「商品の組み合わせと購入回数」を求める
(
SELECT
sh1.item_name,
shr.item_name AS item_name2,
COUNT(DISTINCT sh1.sales_date) AS order_count
FROM
sales_log AS sh1
INNER JOIN
sales_log shr
ON sh1.sales_date = shr.sales_date
AND sh1.item_name <> shr.item_name
GROUP BY
sh1.item_name ,
shr.item_name
) combi_count
ON combi_count.item_name = item_count.item_name;
上記SQL文を分解して読み解く
■SQL全体像と読み解き順序■
- SELECT文はいくつあるか
- SELECT文はどの順で実行されるか（どれが内側か）
- 登場する句の種類とカッコ（スカラサブクエリ、式や関数）があるか
- 句とカッコがどの順で実行されるか
■１つめの文「商品の組み合わせと購入回数■
・SQLーサブクエリ①
SELECT
sh1.item_name,
shr.item_name AS item_name2,
COUNT(DISTINCT sh1.sales_date) AS order_count
FROM
sales_log AS sh1
# INNER JOIN で自己結合テーブル作成
INNER JOIN
sales_log shr
# 「購買日が同じ」で「商品名が異なる」条件により、同じレシートで買われた商品の組み合わせを作成
ON sh1.sales_date = shr.sales_date
AND sh1.item_name <> shr.item_name
GROUP BY
# 商品組み合わせ毎にグループ化するため、それぞれのテーブルのitem_nameでグループ化
sh1.item_name,
shr.item_name ;
・結果
item_name|item_name2|order_count|
---------|----------|-----------|
しょう油 |まぐろ | 2|
しょう油 |ジャガイモ | 1|
しょう油 |卵 | 1|
まぐろ |しょう油 | 2|
まぐろ |卵 | 1|
ジャガイモ |しょう油 | 1|
卵 |しょう油 | 1|
卵 |まぐろ | 1|
結果の１行目を見ると、しょう油とまぐろの組み合わせで２回購入されていることがわかる。これが、信頼度を求めるときの分子になる。
■２つめの文「商品ごとの購入回数」■
・商品ごとの購入回数を求めるSQLーサブクエリ②
SELECT
item_name,
COUNT(DISTINCT sales_date) AS order_count
FROM
sales_log
GROUP BY
item_name;
・結果
item_name|order_count|
---------|-----------|
しょう油 | 3|
とり肉 | 2|
まぐろ | 2|
ジャガイモ | 3|
卵 | 1|
深皿 | 1|
商品と、商品ごとの購入回数がテーブルとして求められている。これが、信頼度を求めるときの分母になる。
■サブクエリ①とサブクエリ②を内部結合した結果を確認■
上記のサブクエリ①と②を内部結合したテーブルから商品を同時に購入される頻度を求めるので、頻度を求める前に内部結合した中間テーブルを確認する。
・サブクエリ①と②を内部結合したテーブルを求めるSQL
SELECT
*
FROM
(
SELECT
item_name,
COUNT(DISTINCT sales_date) AS order_count
FROM
sales_log
GROUP BY
item_name
) item_count
INNER JOIN
(
SELECT
sh1.item_name,
shr.item_name AS item_name2,
COUNT(DISTINCT sh1.sales_date) AS order_count
FROM
sales_log AS sh1
INNER JOIN
sales_log shr
ON sh1.sales_date = shr.sales_date
AND sh1.item_name <> shr.item_name
GROUP BY
sh1.item_name,
shr.item_name
) combi_count
ON combi_count.item_name = item_count.item_name;
・結果
item_name|order_count|item_name|item_name2|order_count|
--------- |----------- |-------- -|---------- |---------- -|
しょう油| 3| しょう油 | まぐろ | 2|
しょう油 | 3| しょう油 | ジャガイモ | 1|
しょう油 | 3| しょう油 | 卵 | 1|
まぐろ | 2| まぐろ | しょう油 | 2|
まぐろ | 2| まぐろ | 卵 | 1|
ジャガイモ | 3|ジャガイモ | しょう油 | 1|
卵 | 1| 卵 | しょう油 | 1|
卵 | 1| 卵 | まぐろ | 1|
この内部結合のitem_count側のorder_countを分母にして、combi_count側のorder_countの値を分子にした計算結果が、商品が同時に購入される頻度となる。
■最終文「商品が同時に購入される頻度を算出する」SQL■
上記サブクエリ①と②の内部結合テーブルの結果を用いて商品が同時に購入される頻度を算出する。
・最終SQL
SELECT
combi_count.item_name,
combi_count.item_name2,
-- 信頼度を求める
CAST(combi_count.order_count AS UNSIGNED) / item_count.order_count AS confidence
FROM
-- 「商品ごとの購入回数」を求める
(
SELECT
item_name,
COUNT(DISTINCT sales_date) AS order_count
FROM
sales_log
GROUP BY
item_name
) item_count
INNER JOIN
-- 「商品の組み合わせと購入回数」を求める
(
SELECT
sh1.item_name,
shr.item_name AS item_name2,
COUNT(DISTINCT sh1.sales_date) AS order_count
FROM
sales_log AS sh1
INNER JOIN
sales_log AS shr
ON sh1.sales_date = shr.sales_date
AND sh1.item_name <> shr.item_name
GROUP BY
sh1.item_name ,
shr.item_name
) combi_count
ON combi_count.item_name = item_count.item_name;
・結果
item_name|item_name2|confidence|
--------- |---------- |---------- |
しょう油 | まぐろ | 0.6667|
しょう油 | ジャガイモ | 0.3333|
しょう油 | 卵 | 0.3333|
まぐろ | しょう油 | 1.0000|
まぐろ | 卵 | 0.5000|
ジャガイモ | しょう油 | 0.3333|
卵 | しょう油 | 1.0000|
卵 | まぐろ | 1.0000|
■■■「ユーザーの利用機能分析」のSQLを読み解く■■■
■「ユーザーの利用機能分」とは■
- 「それぞれの機能ごと」の利用ユーザー数はどうなっているか？
- 「特定の機能を利用していないユーザー」はどのくらいか？
など各種機能の利用状況を調べ、提供している機能がユーザーに受け入れられているかどうか、想定してる通りに使ってもらえているかの確認。
■求めたい事■
２つや３つの複数の機能をまたがって利用している（または利用していない）ユーザー数や構成比の把握。
・対象データ：action_logテーブル
item_name|item_category|action_category|action_date|user_id|
---------|-------------|---------------|-----------|-------|
ハーブティー |food |tagging | 2019-05-29|UID0001|
紅茶 |food |tagging | 2019-05-29|UID0001|
紅茶 |food |review | 2019-05-30|UID0001|
ハーブティー |food |purchase | 2019-05-30|UID0001|
シロップ |food |purchase | 2019-05-30|UID0001|
ハーブティー |food |tagging | 2019-05-30|UID0001|
レモン |food |tagging | 2019-06-01|UID0001|
レモン |food |tagging | 2019-06-02|UID0001|
マグカップ |zakka |review | 2019-06-03|UID0002|
シロップ |food |tagging | 2019-06-04|UID0001|
紅茶 |food |tagging | 2019-06-04|UID0003|
シロップ |food |purchase | 2019-06-05|UID0001|
シロップ |food |tagging | 2019-06-06|UID0001|
ハーブティー |food |tagging | 2019-06-07|UID0001|
ハーブティー |food |tagging | 2019-06-08|UID0001|
・求めたい結果
・SQL
-- WITHによるサブクエリの表現
WITH
/* ① */
-- ユーザーごとのアクション有無を確認するサブクエリ
user_action AS(
--１つめのSELECT文
SELECT
user_id,
-- SIGN関数で数値をまるめる
SIGN(SUM( # taggingの総回数を計算
-- CASE式で行動有無の判定（
CASE
WHEN action_category = 'tagging' THEN 1
ELSE 0
END
)) AS has_tagging,
SIGN(SUM( # purchaseの総回数を計算
CASE
WHEN action_category = 'purchase' THEN 1
ELSE 0
END
)) AS has_purchase,
SIGN(SUM( # reviewの総回数を計算
CASE
WHEN action_category = 'review' THEN 1
ELSE 0
END
)) AS has_review
FROM
action_log
GROUP BY
user_id
ORDER BY
user_id
),
/* ② */
-- アクションのすべての組み合わせごとにユーザー数の集計を求める
action_diaglam AS (
-- ２番めのSELECT文
-- 全アクションの組み合わせで集計
SELECT
has_tagging,
has_purchase,
has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_tagging,
has_purchase,
has_review
-- 以下、２アクションの組み合わせで集計
UNION ALL
-- ３番めのSELECT文
SELECT
NULL AS has_tagging,
has_purchase,
has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_purchase,
has_review
UNION ALL
（中略）
-- 以下、１アクションについて集計
UNION ALL
-- ６番めのSELECT文
SELECT
NULL AS has_tagging,
NULL AS has_purchase,
has_reivew,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_review
UNION ALL
（中略）
-- 以下、全体のユーザー数の集計
UNION ALL
-- ９番めのSELECT文
SELECT
NULL AS has_tagging,
NULL AS has_purchase,
NULL AS has_review,
COUNT(*) AS user_count
FROM
user_action
)
/* ⑩ */
-- 最後にデータを整形する
-- 10番めのSELECT文
SELECT
-- CASE式でフラグから文字列への変換
CASE has_tagging
WHEN 1 THEN 'tagging'
WHEN 0 THEN 'not tagging'
ELSE 'all'
END AS done_tagging,
CASE has_purchase
WHEN 1 THEN 'purchase'
WHEN 0 THEN 'not purchase'
ELSE 'all'
END AS done_purchase,
CASE has_reivew
WHEN 1 THEN 'review'
WHEN 0 THEN 'not review'
ELSE 'all'
END AS done_review,
user_count,
-- NULLIF関数による対応
ROUND(100.0 * user_count / NULLIF(
-- SUM関数(ウィンドウ関数)による集計
SUM(
CASE
WHEN has_tagging IS NULL
AND has_purchase IS NULL
AND has_review IS NULL
THEN user_count
ELSE 0
END
) over(), 0), 2) AS ratio
FEOM
action_diaglam
ORDER BY
done_tagging,
done_purchase,
done_review;
■SQL全体像と読み解き順序■
確認事項
- SELECT文はいくつあるか
- SELECT文はどの順か（どれが内側か）
- 登場する句の種類
- 句の中にカッコ（スカラサブクエリ、式や関数）があるか
上記のSQL全体を確認すると、下記のようになっている。
- WITH句により、サブクエリが順に並んでいる
- SELECT文は全部で１０
- ただしほとんどがUNION ALLによる連結
- サブクエリの①の部分が１番。①②③・・・の順
これを元に文を一つずつ確認していく。
■１番めの文「ユーザーごとのアクションありなし」■
ユーザー毎に、何のアクションを行ったかを集計
・以下が対象部分のSQL
/ * ① */
SELECT
user_id,
SIGN(SUM(CASE
WHEN action_category = 'tagging' THEN 1
ELSE 0
END
)) AS has_tagging,
SIGN(SUM(CASE
WHEN action_category = 'purchase' THEN 1
ELSE 0
END
)) AS has_purchase,
SIGN(SUM(CASE
WHEN action_category = 'review' THEN 1
ELSE 0
END
)) AS has_review
FROM
action_log
GROUP BY
user_id
ORDER BY
user_id;
・結果
user_id |has_tagging|has_purchase|has_review|
------- |----------- |------------ |---------- |
UID0001| 1| 1| 1|
UID0002| 0| 0| 1|
UID0003| 1| 0| 0|
・SIGN関数について
SIGN(引数)
引数の数値データの符号を返す関数。
戻り値：
- 数値＜0 -> -1
- 数値＝0 -> 0
- 数値＞0 -> +1
- 数値 IS NULL -> NULL
■２番め〜９番めの文「アクションの組み合わせの集計」■
複数の機能をまたがって利用しているユーザー数や構成比を知りたい。例えば「購入とレビューを行ったユーザー数」など２つ（または３つ）のアクションの組み合わせに対するユーザー数が必要。２番めから９番めのSQLがそれに該当。
・以下が対象部分のSQL：アクションの組み合わせをすべてUNION ALLで求める
/* ② ３つのアクションをすべて行っているユーザー数 */
SELECT
has_tagging,
has_purchase,
has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_tagging,
has_purchase,
has_review
UNION ALL
/* ③ has_taggingの行動は問わず、has_purchaseとhas_reviewを行ったユーザー数 */
SELECT
NULL AS has_tagging,
has_purchase,
has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_purchase,
has_review
UNION ALL
/* ④ has_reviewの行動は問わず、has_taggingとhas_purchaseを行ったユーザー数 */
SELECT
has_tagging,
has_purchase,
NULL AS has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_tagging,
has_purchase
UNION ALL
/* ⑤ has_purchaseの行動は問わず、has_taggingとhas_reviewを行ったユーザー数 */
SELECT
has_tagging,
NULL AS has_purchase,
has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_tagging,
has_review
-- 以下から、１アクションについて集計
UNION ALL
/* ⑥ has_taggingとhas_purchaseの行動は問わず、has_reviewだけ行ったユーザー数 */
SELECT
NULL AS has_tagging,
NULL AS has_purchase,
has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_review
UNION ALL
/* ⑦ has_purchaseとhas_reviewの行動は問わず、has_taggingだけ行ったユーザー数 */
SELECT
has_tagging,
NULL AS has_purchase,
NULL AS has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_tagging
UNION ALL
/* ⑧ has_taggingとhas_reviewの行動は問わず、has_purchaseだけ行ったユーザー数 */
SELECT
NULL AS has_tagging,
has_purchase,
NULL AS has_review,
COUNT(*) AS user_count
FROM
user_action
GROUP BY
has_purchase
-- 以下、全体のユーザー数の集計
UNION ALL
/* ⑨ */
SELECT
NULL AS has_tagging,
NULL AS has_purchase,
NULL AS has_review,
COUNT(*) AS user_count
FROM
user_action;
・結果
has_tagging|has_purchase|has_review|user_count|
----------- |------------ |---------- |---------- |
1| 1| 1| 1|
0| 0| 1| 1|
1| 0| 0| 1|
| 1| 1| 1|
| 0| 1| 1|
| 0| 0| 1|
1| 1| | 1|
0| 0| | 1|
1| 0| | 1|
1| | 1| 1|
0| | 1| 1|
1| | 0| 1|
| | 1| 2|
| | 0| 1|
1| | | 2|
0| | | 1|
| 1| | 1|
| 0| | 2|
| | | 3|
この実行結果より、１行目はhas_tagging, has_purchase, has_reviewの３つの行動を行ったユーザーが１名いる、ということがわかる。２行目では、has_taggingとhas_purchaseは行動したことがないが、has_reviewはある、という人が１名。
４行目は、NULL, 1, 1, 1で、NULLは1でも0でもない、つまり行動有無を問わない状態で、has_taggingは無視してhas_purchaseとhas_reviewを行動したことがあるユーザーが１名、という結果を表している。
■１０番めの文「データ整形」■
可読性が高くなるように、②〜⑨のクエリ結果データを整形する。各アクションの組み合わせでのユーザー数の構成比も一緒に計算する。
・SQL
/* ⑩ 最後にデータを整形する */
SELECT
-- CASE式でフラグから文字列への変換
CASE has_tagging
WHEN 1 THEN 'tagging'
WHEN 0 THEN 'not tagging'
ELSE 'all'
END AS done_tagging,
CASE has_purchase
WHEN 1 THEN 'purchase'
WHEN 0 THEN 'not purchase'
ELSE 'all'
END AS done_purchase,
CASE has_review
WHEN 1 THEN 'review'
WHEN 0 THEN 'not review'
ELSE 'all'
END AS done_review,
user_count,
-- NULLIF関数による対応
ROUND(100.0 * user_count / NULLIF(
-- SUM関数(ウィンドウ関数)による集計
SUM(
CASE
WHEN has_tagging IS NULL
AND has_purchase IS NULL
AND has_review IS NULL
THEN user_count
ELSE 0
END
) over() # ウィンドウ関数のover句
, 0), 2) AS ratio # 0)はNULLIF関数の閉じ括弧、,2)はROUND関数の閉じ括弧
FROM
action_diaglam
ORDER BY
done_tagging,
done_purchase,
done_review ;
・結果
done_tagging|done_purchase| done_review |user_count| ratio |
------------ |------------- |----------- |---------- |------ |
all | all | all | 3| 100.00|
all | all | not review | 1| 33.33|
all | all | review | 2| 66.67|
all | not purchase | all | 2| 66.67|
all |not purchase |not review | 1| 33.33|
all |not purchase |review | 1| 33.33|
all |purchase |all | 1| 33.33|
all |purchase |review | 1| 33.33|
not tagging |all |all | 1| 33.33|
not tagging |all |review | 1| 33.33|
not tagging |not purchase |all | 1| 33.33|
not tagging |not purchase |review | 1| 33.33|
tagging |all |all | 2| 66.67|
tagging |all |not review | 1| 33.33|
tagging |all |review | 1| 33.33|
tagging |not purchase |all | 1| 33.33|
tagging |not purchase |not review | 1| 33.33|
tagging |purchase |all | 1| 33.33|
tagging |purchase |review | 1| 33.33|
- 'all'はNULL値の置き換え。allなのは、行動の種類は問わない（あらゆる行動の）ため。
- SUM関数(ウィンドウ関数)はover句の引数が空なので、すべての行を対象として集計している。結果として、このSUM関数は全体のユーザー数を返している。
- NULLIF関数は0除算対応。ユーザー数を全体のユーザー数で割ることで構成比を求めている。全体のユーザー数は、SUMのウィンドウ関数とNULLIF関数を用いて集計されている。
- NULLIF関数は、指定された２つの引数が等しい場合にNULLを返す。ここでは、NULLIF関数の第２引数が0なので、ウィンドウ関数SUMの結果が0の時、NULLを返す。NULLIF関数の役割は、もしもユーザー数集計が0だったら、0で割り算してしまう0除算を回避すること。
■■■既存のSQLをよりよく改善する■■■
・対象SQL：四分位数を行うSQL
-- CASE式にて四分位に振り分ける
SELECT
store_id,
sales,
ranking,
CASE
WHEN sa1.ranking <= 0.25 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 1
WHEN sa1.ranking <= 0.5 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 2
WHEN sa1.ranking <= 0.75 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 3
ELSE 4
END AS 四分位
FROM
-- 相関サブクエリと組み合わせてランキングを算出値する
(
SELECT
S1.store_id,
S1.sales,
-- 自分以上の売上を持つ店舗を数える相関サブクエリ
(
SELECT
COUNT(S2.sales)
FROM
store_sales AS S2
WHERE
S2.sales >= S1.sales
) ranking
FROM
store_sales AS S1
) sa1;
■改善方法１：ウィンドウ関数NTILEで簡単に４グループ分けを実現する■
均等グループ分けを行うNTILEでグループ分けを試す。
NTILE(N)：対象をN個のグループに分ける関数
・ウィンドウ関数NTILEによる改善
SELECT
store_id,
sales,
ntile(4) over(ORDER BY sales DESC) AS '四分位_ntile'
FROM
store_sales;
・結果
store_id|sales|四分位_ntile|
--------|-----|---------|
A | 4350| 1|
B | 550| 1|
E | 450| 1|
D | 420| 2|
F | 420| 2|
C | 400| 2|
G | 400| 3|
J | 400| 3|
H | 320| 4|
I | 320| 4|
上記のように簡単にデータの四分位数を求められた。
■改善方法２−１：WITH句でサブクエリを整理する■
NTILEを用いない改善方法として、まずサブクエリの改善から
元のSQLの、「相関サブクエリと組み合わせてランキングを算出する」サブクエリを、WITH句で整理する。
・改善SQL
WITH sa1 AS(
/* ランキングを求める */
SELECT
S1.store_id,
S1.sales,
(
SELECT
COUNT(S2.sales)
FROM
store_sales AS S2
WHERE
S2.sales >= S1.sales
) ranking
FROM
store_sales AS S1
)
/* with句で求めたランキングに基づき四分位を求める */
SELECT
store_id,
sales,
ranking,
CASE
WHEN sa1.ranking <= 0.25 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 1
WHEN sa1.ranking <= 0.5 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 2
WHEN sa1.ranking <= 0.75 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 3
ELSE 4
END AS 四分位
FROM
sa1;
・結果
store_id|sales|ranking|四分位|
--------|-----|-------|---|
A | 4350| 1| 1|
B | 550| 2| 1|
C | 400| 8| 4|
D | 420| 5| 2|
E | 450| 3| 2|
F | 420| 5| 2|
G | 400| 8| 4|
H | 320| 10| 4|
I | 320| 10| 4|
J | 400| 8| 4|
■改善方法２−２：WITH句内の相関サブクエリをもっと簡単にする■
WITH句内で、相関サブクエリにより順位を求めているが、これをウィンドウ関数で順位を求める方法に変更。
・変更後SQL
WITH sa1 AS(
/* ランキングを求める */
SELECT
store_id,
sales,
rank() over(ORDER BY sales DESC) AS ranking
FROM
store_sales
)
/* with句で求めたランキングに基づき四分位を求める */
ー省略ー
・結果
store_id|sales|ranking|四分位|
--------|-----|-------|---|
A | 4350| 1| 1|
B | 550| 2| 1|
E | 450| 3| 2|
D | 420| 4| 2|
F | 420| 4| 2|
C | 400| 6| 3|
G | 400| 6| 3|
J | 400| 6| 3|
H | 320| 9| 4|
I | 320| 9| 4|
rank関数で求めたランキングで四分位を求めると、最初に求めた結果と異なってしまうので、このまま使えない。なのでrank関数以外のランキングを求める関数を次で見る。
■PERCENT_RANK関数とCUME_DIST関数によるランキング■
- PERCENT_RANK()：現在行の相対順位比率を (ランキング - 1) / (全行数 - 1 ) で求める
- CUME_DIST()：現在行の相対順位比率を (ランキング / 全行数) で求める
・２つのランキング関数の比較用SQL
/* ランキングを求める */
SELECT
store_id,
sales,
PERCENT_RANK() OVER(ORDER BY sales DESC) AS ranking_percent,
CUME_DIST() OVER(ORDER BY sales DESC) AS ranking_cume
FROM
store_sales;
・比率の関数による結果
store_id|sales|ranking_percent |ranking_cume|
--------|-----|------------------|------------|
A | 4350| 0.0| 0.1|
B | 550|0.1111111111111111| 0.2|
E | 450|0.2222222222222222| 0.3|
D | 420|0.3333333333333333| 0.5|
F | 420|0.3333333333333333| 0.5|
C | 400|0.5555555555555556| 0.8|
G | 400|0.5555555555555556| 0.8|
J | 400|0.5555555555555556| 0.8|
H | 320|0.8888888888888888| 1.0|
I | 320|0.8888888888888888| 1.0|
結果を見ると、PERCENT_RANKなどは比率を算出関数なので、順位のような整数の数値とは桁も形式も違う。しかし、同じ形式となるように形を整えて、１０倍（比率の計算では分母が行数なので、ここでは１０）することで使えそう。
・CUME_DIST関数の結果を１０倍してランキング化したSQL
WITH sa1 AS(
/* ランキングを求める */
SELECT
store_id,
sales,
ROUND(PERCENT_RANK() OVER(ORDER BY sales DESC) * 10, 2) AS ranking_percent,
(CUME_DIST() OVER(ORDER BY sales DESC) * 10) AS ranking_cume
FROM
store_sales
)
/* with句で求めたランキングに基づき四分位を求める */
SELECT
store_id,
sales,
ranking_percent,
ranking_cume,
CASE
WHEN sa1.ranking_cume <= 0.25 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 1
WHEN sa1.ranking_cume <= 0.5 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 2
WHEN sa1.ranking_cume <= 0.75 * (
SELECT
COUNT(*)
FROM
store_sales
) THEN 3
ELSE 4
END AS 四分位
FROM
sa1;
・比率を１０倍した結果
store_id|sales|ranking_percent|ranking_cume|四分位|
--------|-----|---------------|------------|---|
A | 4350| 0.0| 1.0| 1|
B | 550| 1.11| 2.0| 1|
E | 450| 2.22| 3.0| 2|
D | 420| 3.33| 5.0| 2|
F | 420| 3.33| 5.0| 2|
C | 400| 5.56| 8.0| 4|
G | 400| 5.56| 8.0| 4|
J | 400| 5.56| 8.0| 4|
H | 320| 8.89| 10.0| 4|
I | 320| 8.89| 10.0| 4|
欲しい順位の数値の形式にあわせ、比率を１０倍したら、意図通りの結果がでた。
次に、四分位を求めるSQLの改善
■スカラサブクエリ繰り返し表記の改善■
0コメント