みなさん、こんにちは！
Accessのクロス集計表をBigQueryで実現する要件があり、どうやって実現するか困っていたところ、BigQueryにはPIVOT演算子があることが分かり、無事解決できたのでご紹介します。
PIVOT項目を静的に指定する方法と、動的に指定する方法の２通りについて記述しています。
縦持ちの「店別売上データ」から、"静的"に横持ちの「店別売上データ_PIVOT」に変換
店CD、店名毎に、期をPIVOT項目として売上げを集計する。
PIVOT演算子の文法
qiita.rb
FROM
[テーブル or サブクエリ]
PIVOT(
[集約関数] [, ...]
FOR [入力項目]
IN ([PIVOT項目] [, ...])
)
[テーブル or サブクエリ]：PIVOT前のテーブル名、または、サブクエリを記載
[集約関数]：SUMやCOUNT等の集約関数を記載
[入力項目]：列を作る際の元となる項目を記載
[PIVOT項目]：列として出力する項目を記載
SQL
実際にSQLを作成し実行してみます。
今回は総計も出力したいので、サブクエリを使用しました。
qiita.rb
CREATE OR REPLACE TABLE `店別売上データ_PIVOT` AS
SELECT
*
FROM
(
SELECT
`店CD`,
`店名`,
SUM(`売上`)OVER(PARTITION BY `店CD`,`店名`) AS `総計`,
`期`,
`売上`
FROM `店別売上データ`
)
PIVOT(
SUM(`売上`) FOR `期` IN (
'Q1','Q2','Q3','Q4'
)
)
ORDER BY `店CD`
;
実行結果
想定通りの結果が得られました。
縦持ちの「店別売上データ」から、"動的"に横持ちの「店別売上データ_PIVOT」に変換
先ほどの例はPIVOT項目が期だったので、Q1からQ4と静的に指定を行えましたが、日付等の可変の項目をPIVOT項目とし、PIVOT変換する方法をご紹介します。
PIVOT項目を可変とするため、データから日付情報を取得し、「EXECUTE IMMEDIATE FORMAT」を利用して実現します。
SQL
実際にSQLを作成し実行してみます。
qiita.rb
---配列定義
DECLARE daily_labels ARRAY<STRING>;
--日付を配列に設定
SET daily_labels = (
SELECT ARRAY_AGG(i.`日付`)
FROM (SELECT DISTINCT `日付` FROM `店別売上データ` ORDER BY 1) AS i
);
--配列をUNSET展開しながらIN句に設定する文字列を生成する
EXECUTE IMMEDIATE FORMAT("""
CREATE OR REPLACE TABLE `店別売上データ_PIVOT` AS
SELECT
*
FROM
(
SELECT
`店CD`,
`店名`,
SUM(`売上`)OVER(PARTITION BY `店CD`,`店名`) AS `総計`,
`日付`,
`売上`
FROM `店別売上データ`
)
PIVOT(
SUM(`売上`) FOR `日付` IN %s) ORDER BY `店CD`;
""", (SELECT CONCAT("(",STRING_AGG(CONCAT("'",dailys,"'"),","),")") FROM UNNEST(daily_labels) AS dailys))
実行結果
想定通りの結果が得られました。
まとめ
今回の要件は、動的に横持ちのテーブルに変換する必要があったため、「PIVOT演算子」と「EXECUTE IMMEDIATE FORMAT」を使用して、無事解決することが出来ました。
また、「PIVOT演算子」と逆で、横持ちを縦持ちに変換する「UNPIVOT演算子」というのもあるらしいので、試してみたいと思います。