目次
本記事概要
本記事の対象者
SQL初心者の方。
主にPMやマーケターの方向け。
Macをお持ちの方。
すでに【SQL練習】サンプルデータベースを用意するでMySQL公式のデータベースをローカルに作成されている方
本記事の目的
優良顧客の抽出ができるようになることです。
デシル分析という分析手法をご存知でしょうか？
特定の行動データを軸に、顧客を10等分にランク付けして、分析する方法のことを言います。
よく使われる行動データはやはり売上です。
顧客を売上順に10等分して、その属性や全体売上に対する比率から優良顧客の抽出を行うことができます。
今回は【SQL練習】サンプルデータベースを用意するで作成したpaymentテーブルを使って、売上を10等分に分け優良顧客を抽出する方法を解説します。
またクエリ作成過程でwindow関数とはどんな関数なのか説明していきます。
そもそもWindow関数（分析関数）とは？
ざっくり概念は分析関数（ウインドウ関数）をわかりやすく説明してみたの「はじめに」がめちゃくちゃわかりやすいので、そのまま引用させていただきますね（感謝）
まず分析関数とは何をするものか、ですが、一言で言うと集合関数と同じ集計動作をそれぞれの行に制限範囲で実行するものです。ここでいう集合関数とは、MAXやSUMやAVG等、GROUP BYと共に使い行をまとめるて集計計算する関数ですね。分析関数は集合関数と同様の計算をしますが、集合関数と違い行をまとめません。それぞれの行で集計計算し結果を返します。ここが集合関数との大きな違いです。
また、集合関数ではGROUP BYの同じカラム値をもつ全行を一つに集計しますが、分析関数では集計対象となる行の範囲を任意で指定できます。関数に続くOVER句でこの範囲指定を行います。集合関数と分析関数は基本同じ名前なので、よく使う一般的な集合関数は後ろにOVER句をつけれれば、分析関数になると考えてください（集合関数だけの関数や分析関数だけの関数もあります）。
別の見方をすると、それぞれの行ので分析関数によって計算された集計値というものは、その行以外の行を参照して得た値の集計結果です。たとえば、ある行を処理している時に、一つ前の行と一つ後の行から得た値の合計をだす等の処理が自己結合することなく可能になります。つまり、分析関数は行間参照する（他の行を参照できる）関数ということになります。
customerテーブルとpaymentテーブル概要
paymentテーブル
ごく一般的な内容です。
今回はこのpaymentテーブルのamountカラムをcustomer_idごとに10等分して、デシル1の顧客を抽出すると共に、パレートの法則（「80:20の法則」）が成りたつのどうか検証したいと思います。
パレートの法則は、働きアリの法則と同じ意味合いで使用されることが多く、組織全体の2割程の要人が大部分の利益をもたらしており、そしてその2割の要人が間引かれると、残り8割の中の2割がまた大部分の利益をもたらすようになるというものである。
（例）
・ビジネスにおいて、売上の8割は全顧客の2割が生み出している。よって売上を伸ばすには顧客全員を対象としたサービスを行うよりも、2割の顧客に的を絞ったサービスを行う方が効率的である。
・商品の売上の8割は、全商品銘柄のうちの2割で生み出している。
~wikipediaより引用~
デシル分析をしてみよう！
1 ユーザーをamountの多い順番に並べる。
2 並び替えたユーザーの上位から10%ずつデシル1 からデシル10 までのグループに割り当てる。
3 各デシルごとのトータルamount,平均amount,積み上げamountを計算します。
4 全体のamountに対して、各デシルでのamountの割合（構成比）を計算する。
5 上位から累積でどの程度の比率を占めるかの構成比累計を集計する。
ユーザーをamountごとに10等分して、10%ずつデシル1~10にグループを割り当てる（抽出イメージの①と②）
集計結果は以下になります。
ntile()
ntile()はWindow内を引数で指定した値で分割して現在行のランクを算出することができます。今回はnitle(10)なので、指定した値を10等分に分けるよう指定しています。
over句
今回のクエリのポイントになるのがこのover句です。
nitile関数ではover句でorder byを使いカラムを指定してx等分することができます。
今回はpayment_amountを指定して、10等分しています。
各デシルごとのトータルamount,平均amount,積み上げamountを計算します。（抽出イメージの③）
集計結果は以下になります。
今回のover句がポイントになります。
先ほどntile関数ではover句でカラムを指定して使用すると説明しましたが、
一般的には「行を順番に並べた上で、最初の行から現在行までのみを集計の対象にする」ことができます。
つまり行ごとに値が加算されていくことになります。
特にsumと併用されることが多く、今回は26行目の
sum(sum(payment_amount)) over (order by decile) as cumulative_amount の部分で使用されており、デシルごとにpayment_amountをsumしたものが加算されています。
逆に27行目でsum(sum(payment_amount)) over () as total_amount
ではover句でカラムを指定していないので、全行スキャンする形になり、どのデシルでも累計amountの67,416.51が表示されています。
全体のamountから構成比率を求め、上位からどの程度の割合を占めるのか集計する（抽出イメージの④と⑤）
集計結果は以下になります。
36,37行目に簡単な計算を入れただけなので、クエリ自体は問題ないかと思います。
まとめ
結果だけ見るとデシル1と2のユーザー比率を見ても、約27%とパレートの法則は見られなかったですね。笑
（これは実際のデータではなく、サンプルデータなのでまあそうだろうという話ですが・・・）
この結果を元に、デシル1ユーザーのcustomer_idを抽出して、メールアドレスを特定して、特別セールのQRコードを送り、ロイヤルユーザー化を目指すなどの施策は打てたりするかもしれませんね。
参考
