OracleでPIVOTを使う

最近会社で聞かれて答えたことをまとめてみました。

Oracleに格納された情報を抽出して、あるツールに流し込みたいという状況でした。元データの格納形式は、いわゆる行持ちデータ。受け取り側のツールが要求する形式は、いわゆる列持ちデータ。元データの形式と、受け取り側のツールが要求する形式が合っていない。SQLを工夫するだけで対応できないか、というものでした。

ちょうどPIVOTでできそうだということを説明して、説明したことをまとめてみました。

会社の題材をそのまま使うわけにはいきません。少し題材を変えていくつか例示しながら説明します。

例によって、確認環境は少し前に作ったOracle 11g XE on Linuxです。

PIVOTの使用例:単項目

元データは以下のようなものです。

品物	色	年月	月別合計金額
AAA	○	2020/01	110
AAA	○	2020/02	220
AAA	○	2020/03	330
AAA	●	2020/01	440
AAA	●	2020/03	660
BBBB	○	2020/01	1100
BBBB	○	2020/02	2200
BBBB	○	2020/03	3300

これを以下のように抽出するケースを考えます。

品物	色	202001	202002	202003
AAA	○	110	220	330
AAA	●	440		660
BBBB	○	1100	2200	3300

"品物"＆"色"単位でグルーピングして1行に集約、行方向に伸びていた"年月"を列方向に伸ばして、交差する場所に月別合計金額を抽出します。以下の変換図を見ると理解しやすいのではないでしょうか？行持ちデータ、列持ちデータと呼ばれる所以が分かると思います。

これを実現するSQLは、以下のとおりです。

WITH
DETAIL AS (
				SELECT ' ' AS ITEM, ' ' AS COLOR, ' ' AS YM, 0 AS AMOUNT FROM DUAL WHERE 0 = 1
	UNION ALL	SELECT 'AAA',  '○', '2020/01',  110 FROM DUAL
	UNION ALL	SELECT 'AAA',  '○', '2020/02',  220 FROM DUAL
	UNION ALL	SELECT 'AAA',  '○', '2020/03',  330 FROM DUAL
	UNION ALL	SELECT 'AAA',  '●', '2020/01',  440 FROM DUAL
	UNION ALL	SELECT 'AAA',  '●', '2020/03',  660 FROM DUAL
	UNION ALL	SELECT 'BBBB', '○', '2020/01', 1100 FROM DUAL
	UNION ALL	SELECT 'BBBB', '○', '2020/02', 2200 FROM DUAL
	UNION ALL	SELECT 'BBBB', '○', '2020/03', 3300 FROM DUAL
)

SELECT	*
FROM	DETAIL
PIVOT (
	SUM(AMOUNT)
	FOR YM IN (
			'2020/01'	AS	"202001"
		,	'2020/02'	AS	"202002"
		,	'2020/03'	AS	"202003"
	)
)
ORDER BY	ITEM
		,	COLOR

解説

分かりにくいのは、以下の2点でしょう。

• 集約単位：暗黙の"GROUP BY"
• 集約関数：AMOUNTに集約関数を施す必要がある

集約単位：暗黙の"GROUP BY"

"品物"＆"色"単位でグルーピングして1行に集約、行方向に伸びていた"年月"を列方向に伸ばして、交差する場所に"月別合計金額"を抽出するというものでした。

横方向に伸ばす"年月"は、PIVOT内、"FOR"の後に指定した項目です。

交差したエリアに出力する"月別合計金額"は、PIVOT内、"FOR"の前に指定した集約関数内で使用している項目です。

"品物"と"色"は、PIVOTの中で指定されていません。そして、指定されていない"品物"＆"色"単位で暗黙的に集約されます。

その結果、例えば"品物='AAA' AND 色='○'のレコードは、3行から1行に集約される動きとなっています。

集約関数：AMOUNTに集約関数を施す必要がある

AMOUNTには、集約関数SUMが施されています。上のSQLを見る限り、このSUMは一見不要なように見えます。

以下のSQLを考えてみましょう。これは、上に書いたSQLのうち、"品物='AAA' AND 色='○'のレコードを抜き出し、レコードを増やしたものです。

