Blog

ブログ

2018.12.20

【SQLの神髄】第4回 PARTITION BYで二郎の地域グループ境界を知る

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

 

前回はラーメン二郎の売り上げデータを、地域別のグループに分けてランキングを出したいというケースを紹介しました。

 

今回も同様に以下のラーメン二郎各店舗における年月ごとの売上データを扱います。

 

【売上】

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

 

今回は、さらに取得した売上データをラーメン二郎の店舗が属する地域別にファイルに出力したいというケースを考えてみたいと思います。

現場でも一括して取得した大量のデータを、店舗や地域といった要素でファイルを分けて出力したい、といったケース、あると思います。

 

この場合に苦労する要因が、データを1行ずつループで見ていく際に、

どこで地域が変わるのか?

そして地域が変わることで、今書き込んでいるファイルをクローズして新しい地域のファイルを作らなければならないのか?
ということが次のレコードを見るまでわからないという点です。

 

大量データで1行ずつフェッチしてレコードを見ていく場合、次のレコードの値は次のループに進まなければわからないので、今処理したレコードの地域を一時変数に保持しておいて、次のレコードを取り出して地域を比較して~、という面倒な処理が必要になってしまいます。

具体的には、以下のようなコードのイメージです。

 

 

リスト2 前レコードの地域と比較してグループの境界を判定(Javaでのコードイメージ)
[sql]
ResultSet r = s.executeQuery(sql);
String preTiiki = ""; // 前レコードの地域

// SQLで取得した結果をフェッチしてループ
while (r.next()){
  // 前レコードの地域と違ったら別ファイルに出力
  if( preTiiki ≠ r.getString("地域") ){
   // ファイルクローズして新規ファイルオープン
   ~~~
  } 
 // ファイルに出力
 ~~~
 // 現レコードの地域を比較用変数に退避
 preTiiki = r.getString("地域");
} 
[/sql]

 

この例ではグループ分けに使用される項目は地域だけですが、現場ではこれが3項目の組み合わせ(例えば店舗、取引先、商品区分)になるといったケースもあると思います。

そうすると比較に使用する一時変数も増え、さらに煩雑なロジックになります。

もちろんしっかりとテストを行えばバグは防げるでしょうが、設計~テスト工程までにかかる労力は馬鹿になりません。

 

しかし、PARTITION BYを応用すればこの負担を劇的に減らすことができます。

PARTITION BYは、グループ分けをしますが集約はしない、そのため、グループ内で各レコードに順位をつけることができるということでした。

順位がわかるということは、グループ内のレコード数もわかるということです。

これが二郎の地域グループの境界を教えてくれます。

 

それでは実際のSQLを見てみましょう。

 

リスト3 PARTITION BYで二郎の地域別売上順位に加えて地域別件数を取得
[sql]
SELECT
  年月, 地域, 店舗名, 売上額,
  ROW_NUMBER() OVER (PARTITION BY 地域 ORDER BY 売上額 DESC) AS 地域別売上順位,
 COUNT(*) OVER (PARTITION BY 地域) AS 地域別件数
FROM
  売上
WHERE
  年月='201810'
ORDER BY
  地域, 地域別売上順位;
[/sql]

 

【リスト3 実行結果】

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

 

これで二郎の地域グループごとのレコード件数を、各レコードに持つことができました。

23区西なら二郎は4店舗、多摩なら3店舗というようにです。

 

こうすると、これらのレコードをループで処理していくとき、「地域別売上順位=地域別レコード数」であれば地域グループの境界と判断することができます。

リスト3の結果でいうと、荻窪店が23区西グループの最終レコードであり、立川店が多摩地区の最終レコードだということが「地域別売上順位=地域別レコード数」により判断できます。

これにより、リスト2のコードは以下のように変えられます。

 

リスト4 現レコードの情報のみでグループの境界を判定できる(Javaでのコードイメージ)
[sql]ResultSet r = s.executeQuery(sql);

// SQLで取得した結果をフェッチしてループ
while (r.next()){
  // グループ内の1レコード目か?
 if( r.getString("地域別売上順位") = 1) ){
   // 該当地域の新規ファイルオープン
   ~~~
  }
 // ファイルに出力 
 ~~~
  // グループ内の最終レコードか?
 if( r.getString("地域別売上順位") = r.getString("地域別レコード数") ){
   // ファイルクローズ
   ~~~
  }
}[/sql]

 

煩わしい一時変数が消え、今処理しているレコードの情報だけで境界が判断できるようになりました。

 

 

これまで暗闇の中を手探りで進んでいたところに、光がさした気分にならないでしょうか?

 

このように二郎の売上分析にも使える[sql][/sql]PARTITION BY、ぜひ活用されてみてください。

 

 

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

 

この記事を書いた人

アフロ・ナミヘイ

アフロ・ナミヘイ

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

結びのメッセージ

HOMEGROWINロゴ背景

Switch with us!

採用エントリー

ご応募はこちら