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