Shunya Ueta

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

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

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

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

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

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

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

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

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

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

用意したデータ

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 で複数カラムを併合する

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 を使えば２つの時間経過によって別れてしまったカラムを一つのカラムとして併合することができました。 常に便利ですね。

References

BigQuery Conditional expressions- COALESCE

---

関連しているかもしれない記事


📮 📧 🐏: 記事への感想のおたよりをおまちしてます。 お気軽にお送りください。 メールアドレス入力があればメールで返信させていただきます。 もちろんお返事を希望せずに単なる感想だけでも大歓迎です。

このサイトの更新情報をRSSで配信しています。 お好きなフィードリーダーで購読してみてください。

このウェブサイトの運営や著者の活動を支援していただける方を募集しています。 もしよろしければ、Buy Me a Coffee からサポート(投げ銭)していただけると、著者の活動のモチベーションに繋がります✨
Amazonでほしいものリストも公開しているので、こちらからもサポートしていただけると励みになります。

#sql #bigquery