Blog

ブログ

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

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

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

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

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

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

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

リスト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の動きは以下のようなイメージで表せます。

　　　　　　　　　　　↓

これに対して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 ～」部分にはソートに使用するカラムを指定します。

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

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

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

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

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