我有一个“供应商”表，看起来像这样......

**company itemKey itemPriceA itemPriceB**
companyA, 203913, 20, 10
companyA, 203914, 20, 20
companyA, 203915, 25, 5
companyA, 203916, 10, 10

每个公司可能有数百万行，我想查询它以返回每个公司的 itemPriceA 和 itemPriceB 之间的代表性增量。我不在乎我带回哪个增量，只要它不是零/空（如第2行或第4行），所以我使用像这样的ANY_VALUE...

SELECT company
, ANY_VALUE(CASE WHEN (itemPriceA-itemPriceB)=0 THEN null ELSE (itemPriceA-itemPriceB) END)
FROM vendors
GROUP BY 1

它似乎有效，但我注意到有两个句子似乎矛盾谷歌的文档...

"当组中所有行的表达式为 NULL 时，返回 NULL。 ANY_VALUE 的行为就像指定了 RESPECT NULLS 一样；考虑并选择表达式为 NULL 的行。"

如果 ANY_VALUE 返回 null“当表达式为 NULL 时allrows in the group" 它不应该为 companyA 返回 null，对吧（因为 4 行中只有 2 行为 null）？但是第二句话听起来确实会包含 null 行。

附注您可能想知道为什么我不简单地添加一个 WHERE 子句“WHERE itemPriceA-itemPriceB>0”，但如果一家公司只有匹配的价格，我仍然希望该公司在我的结果中返回。

澄清

恐怕所接受的答案必须提供与文档相矛盾的更有力的证据。

@Raul Saucedo 建议以下 BigQuery 文档引用 WHERE 子句：

考虑并选择表达式为 NULL 的行

不是这种情况。 ANY_VALUE 文档中的任何地方都没有提到 WHERE 子句。 （无处the page。尝试ctrl+f）并且文档很清楚，正如我将解释的那样。

@d3wannabe 对此的疑问是正确的：

它似乎有效，但我注意到有 2 个句子与 Google 的文档似乎矛盾......

“当组中所有行的表达式为 NULL 时，返回 NULL。ANY_VALUE 的行为就像指定了 RESPECT NULLS；表达式为 NULL 的行将被考虑并可能被选择。”

但这些文档并不矛盾。 2句话并存。

1. “当组中所有行的表达式均为 NULL 时，返回 NULL。”So if all列中的行为 NULL，则将返回 NULL。
2. “ANY_VALUE 的行为就像指定了 RESPECT NULLS；表达式为 NULL 的行将被考虑并可能被选择。”因此，如果该列中有混合有 NULL 和实际数据的行，它将从该列中选择任何内容，包括 null。

如何在 BigQuery 中创建不含 null 的 ANY_VALUE

我们可以用ARRAY_AGG将一组值转换为列表。该聚合函数可以选择 INGORE NULLS。然后，我们在忽略空值后从列表中选择 1 项。

如果我们有一个包含 2 列的表：id and mixed_data, where mixed_data有一些行包含空值：

SELECT
    id,
    ARRAY_AGG( -- turn the mixed_data values into a list
        mixed_data -- we'll create an array of values from our mixed_data column
        IGNORE NULLS -- there we go!
        LIMIT 1 -- only fill the array with 1 thing
    )[SAFE_OFFSET(0)] -- grab the first item in the array
    AS any_mixed_data_without_nulls
FROM your_table
GROUP BY id

在这里查看类似的答案：

• https://stackoverflow.com/a/53508606/6305196
• https://stackoverflow.com/a/62089838/6305196

更新，2022年8月12日

有证据表明文档可能与函数的实际行为不一致。看塞缪尔的最新回答探索他的方法论。

但是，我们无法知道文档是否不正确且 ANY_VALUE 的行为符合预期，或者 ANY_VALUE 是否存在错误且文档表达了预期的行为。我们不知道 Google 在解决此问题时是否会更正文档或功能。

因此，我将继续使用 ARRAY_AGG 创建一个忽略空值的安全 ANY_VALUE，直到我们看到 Google 的修复。

请在 Google 的问题跟踪器中对该问题进行投票看到这个问题得到解决。