BigQueryで横持ちのテーブルを縦持ちに変換してみた - データサイエンス＆サイバーセキュリティ備忘録

はじめに

仕事でデータ集計を行うとき、ほぼBigQueryのクエリを書いて集計しています。

様々な関数を使いこなせるようになりましたが、ときどきPythonだったら楽なのにな～という集計があります。

例えば、横持ちのデータを縦持ちにしたいときです。

DBで横持ちのデータはなかなか無いかもしれませんが、csv形式のファイルでデータが横持ちで入っているということはあるのではないでしょうか？

横持ちのデータは、生データで見る場合には見やすいですが、SQLでの集計やBIツールでの可視化に向いていません。

Pythonでは、melt関数やpivot関数を使えば横持ちから縦持ちに簡単に変換することができますが、今回はSQL（BigQuery）で横持ちのデータを縦持ちに変換する方法を、自分用のメモとして残すと同時に紹介します。

と、その前に、クエリを実行するために使用したデータを簡単に説明します。

使用データ

今回使用したデータは、適当に作成した、とあるスーパーマーケットの部門×州の年間売上データです。

部門と州はSQLで集計しやすいようになっていますが、売上の年が列名となっていて売上金額が入っている状態です。

以下の画像は、使用するデータの一部です。

f:id:a7xche:20201208011938p:plain

今回はこのデータを、左からdepartment、states、year、sales列というように並んだ縦持ちのデータに変換します。

やり方

一発で簡単に横持ちのデータを縦持ちに変換できる関数はないようなので、複数の関数を組み合わせて変換します。

ざっくり流れを説明すると、

　STEP 1.　縦持ちにしたい列をSTRUCT型で1行ずつ格納
　STEP 2.　全てのSTRUCT型を配列で格納
　STEP 3.　CROSS JOINとUNNESTによる配列のフラット化

というやり方です。

以下が実際に使用したクエリで、ここにコメントを付け足して解説をします。

-- 上記のSTEP 1とSTEP2 は、サブクエリ内でまとめる
WITH tmp_table AS(
  SELECT
    department,
    states,
    [
      STRUCT(2016 AS year,   -- 2016をyear列へ
             sales_2016 AS sales),  -- sales_2016のデータをsales列へ
      STRUCT(2017 AS year,   -- 2017をyear列へ
             sales_2017 AS sales),  -- sales_2017のデータをsales列へ
      STRUCT(2018 AS year,   -- 2018をyear列へ
             sales_2018 AS sales),  -- sales_2018のデータをsales列へ
      STRUCT(2019 AS year,   -- 2019をyear列へ
             sales_2019 AS sales),  -- sales_2019のデータをsales列へ
      STRUCT(2020 AS year,   -- 2020をyear列へ
             sales_2020 AS sales)   -- sales_2020のデータをsales列へ
    ] AS agg_data_  -- 配列でSTRUCT型を格納
  FROM
    `snappy-sunset-297913.data.ABC Retail`  -- 今回使用したデータ
)
SELECT
  department,
  states,
  agg_data.year,
  agg_data.sales
FROM
  tmp_table
CROSS JOIN
  UNNEST(tmp_table.agg_data_) AS agg_data  -- STEP 3