Bigqueryで不特定のカラムに対して動的にunpivotするSQL

BigQueryでunpivotの際にカラム名指定するの面倒なんだけど…

データの分析や処理に便利なBigQuery(BQ)。

大規模データの処理に優れており、巨大なデータに対しても短時間でSQLを実行することができる。

データを扱っていると、横方向に長い無数のカラム数を持つデータを、カラム名をそれぞれ一つのキー列に集約して縦長のデータにしたいことがある。

しかし UNPIVOT は横持ちから縦持ちにしたいカラム名を固定で指定する必要がある。

これは面倒だ。

カラムの数も固定とは限らないので動的に指定したい。

うまくunpivotするSQLは無いんでしょうかー

そこで今回はBigqueryで不特定のカラムに対して動的にunpivotするSQLについて紹介する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

BigqueryのUNPIVOT

BigqueryのUNPIVOTは以下のように使用する。

まずデータの例としては横方向にQ1~Q4のカラムを持ったデータを用意。

WITH Produce AS (
  SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
  SELECT 'Apple', 77, 0, 25, 2)
SELECT * FROM Produce

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale    | 51 | 23 | 45 | 3  |
| Apple   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

これをQ1~Q4を縦方向に持ちたい場合は UNPIVOT 演算子に quarter IN (Q1, Q2, Q3, Q4) と指定して縦持ちにする。

SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))

+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

ただしこれでは IN の中が多数のカラムを指定したいときにカラムをいちいち指定するのが面倒になる。

簡単に集計したい

Bigqueryで不特定のカラムに対して動的にunpivotするSQL

Bigqueryで不特定のカラムに対して動的にunpivotするには、関数を使用する。

使うのは fhoffa.x.unpivot()。

使い方は以下の通り。

SELECT product, unpivotted
FROM Produce a 
  , UNNEST(fhoffa.x.unpivot(a, 'Q')) 
unpivotted

結果はこのようになる。

fhoffa.x.unpivot(a, 'Q') と記載した第二引数の部分が縦持ち変換する対象のカラムを指定する場所となっている。

この場合はカラム名に Q を含むカラムを対象としているが、正規表現のように Q[1-9] としても同様の結果が得られる。

関数自体は2020年の記事で紹介されたもので、ユーザー定義関数（UDF）となっている。

関数のソースも公開されているので、自分の環境上に関数を定義することも可能だ。

これでカラム数が不特定の場合でも同じSQLでUNPIVOTできますね

まとめ

今回はBigqueryで不特定のカラムに対して動的にunpivotするSQLについて解説した。

Bigqueryで不特定のカラムに対して動的にunpivotするには、ユーザー定義関数 fhoffa.x.unpivot() を用いる。

縦持ちにする対象カラムを正規表現で指定できるので、不特定多数のカラムに対してUNPIVOTするのに便利である。

これでイチイチ列名を指定しなくて済むね

他にもBigQueryのテクニックに関する記事もあるので、興味があれば見てみて欲しい。