Zenn
✨

BigQueryでnullを考慮してイコールの評価をしたい

2021/07/28に公開
GCP
SQL
BigQuery
tech

やりたいこと

BigQueryでカラムAとカラムBが一致しないレコードの件数を調べたいことがあったのですが、
A != Bとするとどちらか一方でもnullだと結果がnullになってしまいます。

  • AもBもnullでない　→　普通に評価
  • Aだけnull or Bだけnull　→　not equal
  • AもBもnull　→　equal

となるようにしたい。

解決方法

# equalの評価
coalesce(A = B, A is null and B is null)
# not equalの評価
not coalesce(A = B, A is null and B is null)

前述の通り、AかBどちらか一方でもnullだとA = Bの結果はnullです。
coalesceは引数の中の最初の非null式の値を返すので、AとBどちらもnullでない場合はA = Bの結果を、AかBどちらか一方でもnullの場合はA is null and B is nullの結果を返します。

  1. AもBもnullでない　→　A = Bを評価
  2. Aだけnull or Bだけnull　→　A is null and B is nullを評価　→　not equal
  3. AもBもnull　→　A is null and B is nullを評価　→　equal

という感じです。
この式をequal_respect_nullsなど名前つけてUDFにしてしまうと便利かもしれません。

CREATE OR REPLACE FUNCTION `<project>.<dataset>.equal_respect_nulls`(A ANY TYPE, B ANY TYPE) AS (
coalesce(A = B, A is null and B is null)
);

ボツ案

愚直な方法としてまず以下の式が思いつきました。

A = B or (A is null and B is null)

しかし、これだと2.のパターンがうまくいかないです。
論理演算子のドキュメントを見ると、null or falseの結果はnullとなってしまうことが分かります。
そこで、代わりにcoalesceを使ったのが解決方法に挙げた式です。

まとめ

coalesce、覚えておくとたまに役に立ちますね

Discussion