WITH
DETAIL AS (
				SELECT ' ' AS ITEM, ' ' AS COLOR, ' ' AS YM, 0 AS AMOUNT FROM DUAL WHERE 0 = 1
	UNION ALL	SELECT 'AAA',  '○', '2020/01',  110 FROM DUAL
	UNION ALL	SELECT 'AAA',  '○', '2020/02',  220 FROM DUAL
	UNION ALL	SELECT 'AAA',  '○', '2020/03',  330 FROM DUAL
	-- 以下、追加部分
	UNION ALL	SELECT 'AAA',  '○', '2020/01', 10000 FROM DUAL
	UNION ALL	SELECT 'AAA',  '○', '2020/02', 20000 FROM DUAL
	UNION ALL	SELECT 'AAA',  '○', '2020/03', 30000 FROM DUAL
	-- 追加部分はここまで
)

SELECT	*
FROM	DETAIL
PIVOT (
	SUM(AMOUNT)
	FOR YM IN (
			'2020/01'	AS	"202001"
		,	'2020/02'	AS	"202002"
		,	'2020/03'	AS	"202003"
	)
)
ORDER BY	ITEM
		,	COLOR

抽出結果は以下のようになります。

品物	色	202001	202002	202003
AAA	○	10110	20220	30330

これを見ると、SUMが必要な理由がはっきりするのではないでしょうか。

一つ目のSQLでは、"月別合計"といったように、既に"月単位でのGROUP BYが施されているデータ"を対象にPIVOTを掛けていました。しかし、そのような2段階の集約をする必要はなかったわけです。明細データをPIVOTに直接渡せば、PIVOTが"集約"したうえで"行持ちデータを列持ちデータに変換"してくれるわけです。だからSUMが必要、というわけです。

例えばあるサイトでは"平均がテーブルに入ってる時点で恣意的な例"と書いてあったりします。これなんかも、明細データを対象に"PIVOT(AVG(value) FOR kamoku_key)"といったようにすれば、"既に平均値を求めてあるデータ"を対象にするのと同じ結果が得られます。

とはいえ、PIVOTは"行持ちデータを列持ちデータに変換する"ためだけに使う、集約はPIVOTに渡す前に明示的に"GROUP BY"する、とするほうが分かりやすいようにも思います。

それというのも、暗黙の"GROUP BY"の制約があるからです。明細取得時に余分な項目を含めてしまうと、その項目も暗黙の"GROUP BY"の項目となってしまいます。そのため、明細の抽出列を事前に絞っておく必要があります。そして、列を絞るなら、その段階で集約もしておくほうが分かりやすいだろうということになります。

こういった背景を理解せずに、"なんかよく分からんけど適当な集約関数、例えばMINとかつけておけばいい"というようなことをしないように心がけたいところです。

PIVOTの使用例:複数項目

PIVOTでは、行列変換する値の部分を、複数の項目にすることができます。前項では"月別合計金額"だけを出力していました。ここに"月別件数"を追加することにします。

元データは以下のようなものです。なお、前項に"事前に集計しておく必要はない"と書いています。しかし、ここでは理解しやすくするために事前に集計した想定で書いています。

品物	色	年月	月別合計金額	月別件数
AAA	○	2020/01	110	10
AAA	○	2020/02	220	20
AAA	○	2020/03	330	30
AAA	●	2020/01	440	40
AAA	●	2020/03	660	60
BBBB	○	2020/01	1100	100
BBBB	○	2020/02	2200	200
BBBB	○	2020/03	3300	300

これを以下のように抽出するケースを考えます。

品物	色	01_AMOUNT	01_CNT	02_AMOUNT	02_CNT	03_AMOUNT	03_CNT
AAA	○	110	10	220	20	330	30
AAA	●	440	40			660	60
BBBB	○	1100	100	2200	200	3300	300

前節では、"品物"＆"色"＆"年月"単位で出力する項目が"月別合計金額"だけでした。今回は"月別合計金額"と"月別件数"を併記しています。項目数が増えるため、列名を少し短くしています。変換イメージは以下のようになります。

