为何GROUP BY无法搭配分析（窗口）函数？以first_value为例

原始查询及报错 #

我尝试执行以下SQL查询：

SELECT
  dim1,
  dim2,
  first_value(dim3) over (partition by dim1, dim2 order by m1 desc) as top_dim3,
  sum(m1) as m1,
  avg(m2) as m2
FROM
  [TABLE_ID]
GROUP BY
  dim1,
  dim2

得到报错：

SELECT列表中的表达式引用了既未分组也未聚合的列page

尝试将top_dim3添加到GROUP BY中后，又出现新报错：

列top_dim3包含分析函数，不允许出现在GROUP BY中

报错原因：SQL执行顺序的深层逻辑 #

这不是单纯的语法限制，核心是SQL的执行顺序规则：
SQL语句的执行流程大致是：FROM → WHERE → GROUP BY → 聚合函数计算 → HAVING → SELECT → ORDER BY。

• 聚合函数（sum/avg这类）是在GROUP BY阶段计算的，此时会将原始数据按分组字段聚合成每组一行的结果，原始行的非分组/非聚合列（比如dim3、未聚合的m1）已经不再可用。
• 分析函数（窗口函数，比如first_value）是在SELECT阶段执行的，此时GROUP BY已经完成，无法再访问原始行的细节数据。

原始查询中，first_value试图引用原始行的m1和dim3，但GROUP BY之后这些数据已经被聚合，所以触发第一个报错；而把分析函数生成的top_dim3加入GROUP BY时，因为GROUP BY阶段早于分析函数的执行阶段，此时top_dim3还不存在，所以触发第二个报错。

子查询方案的合理性与注意事项 #

用子查询实现需求的逻辑是合理的：

SELECT
  dim1,
  dim2,
  top_dim3,
  sum(m1) as m1,
  avg(m2) as m2
FROM (
  SELECT
    dim1, dim2, m1, m2,
    first_value(dim3) over (partition by dim1, dim2 order by m1 desc) as top_dim3
  FROM [TABLE_ID]
)      
GROUP BY
  dim1,
  dim2,
  top_dim3

先在子查询中对原始行计算窗口函数，此时每个行都能拿到所属分组的top_dim3（同一个分组内的所有行的top_dim3值一致），再在外层按dim1、dim2、top_dim3分组聚合——因为同一分组的top_dim3相同，所以不会改变聚合结果。

关于“避免子查询”的顾虑：

• 可读性方面：这种子查询反而能清晰分离“计算分组维度值”和“聚合统计”两个逻辑，只要命名清晰，并不会降低可读性。
• 性能方面：在BigQuery这类现代数据仓库中，查询优化器会自动识别这类逻辑，通常不会产生额外的性能开销，执行计划和更简洁的写法差异不大。
• 潜在问题：如果后续修改窗口函数逻辑，导致同一分组内的top_dim3出现不同值，外层GROUP BY会把这些值拆分成不同组，需要注意这种场景的兼容性。

BigQuery专属的简洁方案 #

BigQuery支持扩展语法，用any_value配合having子句可以直接实现需求，无需子查询：

SELECT
  dim1,
  dim2,
  any_value(dim3 having max(m1)) as top_dim3,
  sum(m1) as m1,
  avg(m2) as m2
FROM
  [TABLE_ID]
GROUP BY
  dim1,
  dim2

这里any_value(dim3 having max(m1))的作用是：在每个分组中，找到m1最大的那一行对应的dim3值，本质是把“找维度值”和“聚合统计”合并在GROUP BY阶段完成。

通用SQL中的处理方式 #

在标准SQL或其他数据库（如MySQL、PostgreSQL）中，通常没有BigQuery这种any_value的扩展用法，一般还是采用子查询/CTE的方式实现。部分数据库有专属语法，比如PostgreSQL的DISTINCT ON，但不属于通用标准。

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