BigQueryでPreviewになったPIVOTとUNPIVOTを試す

BigQueryのPIVOTとUNPIVOTの構文をそれぞれ実行し、縦持ち/横持ちを変換する。
2021.05.11

データアナリティクス事業本部、池田です。
2021/05/10のリリースでBigQueryPIVOTUNPIVOTが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 ) と 動的SQLEXECUTE 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だけのレコードが大量にできてしまいました。)

へー。

おわりに

他にもいろいろオプションがあるようなので便利そうなのがあれば試してみようと思います。たぶん。

関連情報/参考にさせていただいたページ