previous arrowprevious arrow
next arrownext arrow
Shadow
Slider
SQL

SQL 列単位で複数の集計をする関数 PARTITION BY

最近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

商品コード単位に在庫を抽出するには?

例題1

在庫テーブルから商品コード単位に在庫合計を抽出するには?

簡単です。

SQLはこうなります。

抽出結果はこうなります。

商品コード 在庫_商品コード単位
A10000 117
B10001 183
C10005 109

商品コード×色単位に在庫を抽出するには?

例題2

在庫テーブルから商品コード×色に在庫合計を抽出するには?

これも簡単です。

SQLはこうなります。

抽出結果はこうなります。

商品コード 在庫_色・サイズ単位
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こうなります。

簡単に解説

簡単に解説します。

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はこんな感じになります。

簡単に解説

SUM()の中でCASE文を使って該当すれば1として、

それをどんどん1ずつ増やしていきます。

CASEの中は在庫数が0または、NULLなら1をカウントするようになっています。

以上です(笑)。

 

テラテイルを見ていると、「case式が猛威を振るいます。」と書いてありました。

本当に猛威を振るいますね。

最後に

PARTITION BYが使いこなせれば、エクセルのMINIFSやMAXIFSなどいろんなエクセル関数が実現できますね。

エクセル関数でできるあの関数をSQLでしたいと思ったらぜひ皆さんもチャレンジしてみて下さい。

以外とエクセル関数でできれば、SQLに落とし込むことは簡単かもしれません。

SQL ヘッダー行と明細(データ)行で項目数が異なるデータを抽出する IFファイルを作るときに、ヘッダー行、明細行、明細行...、ヘッダー行、明細行、明細行...のような作りのことがあります。 ...
関連している記事

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です