英文:
How to check whether a BigQuery array includes a NULL value?
问题
在BigQuery中,检查是否有NULL值在数组中的标准方法是使用IN UNNEST(...)
。例如:
SELECT NULL IN UNNEST([0, 1, 1, 2, 3, null, 5]) AS contains_null;
有没有比这更直接的方法呢?如果是BigQuery团队的成员或者熟悉性能/优化的人员,是否有关系如果我在子查询中添加一个LIMIT 1
,即使它找到结果,exists
操作符是否会自动短路,还是我必须手动在子查询中添加LIMIT 1
?
英文:
The standard way to check if a value is in an array in BigQuery is using IN UNNEST(...)
. For example:
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
What is the suggested way to check if the NULL
value is in an array? Is there a more direct way than doing:
SELECT EXISTS(SELECT 1 FROM UNNEST([0,1,1,2,3,null,5]) v WHERE v IS NULL)
Is there a more straightforward way for this? And if someone on the BigQuery team or someone familiar with performance/optimizations: does it matter if I add a LIMIT 1
to the sub-select -- i.e., does the exists
operator short-circuit automatically once it finds a result or do I have to manually add in a LIMIT 1
to the subquery?
答案1
得分: 1
以下是要翻译的内容:
这里有一个可能的简单替代方案要考虑:
SELECT ANY_VALUE(x IS NULL) AS contains_value
FROM UNNEST([0, 1, 1, 2, 3, NULL, 5]) AS x;
(也可以使用 COUNTIF(x IS NULL) > 0
或 MAX(x IS NULL)
代替 ANY_VALUE(x IS NULL)
,但我认为后者更可取)。
不确定在效率方面如何比较。与您提出的类似的东西可能一样好(并且更接近文档中的内容):
SELECT EXISTS(
SELECT * FROM UNNEST([0, 1, 1, 2, 3, null, 5]) AS x
WHERE x IS null
LIMIT 1)
AS contains_value;
除非您能从Google开发人员那里得到明确的关于 LIMIT
问题的答案,否则我建议保留它 - 因为直观地说,外部选择语句只需要查看一个单一的值。
英文:
Here is a possible simple alternative to consider:
SELECT ANY_VALUE(x IS NULL) AS contains_value
FROM UNNEST([0, 1, 1, 2, 3, NULL, 5]) AS x;
(Could also use COUNTIF(x IS NULL) > 0
or MAX(x IS NULL)
in place of ANY_VALUE(x IS NULL)
but think the latter is preferable).
Not sure how this will compare in terms of efficiency. Something very similar to what you have proposed may be just as good (and closer to what is in the documentation):
SELECT EXISTS(
SELECT * FROM UNNEST([0, 1, 1, 2, 3, null, 5]) AS x
WHERE x IS null
LIMIT 1)
AS contains_value;
Unless you can get a definitive answer from the Google developers on the LIMIT
question, I would leave it in - as intuitively the outer select then only needs to look at a single value.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论