【SQLの神髄】第３回　PARTITION BYで導くグループ別の二郎売上ランキング

どうも、PostgreSQL推進委員会のアフロ・ナミヘイです。

今回はPARTITION BYを使ってグループ別のランキングを取得するテクニックをご紹介します。

 

以下の売上テーブルにはラーメン二郎の各店舗の売上額が年月別に記録されています。
正規化されたモデル設計ではありませんが、説明をわかりやすくするためこのようなテーブルとしています。

 

【売上】

年月	地域	店舗名	売上額
201810	23区西	三田	9000000
201810	23区西	目黒	8000000
201810	23区西	品川	7000000
201810	23区西	荻窪	6500000
201810	多摩	野猿	6000000
201810	多摩	府中	5000000
201810	多摩	立川	4000000

 

さて、ここで二郎の売上ランキングを23区西や多摩といった地域別に出したいとします。
現場でも何らかのデータを地域や国、年齢層など様々なグループに分けてランキングを出したいといったケース、あると思います。
最終的に欲しいのは以下の結果です。

 

【出力：地域別売上順位を付与】

年月	地域	店舗名	売上額	地域別売上順位
201810	23区西	三田	9000000	1
201810	23区西	目黒	8000000	2
201810	23区西	品川	7000000	3
201810	23区西	荻窪	6500000	4
201810	多摩	野猿	6000000	1
201810	多摩	府中	5000000	2
201810	多摩	立川	4000000	3

 

こんなときに現場で起こりがちなのが、まずは対象レコード全体を地域、売上額でソートした結果をSQLで取得するという事件です。
そして、以下のように取得した結果をループさせて地道に地域別の順位を振っていく、などということをやっていないでしょうか？

 

リスト1　対象レコード全体を地域、売上額でソート

SELECT
   年月, 地域, 店舗名, 売上額 
FROM
   売上
WHERE
   年月='201810' 
ORDER BY
   地域, 売上額 DESC;

【リスト1 実行結果】

年月	地域	店舗名	売上額
201810	23区西	三田	9000000
201810	23区西	目黒	8000000
201810	23区西	品川	7000000
201810	23区西	荻窪	6500000
201810	多摩	野猿	6000000
201810	多摩	府中	5000000
201810	多摩	立川	4000000

 

リスト2　地域別売上順位をループ処理で付与（Javaでのコードイメージ）

ResultSet r = s.executeQuery(sql);
String preTiiki = ""; // 前レコードの地域
int rankByTiiki = 1; // 地域別売上順位

// SQLで取得した結果をループ
while (r.next())
  // 前レコードの地域と違ったら順位を初期化
  if( preTiiki ≠ r.getString("地域") ){
　　　rankByTiiki = 1;
  } else {
　　　rankByTiiki++;
　}
　// 地域を比較用変数に退避して現レコードの地域別売上順位を設定
　preTiiki = r.getString("地域");
　～～～
} 

 

この面倒なループ処理を不要にするのがPARTITION BYです。
PARTITION BYとは何か？ですが、
一言でいうと「集約しないGROUP BY」です。
当連載の第1回、第2回でGROUP BYを使ったテクニックを紹介してきましたが、GROUP BYの動きは以下のようなイメージで表せます。

【GROUP BY 年月,地域 による集約イメージ】

年月	地域	店舗名	売上額
201810	23区西	三田	9000000
201810	23区西	目黒	8000000
201810	23区西	品川	7000000
201810	23区西	荻窪	6500000
201810	多摩	野猿	6000000
201810	多摩	府中	5000000
201810	多摩	立川	4000000

　　　　　　　　　　　↓

年月	地域	店舗名	売上額
201810	23区西	※MAX関数などで1行に集約する必要あり
201810	多摩	※MAX関数などで1行に集約する必要あり

 

これに対してPARTITION BYは、年月と地域でグループ分けをしますが集約はしません。
そのため、各地域で分けたグループ内で二郎各店舗に売上順位をつけることができます。
では実際のSQLを見てみましょう。

 

リスト3　PARTITION BYで地域別売上順位を取得
SELECT
  年月, 地域, 店舗名, 売上額,
  ROW_NUMBER() OVER (PARTITION BY 地域 ORDER BY 売上額 DESC) AS 地域別売上順位
FROM
  売上
WHERE
  年月='201810'
ORDER BY
  地域, 地域別売上順位;

 

「PARTITION BY ～」部分にはグループ分けに使用するカラムを、「ORDER BY ～」部分にはソートに使用するカラムを指定します。

実行結果は以下のようになります。

 

【リスト3 実行結果】

年月	地域	店舗名	売上額	地域別売上順位
201810	23区西	三田	9000000	1
201810	23区西	目黒	8000000	2
201810	23区西	品川	7000000	3
201810	23区西	荻窪	6500000	4
201810	多摩	野猿	6000000	1
201810	多摩	府中	5000000	2
201810	多摩	立川	4000000	3

 

いかがでしたでしょうか？

 

SQLを有効活用するとサーバサイドでの面倒な処理を簡単に済ませられる場合があります。
特に一括して取得したレコードをループで処理するケースは要注意です。
あなたの書いているそのコード、実は無駄かもしれません。

次回は更に便利なPARTITION BYの使い方をご紹介します。

 

 

※文中の実行環境はPostgreSQL9.6を使用しています。