为什么BigQuery中的比较和条件聚合会失败

为什么在BigQuery中比较和条件聚合失败？

BigQuery(BQ) 对于数据分析或处理非常有用。

它擅长处理海量数据。它在短时间内返回汇总结果。

当我们处理大数据时，有时我们希望提取或汇总满足特定条件的数据。

通常我们使用如下比较运算符。

WHERE COLUMN1 = "A"

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

但它在特定情况下会失败。

为什么它不起作用。

所以今天我介绍一下《为什么BigQuery中比较和条件聚合会失败》。

作者

中级工程师（AI、系统）。擅长Python和SQL。

阅读优势

你可以理解“为什么比较和条件聚合在 BigQuery 中失败”。那么您就不必担心比较和条件聚合。

数据

首先，准备资料。

将此 csv 文件导入为“null_sample”表。

Data

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

然后你可以看到如下表。

Table

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"

Result

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

为了获得条件和，我们可以使用 SUM 和 IF 函数。

SQL

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

Result

Row	sum_col1_a
1	3

失败的例子

那么他们就是失败的例子。

他们使用 != 并且它的比较不起作用。

SQL

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

Result

No result

即使有条件聚合，它也会像下面这样失败。

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

Result

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 或 col3 中它应该是 5。

为什么聚合失败。

为什么比较和条件聚合在 BigQuery 中失败

BigQuery 中比较和条件聚合失败的原因是 null。

null 是特殊的不会同时对 = 和 != 返回 true。

所以如果你想比较数据，你应该通过 IFNULL 或 COALESCE 将 null 替换为其他值。

SQL

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

Result

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

对于条件聚合，我们应该使用 IFNULL 或 COALESCE。

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

Result