google-bigquery - 在 BigQuery 中包含具有 ANY_VALUE 的空值

Question

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

**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

它似乎有效，但我注意到 Google's documentation 中的 2 个句子似乎相互矛盾。 ...

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

如果 ANY_VALUE 返回 null “当组中 所有 行的表达式为 NULL 时”它不应该为 companyA 返回 null 对(因为 4 行中只有 2 行为 null)？但是第二句听起来确实会包含空行。

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

Answer 1

澄清

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

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

rows for which expression is NULL are considered and may be selected

事实并非如此。 ANY_VALUE 文档中的任何地方都没有提到 WHERE 子句。 (the page 上无处。尝试 ctrl+f 。)而且文档很清楚，我会解释。

@d3wannabe 对此感到疑惑是正确的:

It seems to be working but I notice 2 sentences that seem contradictory from Google's documentation...

"Returns NULL when expression is NULL for all rows in the group. ANY_VALUE behaves as if RESPECT NULLS is specified; rows for which expression is NULL are considered and may be selected."

但文档并不矛盾。两句并存。

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

如何在 BigQuery 中创建不带空值的 ANY_VALUE

我们可以使用 ARRAY_AGG将一组值变成一个列表。此聚合函数可以选择 INGORE NULLS。然后我们在忽略空值后从列表中选择 1 项。

如果我们有一个包含 2 列的表:id 和 mixed_data，其中 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-08-12

有证据表明文档可能与函数的实际行为不一致。见 Samuel's latest answer探索他的方法论。

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

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

Please upvote the issue in Google's Issue Tracker看到这个问题得到解决。