最近SQLで知ったのですが、集計を列単位など、複数の単位で行えます。
感動したので、備忘録として残しておきます。
OracleでもSQLServerでも使えます。
サンプルテーブル
例えばこんな在庫テーブルがあるとします。
| ITEM_CODE (商品コード) |
COLOR (色) |
SIZE (サイズ) |
STOCK_AMOUNT (在庫) |
| A10000 | 青 | S | 35 |
| A10000 | 青 | M | 61 |
| A10000 | 赤 | S | NULL |
| A10000 | ホワイト | S | 21 |
| A10000 | ブラック | L | 0 |
| B10001 | 青 | S | 78 |
| B10001 | 赤 | S | NULL |
| B10001 | 赤 | M | 0 |
| B10001 | ホワイト | S | 51 |
| B10001 | ホワイト | L | 54 |
| C10005 | 赤 | S | 22 |
| C10005 | ホワイト | S | 0 |
| C10005 | ブラック | L | 87 |
商品コード単位に在庫を抽出するには？
例題１
在庫テーブルから商品コード単位に在庫合計を抽出するには？
簡単です。
SQLはこうなります。
|
1 2 3 4 5 |
SELECT ITEM_CODE AS '商品コード' ,SUM(STOCK_AMOUNT) AS '在庫_商品コード単位' FROM [在庫] GROUP BY ITEM_CODE; |
抽出結果はこうなります。
| 商品コード | 在庫_商品コード単位 |
| A10000 | 117 |
| B10001 | 183 |
| C10005 | 109 |
商品コード×色単位に在庫を抽出するには？
例題２
在庫テーブルから商品コード×色に在庫合計を抽出するには？
これも簡単です。
SQLはこうなります。
|
1 2 3 4 5 6 |
SELECT ITEM_CODE AS '商品コード' ,COLOR AS '色' ,SUM(STOCK_AMOUNT) AS '在庫_色単位' FROM [在庫] GROUP BY ITEM_CODE,COLOR; |
抽出結果はこうなります。
| 商品コード | 色 | 在庫_色・サイズ単位 |
| A10000 | ブラック | 0 |
| C10005 | ブラック | 87 |
| A10000 | ホワイト | 21 |
| B10001 | ホワイト | 105 |
| C10005 | ホワイト | 0 |
| A10000 | 青 | 96 |
| B10001 | 青 | 78 |
| A10000 | 赤 | NULL |
| B10001 | 赤 | 0 |
| C10005 | 赤 | 22 |
「商品コード単位」と「商品コード×色単位」の在庫を1つの表に抽出するには？
例題3
「商品コード単位」の在庫と「商品コード×色単位」の在庫を1つの表に抽出するには？
こんな抽出結果が欲しいときです。
抽出結果イメージ↓↓↓
| 商品コード | 色 | 在庫_色単位 | 在庫_商品コード単位 |
| A10000 | ブラック | 0 | 117 |
| A10000 | ホワイト | 21 | 117 |
| A10000 | 青 | 96 | 117 |
| A10000 | 赤 | NULL | 117 |
| B10001 | ホワイト | 105 | 183 |
| B10001 | 青 | 78 | 183 |
| B10001 | 赤 | 0 | 183 |
| C10005 | ブラック | 87 | 109 |
| C10005 | ホワイト | 0 | 109 |
| C10005 | 赤 | 22 | 109 |
普通にGROUP BYを使うと無理です。
列単位で複数の集計をする関数 PARTITION BY
列ごとにGROUP BYができればOKですが、普通は1つの抽出結果に対してGROUP BYで集計しますよね。
列単位で集計できるのがPARTITION BYです。
例題3を抽出するSQLこうなります。
|
1 2 3 4 5 6 7 |
SELECT DISTINCT ITEM_CODE AS '商品コード' ,COLOR AS '色' ,SUM(STOCK_AMOUNT) OVER(PARTITION BY ITEM_CODE,COLOR) AS '在庫_色単位' ,SUM(STOCK_AMOUNT) OVER(PARTITION BY ITEM_CODE) AS '在庫_商品コード単位' FROM [在庫]; |
簡単に解説
簡単に解説します。
OVER以降の()の中の書き方がGROUP BYではなく、PARTITION BYに変わります。
OVER(PARTITION BY ITEM_CODE,COLOR)
重複が出てしまうので、DISTINCTで重複削除をしています。
以上です。
応用 エクセルのCOUNTIFSをSQLで(CASEとSUMを合せる)
ちょっとやってみたいなと思ったことがあります。
エクセルでCOUNTIFSって関数がありますよね。
これってSQLでできないかな？と思って調べてみました。
すると載っていました。
参考URL：https://teratail.com/questions/264469
ということで、例題4行ってみよう。
例題4
以下を抽出するには？
・商品コード単位で在庫が0またはNULLになっている件数
・色単位で在庫が0またはNULLになっている件数
抽出結果↓↓↓
| 商品コード | 色 | 在庫0SKUの数 商品コード単位 |
在庫0SKUの数 色単位 |
| A10000 | ブラック | 2 | 1 |
| A10000 | ホワイト | 2 | 0 |
| A10000 | 青 | 2 | 0 |
| A10000 | 青 | 2 | 0 |
| A10000 | 赤 | 2 | 1 |
| B10001 | ホワイト | 2 | 0 |
| B10001 | ホワイト | 2 | 0 |
| B10001 | 青 | 2 | 0 |
| B10001 | 赤 | 2 | 2 |
| B10001 | 赤 | 2 | 2 |
| C10005 | ブラック | 1 | 0 |
| C10005 | ホワイト | 1 | 1 |
| C10005 | 赤 | 1 | 0 |
SQLはこんな感じになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT ITEM_CODE AS '商品コード' ,COLOR AS '色' ,SUM(CASE WHEN STOCK_AMOUNT = 0 THEN 1 WHEN STOCK_AMOUNT IS NULL THEN 1 ELSE 0 END ) OVER(PARTITION BY ITEM_CODE) AS '在庫0SKUの数_商品コード単位' ,SUM(CASE WHEN STOCK_AMOUNT = 0 THEN 1 WHEN STOCK_AMOUNT IS NULL THEN 1 ELSE 0 END ) OVER(PARTITION BY ITEM_CODE,COLOR)AS '在庫0SKUの数_色単位' FROM [在庫] |
簡単に解説
SUM()の中でCASE文を使って該当すれば1として、
それをどんどん1ずつ増やしていきます。
CASEの中は在庫数が0または、NULLなら1をカウントするようになっています。
以上です(笑)。
テラテイルを見ていると、「case式が猛威を振るいます。」と書いてありました。
本当に猛威を振るいますね。
最後に
PARTITION BYが使いこなせれば、エクセルのMINIFSやMAXIFSなどいろんなエクセル関数が実現できますね。
エクセル関数でできるあの関数をSQLでしたいと思ったらぜひ皆さんもチャレンジしてみて下さい。
以外とエクセル関数でできれば、SQLに落とし込むことは簡単かもしれません。
