SQL滚动求和遇报错：column 'amount_paid'需在GROUP BY或聚合函数中使用

报错信息 #

column "amount_paid" must appear in the GROUP BY clause or be used in an aggregate function

用户原SQL代码 #

select 
    month,
    Id,
    SUM(amount_paid) OVER(PARTITION BY month ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
from table
where month >= '2022-01-01' 
and Id between 0 and 12
group by month,Id
order by month,Id

数据示例 #

问题原因与解决方法 #

报错的核心原因是你混淆了聚合函数GROUP BY和窗口函数的使用逻辑：

• 你用了GROUP BY month,Id，但amount_paid既不在GROUP BY列表里，也没被聚合函数包裹，数据库不知道该如何处理这个字段（虽然你是在窗口函数里用它，但GROUP BY是先于窗口函数执行的）。
• 你的需求是计算按月分区、按Id排序的累积和，本身窗口函数就能完成这个计算，不需要额外的GROUP BY（除非同一month+Id有重复数据需要先聚合）。

方案1：去掉多余的GROUP BY（适用于同一month+Id无重复数据的情况） #

如果你的数据里每个month和Id组合都是唯一的（像你给出的示例数据那样），直接删除GROUP BY month,Id即可：

select 
    month,
    Id,
    SUM(amount_paid) OVER(PARTITION BY month ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
from table
where month >= '2022-01-01' 
and Id between 0 and 12
order by month,Id

方案2：先聚合再计算累积和（适用于同一month+Id有重复数据的情况） #

如果同一month+Id有多条数据，需要先聚合求和，再基于聚合后的结果计算累积和：

with aggregated_data as (
    select 
        month,
        Id,
        SUM(amount_paid) as total_amount
    from table
    where month >= '2022-01-01' 
    and Id between 0 and 12
    group by month,Id
)
select 
    month,
    Id,
    SUM(total_amount) OVER(PARTITION BY month ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
from aggregated_data
order by month,Id

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