![[SQL] Window関数を使ってデシル分析 〜ntile関数で優良顧客を抽出する〜](https://devio2023-media.developers.io/wp-content/uploads/2015/07/sql.png)
[SQL] Window関数を使ってデシル分析 〜ntile関数で優良顧客を抽出する〜
はじめに
まずはじめにデシル分析とは
全顧客の購入金額を高い順に１０等分にして各ランク(デシル)の購入比率や売り上げ構成比を算出して優良顧客を探ろうとする分析だそうです。
マーケティングの世界では「80:20の法則」や「2:8の法則」と呼ばれるパレートの法則というものがあるそうです。
Wikipediaによると
商品の売上の8割は、全商品銘柄のうちの2割で生み出している。
Wikipedia
売上の8割は、全従業員のうちの2割で生み出している。
仕事の成果の8割は、費やした時間全体のうちの2割の時間で生み出している。
所得税の8割は、課税対象者の2割が担っている。
プログラムの処理にかかる時間の80%はコード全体の20%の部分が占める。
デシル分析で上位２０％の顧客を抽出できればマーケティングに活かせそうですね。
環境
PostgreSQL 9.5.4
実現したいこと
直近４カ月で利用したユーザーの中から利用料金の合計が上位２％に該当するユーザーを各月で抽出するというSQLを書いてみます。
※ サンプルのデータ数が多かったので2%を抽出するSQLと実行結果を表示していますが、20%で表示する方法も併記します
今回もいつも使っているサンプルデータのpaymentテーブルを使います。
手を動かしながら動作を確認したい方はこちらを参考にサンプルをご用意ください！
payment_id | customer_id | staff_id | rental_id | amount | payment_date
------------+-------------+----------+-----------+--------+----------------------------
17503 | 341 | 2 | 1520 | 7.99 | 2007-02-15 22:25:46.996577
17504 | 341 | 1 | 1778 | 1.99 | 2007-02-16 17:23:14.996577
17505 | 341 | 1 | 1849 | 7.99 | 2007-02-16 22:41:45.996577
17506 | 341 | 2 | 2829 | 2.99 | 2007-02-19 19:39:56.996577
17507 | 341 | 2 | 3130 | 7.99 | 2007-02-20 17:31:48.996577
17508 | 341 | 1 | 3382 | 5.99 | 2007-02-21 12:33:49.996577
17509 | 342 | 2 | 2190 | 5.99 | 2007-02-17 23:58:17.996577
17510 | 342 | 1 | 2914 | 5.99 | 2007-02-20 02:11:44.996577
17511 | 342 | 1 | 3081 | 2.99 | 2007-02-20 13:57:39.996577
17512 | 343 | 2 | 1547 | 4.99 | 2007-02-16 00:10:50.996577
17513 | 343 | 1 | 1564 | 6.99 | 2007-02-16 01:15:33.996577
17514 | 343 | 2 | 1879 | 0.99 | 2007-02-17 01:26:00.996577
17515 | 343 | 2 | 1922 | 0.99 | 2007-02-17 04:32:51.996577
17516 | 343 | 2 | 2461 | 6.99 | 2007-02-18 18:26:38.996577
17517 | 343 | 1 | 2980 | 8.99 | 2007-02-20 07:03:29.996577
17518 | 343 | 1 | 3407 | 0.99 | 2007-02-21 14:42:28.996577
17519 | 344 | 1 | 1341 | 3.99 | 2007-02-15 10:54:44.996577
17520 | 344 | 2 | 1475 | 4.99 | 2007-02-15 19:36:27.996577
17521 | 344 | 1 | 1731 | 0.99 | 2007-02-16 14:00:38.996577
17522 | 345 | 2 | 1210 | 0.99 | 2007-02-15 01:26:17.996577
17523 | 345 | 1 | 1457 | 4.99 | 2007-02-15 18:34:15.996577
17524 | 345 | 2 | 1550 | 0.99 | 2007-02-16 00:27:01.996577
17525 | 345 | 2 | 2766 | 4.99 | 2007-02-19 16:13:41.996577
17526 | 346 | 1 | 1994 | 5.99 | 2007-02-17 09:35:32.996577
以下省略 14596rows
このテーブルを分析しやすいように以下のように表示します。
payment_month | customer_id | sum | decile
---------------------+-------------+--------+-------
2007-02-01 00:00:00 | 454 | 52.90 | 1
2007-02-01 00:00:00 | 178 | 44.92 | 1
2007-02-01 00:00:00 | 176 | 42.92 | 1
2007-02-01 00:00:00 | 26 | 41.93 | 1
2007-02-01 00:00:00 | 526 | 41.91 | 1
2007-02-01 00:00:00 | 550 | 39.94 | 1
2007-02-01 00:00:00 | 267 | 38.91 | 2
2007-02-01 00:00:00 | 236 | 37.92 | 2
2007-02-01 00:00:00 | 234 | 36.92 | 2
2007-02-01 00:00:00 | 403 | 35.94 | 2
2007-02-01 00:00:00 | 193 | 35.94 | 2
2007-02-01 00:00:00 | 431 | 35.93 | 2
2007-03-01 00:00:00 | 148 | 87.82 | 1
2007-03-01 00:00:00 | 410 | 86.83 | 1
2007-03-01 00:00:00 | 21 | 79.83 | 1
2007-03-01 00:00:00 | 15 | 79.82 | 1
2007-03-01 00:00:00 | 119 | 77.82 | 1
2007-03-01 00:00:00 | 526 | 76.87 | 1
2007-03-01 00:00:00 | 147 | 76.85 | 2
2007-03-01 00:00:00 | 259 | 75.84 | 2
2007-03-01 00:00:00 | 137 | 74.85 | 2
2007-03-01 00:00:00 | 144 | 72.84 | 2
2007-03-01 00:00:00 | 569 | 72.83 | 2
2007-03-01 00:00:00 | 468 | 71.86 | 2
2007-04-01 00:00:00 | 148 | 100.78 | 1
2007-04-01 00:00:00 | 522 | 97.81 | 1
2007-04-01 00:00:00 | 470 | 96.83 | 1
2007-04-01 00:00:00 | 137 | 96.81 | 1
2007-04-01 00:00:00 | 144 | 93.82 | 1
2007-04-01 00:00:00 | 459 | 89.82 | 1
2007-04-01 00:00:00 | 526 | 89.80 | 2
2007-04-01 00:00:00 | 257 | 88.82 | 2
2007-04-01 00:00:00 | 295 | 88.81 | 2
2007-04-01 00:00:00 | 595 | 86.81 | 2
2007-04-01 00:00:00 | 178 | 85.82 | 2
2007-04-01 00:00:00 | 366 | 85.80 | 2
2007-05-01 00:00:00 | 60 | 9.98 | 1
2007-05-01 00:00:00 | 75 | 8.97 | 1
2007-05-01 00:00:00 | 267 | 7.98 | 2
2007-05-01 00:00:00 | 53 | 7.98 | 2
sumは各月の利用料金の合計
decileは利用料金の合計が全ユーザの中で上位何%に該当するのかを表示しています。
実践
SQLが少し長くなりますので順を追って説明します。
まず各月のユーザーの利用料金が必要になるので,利用月と利用料金でグループ分けが必要ですね。
※ date_trunc関数がわからない方はこちらをご覧ください
select
*
from
payment
group by
customer_id
,date_trunc('month',payment_date);
次に表示したい項目は利用月、ユーザー、ユーザーが各月に利用した合計金額とそのユーザーが上位何％に該当するかなので
select
date_trunc('month',payment_date) as payment_month
,customer_id
,sum(amount)
from
payment
group by
customer_id
,date_trunc('month',payment_date)
order by
payment_month;
２行目 利用月
３行目 ユーザーID
４行目 該当月における各ユーザーの利用料金
11行目 並び順を指定
ここで実行してもまだ表示されているユーザーが全体の中で上位何％なのかは表示されていません。
次に
select
date_trunc('month',payment_date) as payment_month
,customer_id
,sum(amount)
,ntile(100) over (
partition by date_trunc('month',payment_date)
order by sum(amount) desc
) as decile
from
payment
group by
customer_id
,date_trunc('month',payment_date)
order by
payment_month;
このntile()というのはWindow関数(分析関数)と呼ばれるもので 詳しくはこちら
PartitionBy句で分けられたグループ内をntile()の引数の値で分割し、全体の中でどこに位置するのかを表示します。
ntile(10)とすればdecile
カラムの値が 1~2 が上位20％
ntile(100)とすればdecileカラムの値が 1~20 が上位20％
となります。
ここで１度実行すると
payment_month | customer_id | sum | decile
---------------------+-------------+--------+--------
2007-02-01 00:00:00 | 454 | 52.90 | 1
2007-02-01 00:00:00 | 178 | 44.92 | 1
2007-02-01 00:00:00 | 176 | 42.92 | 1
2007-02-01 00:00:00 | 26 | 41.93 | 1
2007-02-01 00:00:00 | 526 | 41.91 | 1
2007-02-01 00:00:00 | 550 | 39.94 | 1
2007-02-01 00:00:00 | 267 | 38.91 | 2
2007-02-01 00:00:00 | 236 | 37.92 | 2
2007-02-01 00:00:00 | 234 | 36.92 | 2
2007-02-01 00:00:00 | 403 | 35.94 | 2
2007-02-01 00:00:00 | 193 | 35.94 | 2
2007-02-01 00:00:00 | 431 | 35.93 | 2
2007-02-01 00:00:00 | 213 | 35.91 | 3
2007-02-01 00:00:00 | 31 | 35.89 | 3
2007-02-01 00:00:00 | 341 | 34.94 | 3
2007-02-01 00:00:00 | 384 | 34.93 | 3
2007-02-01 00:00:00 | 260 | 34.92 | 3
2007-02-01 00:00:00 | 25 | 33.94 | 3
2007-02-01 00:00:00 | 576 | 33.93 | 4
2007-02-01 00:00:00 | 510 | 33.92 | 4
2007-02-01 00:00:00 | 561 | 33.91 | 4
2007-02-01 00:00:00 | 571 | 32.94 | 4
2007-02-01 00:00:00 | 46 | 32.93 | 4
以下省略
ちゃんと表示されています。
仮に上位２％のユーザーに施作を打つためにデータを抽出しているのだとすると、残りは不要なデータになるので表示する必要はないですね。
そこで上位２％だけを表示するためにWhere句で指定します。
select ④
date_trunc('month',payment_date) as payment_month
,customer_id
,sum(amount)
,ntile(100) over (
partition by date_trunc('month',payment_date)
order by sum(amount) desc
) as decile
from ①
payment
where ②
decile <= 2
group by ③
customer_id
,date_trunc('month',payment_date)
order by ⑤
payment_month;
上のように書いたらwhere句のところで「そんなカラムないよ」と怒られてしまいます。
原因はSQLの実行順序です。SQLの実行順序を丸数字で書きましたが、上のSQLを実行した場合Where句を実行するタイミングはntile関数を実行するタイミングよりも早いので、Where句で指定したカラムがないのです。(SQLの実行順序については詳しく知りたい方はこちらをご覧ください)
なので今回はサブクエリーを使って実現します。
select ⑦
*
from ⑤
(select ③
date_trunc('month',payment_date) as payment_month
,customer_id
,sum(amount)
,ntile(100) over (
partition by date_trunc('month',payment_date)
order by sum(amount) desc
) as decile
from ①
payment
group by ②
customer_id
,date_trunc('month',payment_date)
order by ④
payment_month
) as tmp
where ⑥
decile <= 2;
サブクエリー内で先にntile関数を実行して、その結果をWhere句で判定して上位２％を抽出しています。
上位20％を表示したい場合は(decile <= 20)とすれば上位２0％になります。
payment_month | customer_id | sum | decile
---------------------+-------------+--------+-------
2007-02-01 00:00:00 | 454 | 52.90 | 1
2007-02-01 00:00:00 | 178 | 44.92 | 1
2007-02-01 00:00:00 | 176 | 42.92 | 1
2007-02-01 00:00:00 | 26 | 41.93 | 1
2007-02-01 00:00:00 | 526 | 41.91 | 1
2007-02-01 00:00:00 | 550 | 39.94 | 1
2007-02-01 00:00:00 | 267 | 38.91 | 2
2007-02-01 00:00:00 | 236 | 37.92 | 2
2007-02-01 00:00:00 | 234 | 36.92 | 2
2007-02-01 00:00:00 | 403 | 35.94 | 2
2007-02-01 00:00:00 | 193 | 35.94 | 2
2007-02-01 00:00:00 | 431 | 35.93 | 2
2007-03-01 00:00:00 | 148 | 87.82 | 1
2007-03-01 00:00:00 | 410 | 86.83 | 1
2007-03-01 00:00:00 | 21 | 79.83 | 1
2007-03-01 00:00:00 | 15 | 79.82 | 1
2007-03-01 00:00:00 | 119 | 77.82 | 1
2007-03-01 00:00:00 | 526 | 76.87 | 1
2007-03-01 00:00:00 | 147 | 76.85 | 2
2007-03-01 00:00:00 | 259 | 75.84 | 2
2007-03-01 00:00:00 | 137 | 74.85 | 2
2007-03-01 00:00:00 | 144 | 72.84 | 2
2007-03-01 00:00:00 | 569 | 72.83 | 2
2007-03-01 00:00:00 | 468 | 71.86 | 2
2007-04-01 00:00:00 | 148 | 100.78 | 1
2007-04-01 00:00:00 | 522 | 97.81 | 1
2007-04-01 00:00:00 | 470 | 96.83 | 1
2007-04-01 00:00:00 | 137 | 96.81 | 1
2007-04-01 00:00:00 | 144 | 93.82 | 1
2007-04-01 00:00:00 | 459 | 89.82 | 1
2007-04-01 00:00:00 | 526 | 89.80 | 2
2007-04-01 00:00:00 | 257 | 88.82 | 2
2007-04-01 00:00:00 | 295 | 88.81 | 2
2007-04-01 00:00:00 | 595 | 86.81 | 2
2007-04-01 00:00:00 | 178 | 85.82 | 2
2007-04-01 00:00:00 | 366 | 85.80 | 2
2007-05-01 00:00:00 | 60 | 9.98 | 1
2007-05-01 00:00:00 | 75 | 8.97 | 1
2007-05-01 00:00:00 | 267 | 7.98 | 2
2007-05-01 00:00:00 | 53 | 7.98 | 2
各月の上位２％のユーザーが表示されていますね。
ここで得られたユーザー向けに施策をうてば売り上げのアップが見込めそうですね。
最後に
SQLが長くなると実行順序を意識しないと何がどうなっているのかわからなくなりますね。
Window関数やサブクエリーの実行順序を整理してご紹介できればと思います。
参考
１０年戦えるデータ分析入門
