これをマスター
SQLCASEでピボット分析をマスターしましょう!マスター 売上をピボットしよう
テーブル: 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で
詳しく
WHEN 条件B THEN 結果B
WHEN 条件C THEN 結果C
WHEN 条件D THEN 結果D
ELSE 結果E
END
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については別ペ
ージで詳しくシェアしてますので良かったらご覧になってください
COUNTは集計をする為の演算子で非常に良く使われます
他には、SUM、AVG、MIAX、MIN、AVGなども合わせて使われます
テーブル: 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 |