カラムがずれていてUNIONが面倒問題がこれで解決できる（かもしれない）

UNION ALLはめんどうなので何とかしたい

通常のUNION ALLを使うとき、複数のSELECT文のカラム数と順序が完全に一致している必要がある。テーブル数が多いと、カラム名を全部列挙して順序を合わせるのは面倒だ。

なんとかできないものかと以前から思っていたが、FULL UNION ALL CORRESPONDINGなる関数をみつけた。

【アップデート】BigQuery の BY NAME と CORRESPONDING でカラム名ベースの集合演算が可能になりました

いつの間にかに追加されてたみたい。

サンプル

実際にどう動くか見てもらうのが早いだろう。

2つのテーブルがある。テーブルaは1カラム、テーブルbは2カラムなのでUNION ALLはできない。aにカラムyを入れるか、bのカラムyを削ることが必要だ。

しかし、FULL UNION ALL CORRESPONDINGは実行できる。カラムが足りなくても保管してNULLで埋めてくれるし、順番がずれていてもカラム名でUNIONしてくれる。

WITH a AS (
  SELECT 1 AS x
),
b AS (
  SELECT 3 AS y, 2 AS x
)
SELECT *
FROM a 
FULL UNION ALL CORRESPONDING 
SELECT *
FROM b

-- 実行結果：
-- x    y
-- 1    NULL
-- 2    3

FULL UNION ALL CORRESPONDINGのメリット

• カラムが不足していた場合に自動的に保管してNULLで埋めてくれる
• カラム順序を気にしなくてよい。各SELECT文でカラムの順序が違っていても、カラム名が一致していれば自動的にマッチングされる
• 追加が楽になる。必要なテーブルにだけ追加すればいい

FULL UNION ALL CORRESPONDINGのデメリット

• 今のところBigQuery専用の構文らしい
• カラムがずれていてもエラーが起きない

ぐらいだろうか。メリットに比べるとデメリットはかなり小さい印象だ。

あと、情報がなく本当に正しい挙動なのか保証できない、というのもデメリットかもしれない

その他の細かい注意点

• 同じカラム名でデータ型が違う場合はエラーになる
• 一番上のテーブルに値がない場合、NULLが数値型で埋まるので2番目以降に文字列が入るとエラーになる模様

最初のはUNIONと同じ。2番目は気づかないとはまりそう。最初のテーブルでNULLをstringにCASTするか、文字列がある場合を先頭に持ってくるしかない。

いっそ先頭のテーブルに強制的にCASTするオプションとか作ってほしい。

INNERとLEFTもある

JOINと同じく、INNERとLEFTもある。

• INNER UNION ALL CORRESPONDINGなら共通のカラムだけ
• LEFT UNION ALL CORRESPONDINGなら一番最初のテーブルにあるカラムだけ

LEFTはLEFT JOINのイメージなんだろうけど、左というのがかえってわかりづらい気がしなくもない。

両方ともFULLでUNIONしてからカラムで絞り込みする方がいいので、よほど巨大で先に絞り込みしないと無理な状況でなければFULLでいいのでは。

なおRIGHTはないみたい。

もう全部これでいいんじゃないの

いままでさんざん悩まされている問題であり、こんな便利な関数があることになぜ今まで気が付かなかったのか、見つけた時には本当におどろいた。データ整備でUNION使うならもう全部これでいいのでは。

ただ、今ならAIに渡して適度に埋めてもらえば済むことも多い。もう少し早ければ大活躍していたのではと考えると非常に惜しい。