Blog

ブログ

どうも、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を使用しています。