これを実現するSQLは、以下のとおりです。

WITH
DETAIL AS (
				SELECT ' ' AS ITEM, ' ' AS COLOR, ' ' AS YM, 0 AS AMOUNT, 0 AS CNT FROM DUAL WHERE 0 = 1
	UNION ALL	SELECT 'AAA',  '○', '2020/01',  110,  10 FROM DUAL
	UNION ALL	SELECT 'AAA',  '○', '2020/02',  220,  20 FROM DUAL
	UNION ALL	SELECT 'AAA',  '○', '2020/03',  330,  30 FROM DUAL
	UNION ALL	SELECT 'AAA',  '●', '2020/01',  440,  40 FROM DUAL
	UNION ALL	SELECT 'AAA',  '●', '2020/03',  660,  60 FROM DUAL
	UNION ALL	SELECT 'BBBB', '○', '2020/01', 1100, 100 FROM DUAL
	UNION ALL	SELECT 'BBBB', '○', '2020/02', 2200, 200 FROM DUAL
	UNION ALL	SELECT 'BBBB', '○', '2020/03', 3300, 300 FROM DUAL
)

SELECT	SUMMARY.*
FROM	DETAIL
PIVOT (
			SUM(AMOUNT)		AS	AMOUNT
		,	SUM(CNT)		AS	CNT
		FOR YM IN (
				'2020/01'	AS	"01"
			,	'2020/02'	AS	"02"
			,	'2020/03'	AS	"03"
		)
) SUMMARY
ORDER BY	SUMMARY.ITEM
		,	SUMMARY.COLOR

解説

出力する項目を増やしたことに加えて、前節から以下の要素を追加しています。

• 出力値に別名付与
• PIVOT()に別名付与

出力値に別名付与

前節では"SUM(AMOUNT)"に別名を付けませんでした。これは、別名を付けなくても結果が変わらないからです。

実は、列名は以下を連結した文字列で決まります。

1. "FOR ～ IN ()"の"IN"内で指定された値、但し別名指定がある場合はその別名
2. 次項3がある場合は、前項1と次項3の間を"_"で連結
3. "FOR ～ IN ()"の"～"に指定された式に対する別名、但し別名指定がない場合は値なし

ここまで1は必ず指定していました。指定しない場合は"'2020/01'"といった列名になっていたはずです。

そして3は、前節では別名指定がありませんでした。そのため、2と3は値なしとなります。1だけに基づいて列名が付けられていました。

今回は、同じ年月内に出力する項目が複数あります。3を指定しないと、これらの項目は同じ列名となってしまうためエラーとなります(ORA-00918)。そのため、別名を付ける必要があります。

上述のルールに基づいて、"01_AMOUNT"や"01_CNT"といった列名になります。

PIVOT()に別名付与

公式サイトに解説のあるとおり、PIVOTはFROM句の一部として評価されます。そのため、テーブルに別名を付けることができるのと同様、"PIVOT()"にも別名を付けることができます。

今まで"SELECT DETAIL.* FROM DETAIL PIVOT(～) ORDER BY DETAIL.KEY"のように指定するとORA-00904(無効な識別子)となっていたのが謎でした。このエラーの理由は、ここの"SELECT"句や"ORDER BY"句に指定すべき項目は、"DETAILの列"ではなく"PIVOT()の列"だからです。

そして、"PIVOT()"に別名を付ければ、当然のようにその別名で列を指定することができます。

まとめ

過去に関わったプログラムで、"PIVOTが使えればPIVOTでできたよな"と思い出したものがいくつかありました。

そのような処理の代表は年月集計系の処理です。横方向に"1月"～"12月"や"4月"～"3月"が並ぶような出力結果のものです。"PIVOT()"の"FOR ～ IN()"のIN部分には、値を固定的に列記する必要があります。そのため、値のバリエーションを固定的に扱えるかどうかで、PIVOTの使いやすさが変わります。

月"1月"～"12月"や"4月"～"3月"であれば、値のバリエーションが変動する可能性はまず考えられません。まさにPIVOTが活躍できる場所です。