小白学习MySQL - 增量统计SQL的需求 - 开窗函数的方案

select code, date_format(cdate, '%Y-%m'), 
ctotal,
sum(ctotal) 
over (partition by code, date_format(cdate, '%Y-%m')
order by date_format(cdate, '%Y-%m')) as total
from tt;
复制

select t.code, t.cdate, t.total, 
sum(t.total) over (partition by t.code order by t.cdate) as g_total
from 
(select code, date_format(cdate, '%Y-%m') as cdate, 
ctotal,
row_number() over (partition by code, date_format(cdate, '%Y-%m') 
order by code, date_format(cdate, '%Y-%m')) as r_seq,
sum(ctotal) 
over (partition by code, date_format(cdate, '%Y-%m') 
order by code, date_format(cdate, '%Y-%m')) as total
from tt) t
where t.r_seq = 1;
复制