DB

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

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

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


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

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


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

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

これは面倒だ。

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


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


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

この記事を書いている人


システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。Pythonと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)となっている。

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


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



まとめ

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


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

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


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



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

ITipsと同じようなブログを作る方法

ブログに興味がありますか?

もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

ランキング参加中

にほんブログ村 IT技術ブログへ

他にもブログやSNSで紹介してくれると励みになります。

はてブのコメントで酷評されると泣きます(´;ω;`)

-DB
-, ,

© 2024 ITips