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

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

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

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

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

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

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

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

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

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

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

リスト2　前レコードの地域と比較してグループの境界を判定（Javaでのコードイメージ）

ResultSet r = s.executeQuery(sql);
String preTiiki = ""; // 前レコードの地域

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

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

そうすると比較に使用する一時変数も増え、さらに煩雑なロジックになります。
もちろんしっかりとテストを行えばバグは防げるでしょうが、設計～テスト工程までにかかる労力は馬鹿になりません。
 
しかし、PARTITION BYを応用すればこの負担を劇的に減らすことができます。
PARTITION BYは、グループ分けをしますが集約はしない、そのため、グループ内で各レコードに順位をつけることができるということでした。
順位がわかるということは、グループ内のレコード数もわかるということです。
これが二郎の地域グループの境界を教えてくれます。
 
それでは実際のSQLを見てみましょう。
 
リスト3　PARTITION BYで二郎の地域別売上順位に加えて地域別件数を取得
SELECT
  年月, 地域, 店舗名, 売上額,
  ROW_NUMBER() OVER (PARTITION BY 地域 ORDER BY 売上額 DESC) AS 地域別売上順位,
　COUNT(*) OVER (PARTITION BY 地域) AS 地域別件数
FROM
  売上
WHERE
  年月='201810'
ORDER BY
  地域, 地域別売上順位;

 


【リスト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でのコードイメージ）
ResultSet r = s.executeQuery(sql);

// SQLで取得した結果をフェッチしてループ
while (r.next()){
  // グループ内の1レコード目か？
　if( r.getString("地域別売上順位") = 1) ){
　　　// 該当地域の新規ファイルオープン
　　　～～～
  }
　// ファイルに出力 
　～～～
  // グループ内の最終レコードか？
　if( r.getString("地域別売上順位") = r.getString("地域別レコード数") ){
　　　// ファイルクローズ
　　　～～～
  }
}
 
煩わしい一時変数が消え、今処理しているレコードの情報だけで境界が判断できるようになりました。
 
 
これまで暗闇の中を手探りで進んでいたところに、光がさした気分にならないでしょうか？
 
このように二郎の売上分析にも使えるPARTITION BY、ぜひ活用されてみてください。
 
 
※文中の実行環境はPostgreSQL9.6を使用しています。
 
The following two tabs change content below.
この記事を書いた人
最新の記事

	

		

		
		
		

			
アフロ・ナミヘイ

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

		

		
		

			

				
最新記事 by アフロ・ナミヘイ (全て見る)
			
			

				

					AWSを不正利用されて高額な請求がきた話 - 2022年6月23日 
				
				

					【アプリエンジニア向けAWS基礎知識】AWSと冗長化における基本の基 - 2021年10月11日 
				
				

					【アプリエンジニア向けSEO基礎知識】マイナス評価を回避するSEO施策 - 2021年9月17日 
				
		
	
		
	
			

			

			« 前の記事			
			
			

			次の記事 »