[SQL] Window関数を使ってデシル分析 〜ntile関数で優良顧客を抽出する〜

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

まずはじめにデシル分析とは
全顧客の購入金額を高い順に10等分にして各ランク(デシル)の購入比率や売り上げ構成比を算出して優良顧客を探ろうとする分析だそうです。

マーケティングの世界では「80:20の法則」や「2:8の法則」と呼ばれるパレートの法則というものがあるそうです。
Wikipediaによると
商品の売上の8割は、全商品銘柄のうちの2割で生み出している。
売上の8割は、全従業員のうちの2割で生み出している。
仕事の成果の8割は、費やした時間全体のうちの2割の時間で生み出している。
所得税の8割は、課税対象者の2割が担っている。
プログラムの処理にかかる時間の80%はコード全体の20%の部分が占める。
Wikipedia

デシル分析で上位20%の顧客を抽出できればマーケティングに活かせそうですね。

環境

PostgreSQL 9.5.4

実現したいこと

直近4カ月で利用したユーザーの中から利用料金の合計が上位2%に該当するユーザーを各月で抽出するという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;

2行目 利用月
3行目 ユーザーID
4行目 該当月における各ユーザーの利用料金
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%
となります。

ここで1度実行すると

    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
 以下省略

ちゃんと表示されています。
仮に上位2%のユーザーに施作を打つためにデータを抽出しているのだとすると、残りは不要なデータになるので表示する必要はないですね。

そこで上位2%だけを表示するために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句で判定して上位2%を抽出しています。
上位20%を表示したい場合は(decile <= 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-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

各月の上位2%のユーザーが表示されていますね。
ここで得られたユーザー向けに施策をうてば売り上げのアップが見込めそうですね。  

最後に

SQLが長くなると実行順序を意識しないと何がどうなっているのかわからなくなりますね。
Window関数やサブクエリーの実行順序を整理してご紹介できればと思います。  

参考

10年戦えるデータ分析入門