「2：8の法則」をご存知でしょうか。顧客全体の2割である優良顧客が売上の8割をあげているという法則のことで、集団の売上・報酬・評価が一部の構成員に集中することを指します。イタリアの経済学者ビルフレッド・パレート（1848～1923）が1880年代の欧州の経済統計から「個人の所得額」と「その所得額以上の所得を得ている人の数」との間に見出した法則から、別名パレートの法則とも呼ばれたりします。
この法則は売上に限ったことではなく、プロダクトの利用ユーザーなど色んな所で活用できます。データソースの中から最もログイン回数が多いユーザー上位何件までを取得して特徴を可視化したり、SNSであればお気に入り回数が多い層を抽出したり色んな所で役に立ちます。
今回はデータの中から上位N件、N位を抽出するクエリをご紹介します。今回はtoCサービスを運用しているとして、最も購入金額が多いユーザーを取得することにします。
利用するテーブル
下記のようなユーザーと購入金額が書かれたテーブルがあるとします。これくらいの量であれば簡単に集計することができますが、実際は数万・数百万とレコードが存在するため目視で探すのは困難です。
| ユーザーネーム | 累計購入金額 |
| user1 | 12000 |
| user5 | 4800 |
| user8 | 3500 |
| user2 | 8000 |
| user9 | 3000 |
| user7 | 4000 |
| user3 | 6500 |
| user10 | 2500 |
| user6 | 4300 |
| user4 | 5500 |
データから上位N件を抽出する
例えば累計購入金額が高い上位3件のデータをテーブルから抜き出す場合ORDER BYで並び替えをしてLIMITで制限するという方法があります。シンプルで使いやすいですが、上位もしくは下位しか使えないのが難点です。
SELECT *
FROM テーブル名
ORDER BY 累計購入金額 DESC
LIMIT 3;
上位3〜5番目のデータを抜き出す時
OFFSETを使うとLIMITを使い抜き出すことができます。
OFFSETは、一定数の行をスキップして、その後の行を表示するために使用されるSQLステートメントです。 OFFSET句は、LIMIT句と一緒に使用され、LIMIT句が表示する行数を指定し、OFFSET句が最初の行からスキップする行数を指定します。
例えば、LIMIT 5 OFFSET 2 というクエリを実行する場合、最初の2行をスキップして、3行目から5行目までの結果が返されます。OFFSET句を使うことで、データから必要な行だけを取得し、パフォーマンスを向上させることができます。
SELECT *
FROM テーブル名
ORDER BY 累計購入金額 DESC
LIMIT 5
OFFSET 2;
LIMIT句をCOUNTして上位10%を抜き出す
SELECT *
FROM テーブル名
ORDER BY 累計購入金額 DESC
LIMIT ROUND(COUNT(*) * 0.1);
LIMIT句にCOUNTを使うと上位10％を集計することもできます
ROW_NUMBER()関数を使い、上位N件をランキング化する
今まではLIMITを駆使して使いましたが、今回のカラムが購入金額であったりした場合やランキングを作りたい時などには不向きです。そんなときに役立つのがROW_NUMBER関数になります。
SELECT ユーザーネーム, SUM(購入金額) AS 累計購入金額, ROW_NUMBER() OVER(ORDER BY SUM(購入金額) DESC) AS ランキング
FROM テーブル名
GROUP BY ユーザーネーム
ORDER BY 累計購入金額 DESC
LIMIT 3;
ROW NUMBER関数は、クエリの結果に基づいて、各行に一意の番号を割り当てます。この関数は、ランキングやインデックスなどの目的で使用されます。たとえば、結果の最初の行には「1」、2番目の行には「2」などの番号が割り当てられます。ROW NUMBER関数を使うと合計金額の高い順や、平均購入金額の高い順にランキングを作るなどもできるようになります。
ちなみに似たような関数にRANKがありますが、RANKの場合は同じ値の時に順位をスキップします。1位,1位,3位のようになります。
■関連記事→購入周期を調べたい時にはこちらの記事も役に立ちます。
【SQL】LAG関数とOVER関数を使い、購入周期や累積購入金額を計算する - 元エンジニア/現シニアPMのPDMお役立ち情報
