BigQuery窗口函数报错排查：关联交易ID时的用户年消费分组问题

在BigQuery中尝试获取用户年度消费金额并关联到交易ID时，执行SQL遇到错误：

SELECT list expression references column price which is neither grouped nor aggregated at [4:7]

原SQL代码：

SELECT
  trans_id,
  user_id,
  SUM(price) OVER (PARTITION BY user_id, EXTRACT(year FROM date)) as user_annual_spend 
FROM sample_sales_table
GROUP BY trans_id, user_id

表字段说明：

• trans_id：STRING类型，可为空
• user_id：STRING类型，可为空
• date：DATE类型，格式yyyy-mm-dd，可为空
• price：NUMERIC类型，可为空

示例数据：

trans_id | user_id |   date   | price
AAAAAAA  | user1   |2022-01-09| 20
AAAAAAC  | user1   |2022-05-01| 50
BAAAAAB  | user1   |2021-01-05| 10
BAAAACB  | user1   |2020-01-01| 40
BAAAAAC  | user2   |2021-02-01| 30

期望输出：

trans_id | user_id |user_annual_spend
AAAAAAA  | user1   | 70
AAAAAAC  | user1   | 70
BAAAAAB  | user1   | 10
BAAAACB  | user1   | 40
BAAAAAC  | user2   | 30

你错误添加了GROUP BY trans_id, user_id子句。BigQuery的GROUP BY规则要求：SELECT列表中所有未被聚合函数包裹的列，必须出现在GROUP BY列表里。但你的窗口函数SUM(price) OVER (...)用到了price和date，这两个字段既不在GROUP BY列表中，也没有被聚合处理，因此触发报错。

更核心的问题是，你的需求是给每条交易记录附上用户当年的总消费——窗口函数本身就是用来保留原始行的前提下计算分组聚合值的，完全不需要用GROUP BY来合并行，两者逻辑冲突。

SELECT
  trans_id,
  user_id,
  SUM(price) OVER (PARTITION BY user_id, EXTRACT(year FROM date)) AS user_annual_spend
FROM sample_sales_table

• 去掉多余的GROUP BY后，窗口函数会按user_id和交易年份（通过EXTRACT(year FROM date)提取）分组，计算每个用户每年的消费总额，同时保留每一条交易记录，正好实现了把年度消费金额关联到对应交易ID的需求。
• 从示例数据来看，trans_id本身是唯一标识，GROUP BY trans_id, user_id不会起到任何聚合作用，反而会因为未包含price和date字段触发语法错误。

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