【データ基盤構築/BigQuery】UNPIVOT演算子を使って、横持ちデータを縦持ちに変換する

今回の課題

横持ちのデータを縦持ちのデータに変換したい。
横持ちのデータは生データで見る分には見やすいが、
SQLでの集計や BIツールでの可視化には向いていない。

今回はUNPIVOT演算子を使用して、データの変換を行った。

UNPIVOT演算子とは

列を行に変換する演算子。(横持ちのテーブルを縦持ちに変える。）
FROM句の一部として考える。

文法

select
    *
from
    `テーブル名`
    unpivot
    [値の出力列名][, ...]
    for [項目の出力列名]
    in ([出力項目][, ...])
    )

• 項目の出力列名・・・UNPIVOTされる前の列名が入るカラム名。
• 値の出力列名　・・・UNPIVOTされる前の列の値が入るカラム名。
• 出力項目　　　・・・項目の出力列名内に入る項目の一覧。ASで列名をつけることも可能。
  ※参考：【BigQuery】PIVOT/UNPIVOT演算子を試してみた

使用例

成績表のテーブルがあったとする。

name	english	math	japanese
yamada	5	2	3
satou	1	1	4
suzuki	2	1	5

上記のテーブルに対して、下記のクエリを実行すると、

select
    name
    , subject -- 科目
    , record  -- 成績
from
    `テーブル名`
unpivot (
    record
    for subject
    in (english as '英語', math as '数学', japanese as '国語')
    )

このように、縦持ちのデータに変換することができる。

name	subject	record
yamada	英語	5
yamada	数学	2
yamada	国語	3
satou	英語	1
satou	数学	1
satou	国語	4
suzuki	英語	2
suzuki	数学	1
suzuki	国語	5

以上です。

おまけ

縦持ちのデータを横持のデータに変換する場合はどのようにすればいいか、
念のためこちらにメモを残す。
横持ちデータは、目視で見やすいところが長所。

横持データに変換する方法

• PIVOT演算子を使用する。(BigQueryでPreviewになったPIVOTとUNPIVOTを試す)
• CASE式を使用する。(SQLのCASEを使ったテーブルの縦横変換)