デシル分析
デシル分析とは、例えば全顧客の購入金額を高い順に10等分し、上位2割など貢献度の高い優良顧客グループを知ることで広告宣伝費の投資を効果的に行うべき対象を特定するための分析です。「80対20の法則」などで知られるパレートの法則では売り上げの8割は顧客の2割から生じていることが知られています。下にパレート図を示します。デシル1と2の顧客グループに特徴があれば（例えば性別や年代に偏りがあれば）そのグループに対し集中して投資を行うなどの対策ができます。なお、デシルの「デシ」は「10分の1」という意味です。
ここではSQLによって購入金額順に顧客を10のグループに分ける方法を確認していきます。
SQLサンプルと解説
ここでは購買履歴のテーブルを使います。購買履歴でユーザーごとに購買額を合計し、それを10段階にランク付けして表示します。
得たい結果のテーブルは以下の通りです。
user_id | sum_price | decile ----------+-----------+-------- UID0004 | 2010 | 1 UID0003 | 1760 | 1 UID0002 | 1030 | 2 UID0009 | 980 | 2 UID0011 | 350 | 3 UID0006 | 250 | 4 UID0012 | 250 | 5 UID0005 | 250 | 6 UID0008 | 250 | 7 UID0001 | 250 | 8 UID0007 | 250 | 9 UID0010 | 200 | 10
SQLはリスト6のようになります。
select
user_id,
sum_price,
ntile(10) over (/* （2） */
order by sum_price desc
) as decile
from(/* （1） */
select user_id,sum(price) as sum_price
from sales_history
group by user_id
)sales_amount
;
ここでも関数やサブクエリが使われているため複雑に思えますが、順を追って見ていきましょう。
サブクエリの中身から確認します。
（1）ユーザーごとに料金の合計を求める
ここではユーザーごとに購買時の料金を合計し、sum_price列を求めています。途中結果は以下の通りです。
user_id | sum_price ----------+----------- UID0004 | 2010 UID0003 | 1760 UID0009 | 980 UID0005 | 250 UID0011 | 350 UID0008 | 250 UID0001 | 250 UID0010 | 200 UID0007 | 250 UID0006 | 250 UID0002 | 1030 UID0012 | 250
（2）合計料金からデシルを割り振る
上記で求めたsum_priceの値をもとに、行を10分割してデシル1～10を割り振ります。この際にウィンドウ関数（以下のコラムを参照）のntile(10)を用います。
ntile関数の構文は以下の通り。<値>の箇所には正の整数が入り、この値で分割します。デシル分析の場合は10分割なので10を記述します。
項目1でグループ化し（オプション）、項目2でソート後にデシル（1や10など）を割り振ります。
NTILE ( <値> ) OVER ([ PARTITION BY <項目1> ][ ORDER BY <項目2> ])
この例ではsum_priceに対してntile関数を適用しています。これで購入合計額ごとの順位（decile）を求めることができました。
［コラム］ウィンドウ関数（分析関数）
ntile関数や先述のrow_number関数はウィンドウ関数と呼ばれています。GROUP BYによる集計がグループをまとめる（行が減る）のに対して、ウィンドウ関数の場合、行はそのままで算出結果の列が増えます。別途集計結果を列に追加できるため分析で活躍します。PostgreSQLのほかOracle・SQL Serverといった主なDBMSに実装されています（OracleやSQL Serverでは分析用途で利用されることから、分析関数と呼ばれる場合が多いようです）。MySQLには残念ながらウィンドウ関数は実装されておらず利用できません。
デシル分析をさらに分割したグループで行う
さらに、購買の月ごとに分割して集計結果を確認したい場合はどうすればいいでしょうか。SQLはリスト7のようになります。
SQL
select
sales_month,
user_id,
sum_price,
ntile(10) over (
partition by sales_month/* （2） */
order by sum_price desc
) as decile
from(
select
extract(month from date) as sales_month,/* （1） */
user_id,
sum(price) as sum_price
from sales_history
group by
user_id,
sales_month
)sales_amount
;
- （1）日付から月のデータにまるめてsales_monthとして集計します。
- （2）ntile関数の対象としてさらにsales_monthを指定します。
結果は以下の通りです。
sales_month | user_id | sum_price | decile
-------------+----------+-----------+--------
3 | UID0009 | 980 | 1
3 | UID0004 | 980 | 2
3 | UID0002 | 780 | 3
3 | UID0003 | 780 | 4
3 | UID0011 | 350 | 5
3 | UID0007 | 250 | 6
3 | UID0008 | 250 | 7
3 | UID0010 | 200 | 8
4 | UID0004 | 1030 | 1
4 | UID0003 | 980 | 2
4 | UID0001 | 250 | 3
4 | UID0005 | 250 | 4
4 | UID0006 | 250 | 5
4 | UID0002 | 250 | 6
4 | UID0012 | 250 | 7
3月は「UID0009」「UID0004」が上位顧客で、4月は「UID0004」「UID0003」が上位顧客と判明しました。季節ごとに上位顧客の行動が異なる場合や、分析する場合などに役立ちます。
まとめ
今回は、データ解析でよく利用されるクロス集計・デシル分析をSQLで実行する方法を取り上げました。
分析の場合、サブクエリや関数を活用する場合が多く、通常利用されるSQLよりも分かりづらいと感じるかもしれません。ただ、サブクエリの結果など途中経過を確認することで理解しやすくなると思います。
次回はバスケット分析・時系列分析を解説する予定です。
