データの分析や処理に便利なBigQuery(BQ)。
大規模データの処理に優れており、巨大なデータに対しても短時間でSQLを実行することができる。
データを扱っていると、横方向に長い無数のカラム数を持つデータを、カラム名をそれぞれ一つのキー列に集約して縦長のデータにしたいことがある。
しかし UNPIVOT は横持ちから縦持ちにしたいカラム名を固定で指定する必要がある。
これは面倒だ。
カラムの数も固定とは限らないので動的に指定したい。
そこで今回はBigqueryで不特定のカラムに対して動的にunpivotするSQLについて紹介する。
この記事を書いている人
記事を読むメリット
Bigqueryで不特定のカラムに対して動的にunpivotするSQLがわかり、unpivotが得意になる
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()。
使い方は以下の通り。
SQL
SELECT product, unpivotted FROM Produce a , UNNEST(fhoffa.x.unpivot(a, 'Q')) unpivotted
結果はこのようになる。
結果
| product | unpivotted.key | unpivotted.value |
| Kale | Q1 | 51 |
| Kale | Q2 | 23 |
| Kale | Q3 | 45 |
| Kale | Q4 | 3 |
| Apple | Q1 | 77 |
| Apple | Q2 | 0 |
| Apple | Q3 | 25 |
| Apple | Q4 | 2 |
fhoffa.x.unpivot(a, 'Q') と記載した第二引数の部分が縦持ち変換する対象のカラムを指定する場所となっている。
この場合はカラム名に Q を含むカラムを対象としているが、正規表現のように Q[1-9] としても同様の結果が得られる。
関数自体は2020年の記事で紹介されたもので、ユーザー定義関数（UDF）となっている。
関数のソースも公開されているので、自分の環境上に関数を定義することも可能だ。
まとめ
今回はBigqueryで不特定のカラムに対して動的にunpivotするSQLについて解説した。
Bigqueryで不特定のカラムに対して動的にunpivotするには、ユーザー定義関数 fhoffa.x.unpivot() を用いる。
縦持ちにする対象カラムを正規表現で指定できるので、不特定多数のカラムに対してUNPIVOTするのに便利である。
他にもBigQueryのテクニックに関する記事もあるので、興味があれば見てみて欲しい。
。 大規模データの処理に優れており、巨大なデータに対しても短時間でSQLを実){kind=link}