BLOG

2018-11-09

【SQLの神髄】第3回 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を使用しています。

 


The following two tabs change content below.

アフロ・ナミヘイ

PostgreSQL推進委員長。アンチOracle派筆頭。 標準SQLに反する書式は許しません。 (+)とか使ってると激おこ。 アフロは天然。