目的,在编写sql脚本中,希望获取销售组织每个月的销售数量,和截止该月的累积销售数据
刚开始写的逻辑如下:
select calmonth,level1,level2,
sum(amount) as amount
sum(amount) over (PARTITION BY level1,level2 ORDER BY calmonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as acc_amount
from B
where level2 in ('东北运营中心','华南运营中心')
group by calmonth,level1,level2
这个会爆以下问题:
SQL 错误 [42601]: ERROR: syntax error at or near “sum”¶ Position: 1796
最终发现原来是在使用窗口函数的使用,sum(amount) 代表的是一个分组字段,需要在sum一遍
修改后的逻辑如下:
select calmonth,level1,level2,
sum(amount) as amount
sum(sum(amount)) over (PARTITION BY level1,level2 ORDER BY calmonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as acc_amount
from B
where level2 in ('东北运营中心','华南运营中心')
group by calmonth,level1,level2