DB

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

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


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

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


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

通常は比較演算子で

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

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

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

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

この記事を書いている人


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

記事を読むメリット

BigQueryで「!=」比較や条件付きSUM集計が間違う原因がわかり、比較や条件付き集計が得意になる


データ準備

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

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

データ

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


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

テーブル

Row col1 col2 col3
1 null b c
2 null null c
3 a b c
4 a b null
5 a null null



比較や条件付き集計の例

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

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

SQL

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


結果

Row col1 col2 col3
1 a b c
2 a b null
3 a null null


そして条件付き集計は SUMIF を用いる。

SQL

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


結果

Row sum_col1_a
1 3



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

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

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

SQL

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


結果

結果無し


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

SQL

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


結果

Row sum_col1_a sum_col1_not_a sum_col2_not_a sum_col3_not_a
1 3 0 3 3


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

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



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

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

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

SQL

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


結果

Row col1 col2 col3
1 null b c
2 null null c


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

SQL

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


結果

Row sum_col1_a sum_col1_not_a sum_col2_not_a sum_col3_not_a
1 3 2 5 5


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



まとめ

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

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

対処方法はコレ。

ココがポイント

  • IFNULLCOALESCE でnullを置換してから比較や集計をする


nullの扱いが厄介ですね



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

ITipsと同じようなブログを作る方法

ブログに興味がありますか?

もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

ランキング参加中

にほんブログ村 IT技術ブログへ

他にもブログやSNSで紹介してくれると励みになります。

はてブのコメントで酷評されると泣きます(´;ω;`)

-DB
-,

© 2022 ITips