これをマスター

SQLCASEでピボット分析をマスターしましょう！

マスター 売上をピボットしよう

	SELECT [取引ID], [取引日], [店舗ID], [商品ID], CASE WHEN [合計金額] >= 300 THEN ‘300円以上’ WHEN [合計金額] >= 500 THEN ‘500円以上’- WHEN [合計金額] >= 1000 THEN >1000円以上’ ELSE ‘300円未満’ END レンジ FROM sales
テーブル: sales
	取引ID	取引日	店舗ID	商品ID	個数	合計金額
1	7	4/1/20	3	52	1	250
2	11	4/1/20	3	27	2	700
3	19	4/1/20	3	46	2	500
4	32	4/1/20	3	23	2	500
5	33	4/1/20	3	34	1	245
6	39	4/1/20	3	32	1	300
7	50	4/1/20	3	49	2	600
8	53	4/1/20	3	60	1	375
9	59	4/1/20	3	51	2	600
10	62	4/1/20	3	49	2	600
結果: (スペース都合で5行のみ表示)
	取引ID	取引日	店舗ID	商品ID	合計金額	レンジ
1	7	4/1/20	3	52	250	300円未満
2	11	4/1/20	3	27	700	500円以上
3	19	4/1/20	3	46	500	500円以上
4	32	4/1/20	3	23	500	500円以上
5	33	4/1/20	3	34	245	300円未満

テーマ

はじめに

CASEの説明をネットには「複数のステートメントのグループのいずれかを実行」などとあります、、、
うーん。なんかすっと入って来ないですね

では、簡単にお天気の例で言い変えてみましょう
「明日、晴れだったらピクニック
雨だったら映画
そうじゃなかったらショッピング」
みたいな事を処理してくれるコマンドです

もし1だったらAで
もし2だったらBで
もし3だったらCで
その他ならEで

詳しく

CASEは
ざっくり言うと
ExcelのIF関数を、条件分だけいっぱい並べるイメージです

Excel通の方はお気づきかもですが2016から追加されたIFSと同じ役割です
IFSのSQLバージョンのイメージです

ExcelのIFS関数と見比べてみましょう

IFS( 条件A, 結果A, 条件B, 結果B, 条件C, 結果C )

CASE
WHEN 条件A THEN 結果A
WHEN 条件B THEN 結果B
WHEN 条件C THEN 結果C
ELSD 結果D
END

最初のCASEと最後のENDは、ここからここまでの合図なので置いておいて

IFS 関数の 条件の部分が、CASEではWHEN に変わっいて
条件にマッチするとTHENの後の処理されます
条件があるだけ、WHENをどんどん追加出来ます
IFSを縦に並べただけに見えますよね

(CASEも横一文で
CASE WHEN 条件A THEN 結果A WHEN 条件B THEN 結果B…
と書く事も出来ますが見易さを考慮して縦に並べて書かれる事が多いです)

では最初のテーブルの例を詳しくみてみましょう

お客さんが購入された合計金額を
300円以上、500円以上、1000円以上、300円未満のレンジに
振分けしています

WHEN に続く条件で順にマッチングしていき
どれにもあてはまらない場合はELSEの結果が表示されます
このサンプルではどこのWHENでも金額指定されてない「300円未満」が該当します

この例では単純に
各レコードごとに、金額がどのレンジにあるかを出しているだけで
Excelピボットのような集計はされていません

集計はGROP BY と組み合わせる事で行う事ができますので

もう一歩、進んでみてみましょう

もう一歩

上の例では、金額レンジを振り分けるだけでしたが
ピボット集計する方法も見ていきましょう

やりたい事は、金額のレンジ事に何個の注文が入っていたかを
GROPY BYとCOUNTを使って集計していきます

GROP BYやCOUNTについては別ペ
ージで詳しくシェアしてますので良かったらご覧になってください

SQL GROUP BY

SQL 集計演算子

COUNTは集計をする為の演算子で非常に良く使われます
他には、SUM、AVG、MIAX、MIN、AVGなども合わせて使われます

	SELECT [取引ID], [取引日], [店舗ID], [商品ID], CASE WHEN [合計金額] >= 300 THEN ‘300円以上’ WHEN [合計金額] >= 500 THEN ‘500円以上’- WHEN [合計金額] >= 1000 THEN >1000円以上’ ELSE ‘300円未満’ END レンジ COUNT( [取引ID]) AS ボリューム, SUM( [合計金額]]) AS 合計, MIN( [合計金額]]) AS 最小, MAX( [合計金額]]) AS 最大, AVG( [合計金額]]) AS 平均 FROM sales GROUP BY CASE WHEN [合計金額] >= 300 THEN ‘300円以上’ WHEN [合計金額] >= 500 THEN ‘500円以上’- WHEN [合計金額] >= 1000 THEN >1000円以上’ ELSE ‘300円未満’ END ORDER BY CASE WHEN [合計金額] >= 300 THEN ‘300円以上’ WHEN [合計金額] >= 500 THEN ‘500円以上’- WHEN [合計金額] >= 1000 THEN >1000円以上’ ELSE ‘300円未満’ END
テーブル: sales
	取引ID	取引日	店舗ID	商品ID	個数	合計金額
1	7	4/1/20	3	52	1	250
2	11	4/1/20	3	27	2	700
3	19	4/1/20	3	46	2	500
4	32	4/1/20	3	23	2	500
5	33	4/1/20	3	34	1	245
6	39	4/1/20	3	32	1	300
7	50	4/1/20	3	49	2	600
8	53	4/1/20	3	60	1	375
9	59	4/1/20	3	51	2	600
10	62	4/1/20	3	49	2	600
結果: (スペース都合で5行のみ表示)
	レンジ	ボリューム	合計	最小	最大	平均
1	300円未満	2	795	245	250	248
2	300円以上	2	675	300	375	337.5
3	500円以上	6	3500	500	700	583.3
4
5