SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
東南アジアをバックパッカーしながら転職活動をした記録
東南アジアをバックパッカーしながら転職活動をした記録
7年目で初の転職活動 2カ月で内定獲得した記録
7年目で初の転職活動 2カ月で内定獲得した記録
おすすめのプログラミングスクール めざせ就職・年収アップ!
おすすめのプログラミングスクール めざせ就職・年収アップ!
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
ブログ開始3年が経った月収が1万超えてた!
ブログ開始3年が経った月収が1万超えてた!
プログラミング独学・スクール・就職(目次)
プログラミング独学・スクール・就職(目次)
previous arrowprevious arrow
next arrownext arrow
 
Shadow
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はこうなります。

SELECT
 ITEM_CODE			AS '商品コード'
,SUM(STOCK_AMOUNT)	AS '在庫_商品コード単位'
FROM [在庫]
GROUP BY ITEM_CODE;

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

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

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

例題2

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

これも簡単です。

SQLはこうなります。

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こうなります。

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

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に落とし込むことは簡単かもしれません。

SQL ヘッダー行と明細(データ)行で項目数が異なるデータを抽出する IFファイルを作るときに、ヘッダー行、明細行、明細行...、ヘッダー行、明細行、明細行...のような作りのことがあります。 ...
ABOUT ME
LooseCarrot
LooseCarrot
ブログ運営をしているLooseCarrotです。 興味のあることにトライして発信していきます! プロフィール

関連している記事