【BigQuery】PIVOT/UNPIVOT演算子を試してみた

f:id:true-fly:20210728222544p:plain

2021年7月19日のリリースでGAとなった、PIVOT/UNPIVOT演算子を試しました！
cloud.google.com Excelやスプレッドシートで馴染み深いピボットですが、BigQueryには従来はこの機能はなかったので、ピボットテーブルを作るときは、クエリの結果をPythonのpandasで読み込み、Pandasでピボットしていたあのときの手間がなくなり、SQLのみで完結するようになりました。今までもCASEやGROUP BYを絡めた長いSQLを書けばできないことはなかったのですが、この演算子の登場により、もっとシンプルな行列変換ができそうです。
BigQueryでの集計業務の効率アップも間違いないので、早速このPIVOT演算子、試してみようと思います！

今回使うデータセット、テーブル
PIVOT演算子
UNPIVOT演算子
まとめ

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析できるDigital Camp

作者:木田和廣
インプレス

Amazon

今回使うデータセット、テーブル

本記事に記載のSQLで参照しているデータセット、テーブルは、私の個人プロジェクトにある以下のものを利用しています。

データセット：truefly
テーブル：truefly.census

f:id:true-fly:20210723174126p:plain — ※「令和２年国勢調査　人口速報集計」（総務省統計局）を加工して作成しています。

PIVOT演算子

PIVOTとは、行を列に変換する演算子です。
実際のデータを用いたSQLで、どのようにPIVOT演算子を使うのか見ていきましょう。

PIVOT前のデータ

まず、国勢調査のデータで、都道府県、市区町村の人口を取得してみます。SQL、結果はこちら。

SELECT
    prefecture_code,
    prefecture,
    city,
    population,
FROM
    truefly.census
WHERE
    prefecture != city
    AND city != '特別区部'       
ORDER BY
    population DESC

結果

f:id:true-fly:20210723230407p:plain

PIVOT演算子を使ってみる

この都道府県、市区町村の人口データを、「市、区、町、村」で4つに分け、これを列としたピボットテーブルを作成してみます。
PIVOT演算子を用いて実現したSQLがこちらです。

WITH census AS (
    SELECT
        prefecture_code,
        prefecture,
        RIGHT(city, 1) AS city_class,  -- 市,区,町,村に分類
        population,
    FROM
        truefly.census
    WHERE
        prefecture != city
        AND city != '特別区部'        
)
SELECT
    *
FROM
    census
    PIVOT(
        COUNT(*) AS count, -- 件数
        SUM(population) AS total_population -- 人口
        FOR city_class
        IN ('市' AS shi, '区' AS ku, '町' AS cho, '村' AS son)
    )

結果、「市、区、町、村」ごとにそれぞれ件数、人口を計算し、列にしたデータができました。

f:id:true-fly:20210723225040p:plain

PIVOT演算子の文法

文法は以下のとおりです。先程検証したSQLベースでの文法解説になります。PIVOTはFROM句の中に書きます。
[集計関数]の箇所に、SUM()やCOUNT()などの集計関数を複数書けます。ここが値となる項目です。
[入力項目]の箇所は、列を作る際の元となる項目、[出力項目]の箇所は実際にクエリ結果の列として出力する項目になります。

FROM
    [テーブル]
    PIVOT(
        [集計関数] [, ...]
        FOR [入力項目]
        IN ([出力項目] [, ...])
    )

その他詳細は公式ドキュメントを御覧ください。

cloud.google.com

UNPIVOT演算子

こちらはPIVOT演算子とは逆で、列を行に変換する演算子になります。

UNPIVOT前のデータ

以下のSQLを実行して、市区町村毎の人口、男性人口、女性人口を取得します。

SELECT
    prefecture_code,
    prefecture,
    city,
    population,
    male_population,
    female_population,
FROM
    truefly.census
WHERE
    prefecture != city
    AND city != '特別区部'       
ORDER BY
    population DESC

結果

f:id:true-fly:20210724003541p:plain

UNPIVOT演算子を使ってみる

先程のデータ、列に人口、男性人口、女性人口の項目が並んでいるデータ、これをUNPIVOTで行に変換してみます。

WITH census AS (
    SELECT
        prefecture_code,
        prefecture,
        city,
        population,
        male_population,
        female_population,
    FROM
        truefly.census
    WHERE
        prefecture != city
        AND city != '特別区部'       
    ORDER BY
        population DESC
)
SELECT
    prefecture_code,
    prefecture,
    city,
    poputation_type,
    value,
FROM
    census
    UNPIVOT(
        value
        FOR poputation_type
        IN (population AS "総人口", male_population AS "男性人口", female_population AS "女性人口")
    )

結果

f:id:true-fly:20210724004207p:plain

poputation_type,valueという列にそれぞれ項目名、値を入れることができました！！すごい！！！

UNPIVOT演算子の文法

文法は以下のとおりです。先程検証したSQLベースでの文法解説になります。UNPIVOTもFROM句の中に書きます。
[項目の出力列名]の箇所に、UNPIVOTされる前の列名が入るカラム名を書きます。
[値の出力列名]の箇所に、UNPIVOTされる前の列の値が入るカラム名を書きます。 [出力項目]の箇所は、 [項目の出力列名]内に入る項目の一覧を書きます。ASで別名をつけることもできます。

FROM
    [テーブル]
    UNPIVOT(
        [項目の出力列名] [, ...]
        FOR [値の出力列名]
        IN ([出力項目] [, ...])
    )

その他詳細は公式ドキュメントを御覧ください。

cloud.google.com

まとめ

以上、PIVOT/UNPIVOT演算子を試してみた結果でした。
今まではSQLで取得した結果を、スプレッドシートやPythonで行列変換してデータ作成をしてきましたが、またSQLで完結できることも増えました。
今回は比較的シンプルなサンプルデータで検証しましたが、もっと複雑な集計業務でも実用してみて、さらなる活用方法を見出したいと思いました！