BigQueryでPreviewになったPIVOTとUNPIVOTを試す
BigQueryのPIVOTとUNPIVOTの構文をそれぞれ実行し、縦持ち/横持ちを変換する。
データアナリティクス事業本部、池田です。
2021/05/10のリリースでBigQueryでPIVOTとUNPIVOTがPreview（執筆時点）となりましたので、
動かしてみました。
【 Release notes 】
以前のブログ ではSQLでPIVOTできなかったので、pandasを使っていました。
（2021/07/20追記）
07/19のリリース で
generally available（GA） になりました。
掲載のSQLはそのままで動くことを確認済みです。
PIVOT
PIVOTを使ってみます。
【 PIVOT operator 】 ※執筆時点ではドキュメントは英語のみ
対象は BigQuery の一般公開データセット
から出生データの natality サンプルテーブルです。
まずは普通に年・月・性別でGROUP BYして、新生児の平均体重を見てみます。
（今回はPIVOTの確認なので、双子などは考慮せず、 is_male カラムがTRUEでないものを単純にfemaleとして扱っています。）
SELECT
year, month,
CASE is_male
WHEN TRUE THEN 'male'
ELSE 'female'
END AS sex,
ROUND(AVG(weight_pounds), 2) AS avg_weight_pounds
FROM
`bigquery-public-data.samples.natality`
GROUP BY year, month, sex
ORDER BY year, month, sex;
↓
+------+-------+--------+-------------------+ | year | month | sex | avg_weight_pounds | +------+-------+--------+-------------------+ | 1969 | 1 | female | 7.05 | | 1969 | 1 | male | 7.32 | | 1969 | 2 | female | 7.09 | | 1969 | 2 | male | 7.36 | | 1969 | 3 | female | 7.09 | | 1969 | 3 | male | 7.36 | | 1969 | 4 | female | 7.1 | | 1969 | 4 | male | 7.37 | …
同様の内容で性別でPIVOTしてみます。
SELECT year, month, ROUND(female, 2) AS female, ROUND(male, 2) AS male
FROM (
SELECT
year, month,
CASE is_male
WHEN TRUE THEN 'male'
ELSE 'female'
END AS sex,
weight_pounds
FROM
`bigquery-public-data.samples.natality`
)
PIVOT (
AVG(weight_pounds)
FOR sex IN ('female', 'male')
)
ORDER BY year, month;
↓
+------+-------+--------+------+ | year | month | female | male | +------+-------+--------+------+ | 1969 | 1 | 7.05 | 7.32 | | 1969 | 2 | 7.09 | 7.36 | | 1969 | 3 | 7.09 | 7.36 | | 1969 | 4 | 7.1 | 7.37 | | 1969 | 5 | 7.07 | 7.34 | | 1969 | 6 | 7.04 | 7.31 | | 1969 | 7 | 7.05 | 7.31 | | 1969 | 8 | 7.05 | 7.31 | …
ROUND() （四捨五入）をどこでするのかちょっと迷いましたが、実行できました。
UNPIVOT
UNPIVOTを使ってみます。
【 UNPIVOT operator 】 ※執筆時点ではドキュメントは英語のみ
対象は BigQuery の一般公開データセット
から気象データの gsod サンプルテーブルです。
gsodでは、各気象現象（ fog, rain, snow, hail, thunder, tornado ）が起きたかを、
それぞれのカラムでBooleanとして持っています。
SELECT
station_number, year, month, day,
fog, rain, snow, hail, thunder, tornado
FROM
`bigquery-public-data.samples.gsod`
ORDER BY station_number, year, month, day;
↓
+----------------+------+-------+-----+-------+-------+-------+-------+---------+---------+ | station_number | year | month | day | fog | rain | snow | hail | thunder | tornado | +----------------+------+-------+-----+-------+-------+-------+-------+---------+---------+ | 8209 | 2009 | 3 | 9 | false | false | false | false | false | false | | 8209 | 2009 | 3 | 10 | false | false | false | false | false | false | | 8209 | 2009 | 3 | 11 | false | false | false | false | false | false | | 8209 | 2009 | 3 | 12 | false | false | false | false | false | false | … | 10010 | 1955 | 2 | 1 | false | false | false | false | false | false | | 10010 | 1955 | 2 | 2 | true | true | true | true | true | true | | 10010 | 1955 | 2 | 3 | false | false | false | false | false | false | | 10010 | 1955 | 2 | 4 | false | false | false | false | false | false | | 10010 | 1955 | 2 | 5 | true | true | true | true | true | true | …
（サンプルデータはなぜか全てtrueか全てfalseのパターンかしか無いようです。）
各気象現象をUNPIVOTしてみます。
SELECT *
FROM (
SELECT
station_number, year, month, day,
fog, rain, snow, hail, thunder, tornado
FROM
`bigquery-public-data.samples.gsod`
)
UNPIVOT (
occurred
FOR weather IN (fog, rain, snow, hail, thunder, tornado)
)
ORDER BY station_number, year, month, day;
↓
+----------------+------+-------+-----+----------+---------+ | station_number | year | month | day | occurred | weather | +----------------+------+-------+-----+----------+---------+ | 8209 | 2009 | 3 | 9 | false | thunder | | 8209 | 2009 | 3 | 9 | false | snow | | 8209 | 2009 | 3 | 9 | false | tornado | | 8209 | 2009 | 3 | 9 | false | rain | | 8209 | 2009 | 3 | 9 | false | hail | | 8209 | 2009 | 3 | 9 | false | fog | | 8209 | 2009 | 3 | 10 | false | rain | … | 10010 | 1955 | 2 | 1 | false | tornado | | 10010 | 1955 | 2 | 2 | true | hail | | 10010 | 1955 | 2 | 2 | true | rain | | 10010 | 1955 | 2 | 2 | true | fog | | 10010 | 1955 | 2 | 2 | true | snow | | 10010 | 1955 | 2 | 2 | true | thunder | | 10010 | 1955 | 2 | 2 | true | tornado | | 10010 | 1955 | 2 | 3 | false | rain | …
UNPIVOTの時の FOR … IN () 句のカラム名は「''」でくくらないようです。
PIVOTのカラムを動的に指定する
参考：【 PIVOT in BigQuery 】
PIVOT句
内の IN に指定する値（PIVOT後に作成されるカラム）は定数でないといけないようで、
例えば↓のような書き方はできないようです。
SELECT * FROM (
SELECT period_start, period_slot_ms, job_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
)
PIVOT (
SUM(period_slot_ms)
FOR job_id IN (
SELECT DISTINCT job_id FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT WHERE job_creation_time > '2021-04-09 09:00:00'
)
)
ORDER BY period_start;
（対象のテーブルは ジョブのタイムライン情報 です。）
これは、
変数 （ SET ）
と
動的SQL （ EXECUTE IMMEDIATE ）
で回避できるそうです。
DECLARE jobs STRING;
SET jobs = (
SELECT CONCAT('("', STRING_AGG(DISTINCT job_id, '", "'), '")') FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT WHERE job_creation_time > '2021-04-09 09:00:00'
);
EXECUTE IMMEDIATE FORMAT("""
SELECT * FROM (
SELECT period_start, period_slot_ms, job_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE job_id IN %s
)
PIVOT (
SUM(period_slot_ms)
FOR job_id IN %s
)
ORDER BY period_start
""", jobs, jobs);
↓
+---------------------+---------------+---------------+---------------+---------------+ | period_start | bqjob_foo1111 | bqjob_foo2222 | bqjob_foo3333 | bqjob_foo4444 | +---------------------+---------------+---------------+---------------+---------------+ | 2021-04-09 09:34:35 | 0 | 6107 | 0 | 7082 | | 2021-04-09 09:34:36 | 0 | 118037 | 0 | 128499 | | 2021-04-09 09:34:37 | 0 | 291121 | 0 | 298169 | | 2021-04-09 09:34:38 | 0 | 542200 | 0 | 537783 | | 2021-04-09 09:34:39 | 0 | 610966 | 0 | 609666 | | 2021-04-09 09:34:40 | 0 | 336490 | 0 | 311824 | | 2021-04-09 09:34:41 | 0 | 79769 | 0 | 77874 | | 2021-04-09 09:34:42 | 0 | 332044 | 0 | 315014 | | 2021-04-09 09:34:43 | 0 | 559797 | 0 | 767923 | | 2021-04-09 09:34:44 | 0 | 318128 | 0 | 184984 | | 2021-04-09 09:34:45 | 0 | 573927 | 0 | 354814 | …
（ちなみに、SQL15行目のFOR…IN句があるので、11行目のWHERE句は要らないような気がしたのですが、
これが無いとテーブル内全ての period_start に対してピボットテーブルを作るので、
job_id群がNULLだけのレコードが大量にできてしまいました。）
へー。
おわりに
他にもいろいろオプションがあるようなので便利そうなのがあれば試してみようと思います。たぶん。
