Standard SQLのCOALESCEで、時間経過によってカラム名が変化したデータを柔軟に抽出する

By Shunya Ueta on (Last updated:)
bigquery sql

データの蓄積帰還が長くなってくると、例えば JSON形式でログを取っているが、同じデータでもマイグレーションやロギングロジックの更新などでkey の名前が変化したりする場合がある。

その場合取り扱いに困るのが、古いkeyと新しいkey をどのように併合するかだ。 例えば特定の日次できれいにデータが入れ替わっているのなら、色々やりようがあるが、クライアントなどのログの場合データの変化も均一ではないので、徐々に変化していることが大半なので、日次で別々の抽出をして結合するというアプローチも難しい。

その際に役立つのが Standard SQL 条件付き構文の COALESCE だ。

COALECSCE は、引数の最初の非NULLの値を返す関数で、

1

COALESCE(NULL, 'B', 'C')

だと Bが返される。この関数を使うことで、複数カラムを一つに併合することができる。

具体例を交えつつ実践してみる

例えば、以下のように昔のカラム名が title で、全く同じデータが新しいカラムの title_v2 に入ってきているとする。

NOTE: json を例題にkeyの抽出にしたほうが実際の状況に沿いますが、カラムのみで表現したほうが説明が簡単なので今回はそちらを採用。

用意したデータ

1
2
3
4
5
6
7
8

WITH menues AS
 (SELECT "うどん" as title, NULL as title_v2, "2021/10/06" as created UNION ALL
  SELECT "ラーメン", NULL, "2021/10/07"  UNION ALL
  SELECT NULL, "そば", "2021/10/08" UNION ALL
  SELECT "カツ丼", NULL, "2021/10/09" UNION ALL
  SELECT "カツ丼", "カツ丼", "2021/10/10" UNION ALL
  SELECT NULL, "カレー", "2021/10/11")
SELECT * FROM menues
titletitle_v2created
うどん2021/10/06
ラーメン2021/10/07
そば2021/10/08
カツ丼2021/10/09
カツ丼カツ丼2021/10/10
カレー2021/10/11

2021/10/10 のデータなどは旧カラムと新カラムにダブルライトされています。

COALESCE で複数カラムを併合する

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

WITH menues AS
 (SELECT "うどん" as title, NULL as title_v2, "2021/10/06" as created UNION ALL
  SELECT "ラーメン", NULL, "2021/10/07"  UNION ALL
  SELECT NULL, "そば", "2021/10/08" UNION ALL
  SELECT "カツ丼", NULL, "2021/10/09" UNION ALL
  SELECT "カツ丼", "カツ丼", "2021/10/09" UNION ALL
  SELECT NULL, "カレー", "2021/10/10")
SELECT
COALESCE(title, title_v2) as title,
created
FROM menues

結果

titlecreated
うどん2021/10/06
ラーメン2021/10/07
そば2021/10/08
カツ丼2021/10/09
カツ丼2021/10/09
カレー2021/10/10

はい、上記の結果のようにCOALESCE を使えば２つの時間経過によって別れてしまったカラムを一つのカラムとして併合することができました。非常に便利ですね。

Reference

BigQuery Conditional expressions- COALESCE

See Also