BigQueryで「!=」比較や条件付きSUM集計が間違う原因

BigQueryで何か比較や集計がうまくいかないんだけど…

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

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

データを扱っていると、特定の条件に合ったデータだけ抜き出したり条件付き集計したいことがある。

通常は比較演算子で

WHERE COLUMN1 = "A"

SUM(IF(COLUMN1 = "A",1,0)

のように条件指定するのだが、ある場合ではうまくいかない。

なぜうまく比較や集計ができないのか。

そこで今回はBigQueryで「!=」比較や条件付きSUM集計が間違う原因について紹介する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。

データ準備

まずは元となるデータを準備する。

以下のCSVをテーブル名 null_sample としてインポートする。

col1,col2,col3
a,b,c
,b,c
,,c
a,b,
a,,

取り込むと以下のようなテーブルになる。

比較や条件付き集計の例

まずは比較や条件付き集計がうまくいく例を紹介する。

比較で = を使う場合は以下の通り。

SELECT * FROM test.null_sample
WHERE col1 = "a"

そして条件付き集計は SUM と IF を用いる。

SELECT
SUM(IF(col1="a",1,0)) as sum_col1_a
FROM test.null_sample

比較や条件付き集計がうまくいかない例

次に比較や条件付き集計がうまくいかない例を示す。

比較や集計は != を使うとうまく比較できないことがある。

SELECT * FROM test.null_sample
WHERE col1 != "a"

集計でも != を使うと以下のようになる。

SELECT
SUM(IF(col1="a",1,0)) as sum_col1_a,
SUM(IF(col1!="a",1,0)) as sum_col1_not_a,
SUM(IF(col2!="a",1,0)) as sum_col2_not_a,
SUM(IF(col3!="a",1,0)) as sum_col3_not_a
FROM test.null_sample

テーブルは5行あり、値が a でない数は col1なら2つ、col2,3は aをひとつも含まないので5でないとおかしい。

何故このようなことが起こるのか。

BigQueryで「!=」比較や条件付きSUM集計が間違う原因

BigQueryで「!=」比較や条件付きSUM集計が間違う原因、それは null は =、!= どちらで比較してもTrueにならないから。

なので比較する際はnullを IFNULL か COALESCE で別の値に置換してから比較する。

SELECT * FROM test.null_sample
WHERE IFNULL(col1,"") != "a"

集計の場合も IFNULL か COALESCE を使ってから集計する。

SELECT
SUM(IF(IFNULL(col1,"")="a",1,0)) as sum_col1_a,
SUM(IF(IFNULL(col1,"")!="a",1,0)) as sum_col1_not_a,
SUM(IF(IFNULL(col2,"")!="a",1,0)) as sum_col2_not_a,
SUM(IF(IFNULL(col3,"")!="a",1,0)) as sum_col3_not_a
FROM test.null_sample

今度は期待通り a でない行を抽出したり、a でない値の数を集計できた。

まとめ

今回はBigQueryで「!=」比較や条件付きSUM集計が間違う原因について解説した。

BigQueryで「!=」比較や条件付きSUM集計が間違う原因は、null を直接比較しているから。

対処方法はコレ。

nullの扱いが厄介ですね

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