如何检查 BigQuery 数组是否包含 NULL 值?

huangapple go评论69阅读模式
英文:

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) > 0MAX(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.

huangapple
  • 本文由 发表于 2023年6月22日 08:46:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/76527994.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定