关于BigQuery中ANY_VALUE函数NULL值包含逻辑的疑问及业务查询实现

首先要明确：Google文档里的两句话并不矛盾，我们来把它们拆开理解：

"当分组内所有行的表达式结果均为NULL时，返回NULL。ANY_VALUE的行为等同于指定了RESPECT NULLS；表达式结果为NULL的行将被纳入考虑范围，且可能被选中。"

• 第一句说的是极端情况：如果某个分组里，所有行的表达式计算结果都是NULL，那ANY_VALUE只能返回NULL，别无选择。
• 第二句则是函数的常规逻辑：只要分组里存在NULL结果的行（哪怕同时有非NULL行），这些NULL行都会被当作候选值池的一部分，ANY_VALUE完全有可能随机选中它们返回。

回到你的场景：companyA的分组里有2个非NULL的delta（10和20），但还有2个被CASE转成NULL的行。你当前的查询其实有隐患——哪怕存在非零差值，ANY_VALUE也可能随机选中NULL返回，这显然不符合你想要获取有代表性非零差值的需求。

你提到不想直接加WHERE itemPriceA != itemPriceB的原因（要保留全量差值为0的企业）非常合理，这里给你一个更稳妥且高效的优化写法：

优化后的查询（推荐） #

SELECT
  company,
  ANY_VALUE(itemPriceA - itemPriceB)
    FILTER (WHERE itemPriceA != itemPriceB) AS representative_delta
FROM vendors
GROUP BY company

为什么这个写法更好？ #

1. 彻底避免选中NULL：FILTER (WHERE itemPriceA != itemPriceB)会让ANY_VALUE只从非零差值的行里选取值，完全排除了那些差值为0的行（也就是你原来用CASE转成NULL的行）的候选资格。
2. 保留全零差值的企业：如果某企业的所有行差值都是0，FILTER后的结果集为空，ANY_VALUE会返回NULL，该企业仍会出现在结果中，完美匹配你的需求。
3. 更简洁高效：不需要嵌套子查询，逻辑清晰，BigQuery的查询优化器也能更好地处理这种写法，尤其适合你百万级数据的场景。

对比你的原始查询，这个写法从根源上解决了文档描述带来的疑问——因为我们直接限定了ANY_VALUE的候选范围，不会再出现“选中NULL”的意外情况。

内容的提问来源于stack exchange，提问作者d3wannabe