如何检查该表在 Bigquery 中是否包含空值?

Question

我试过这个。这是对的吗？

#standardSQL
with table1 as(
SELECT "somename" name,"someaddress" as adrs UNION ALL
SELECT null name,null UNION ALL
SELECT null name,null
)
SELECT sum(array_length(regexp_extract_all(to_json_string(table1),"null[,}]")))no_of_nulls from table1

Answer 1

从问题和评论中仍然不清楚，但假设您只有几列，请将它们显式列为查询的一部分以计算 NULL 值：

WITH table1 AS (
SELECT "somename" AS name,"someaddress" AS adrs UNION ALL
SELECT NULL, NULL UNION ALL
SELECT NULL, NULL
)
SELECT COUNTIF(name IS NULL) + COUNTIF(adrs IS NULL) AS null_count
FROM table1

这比转换为 JSON 并将正则表达式应用于结果更有效。