【SQL】累计统计运算

表名：table_test_v1

字段说明：id-唯一标识；date_id-日期；earnings-收益

数据如下：

需求：计算往年累计收益

方法一：

select b.date_id as '年份',sum(a.earnings) as '累计收益' 
from table_test_v1 a,table_test_v1 b 
where a.date_id<=b.date_id 
group by b.date_id 
order by b.date_id;

方法二：

select s1.date_id as '年份'
,(select sum(s2.earnings) from table_test_v1 s2 where s2.date_id<=s1.date_id) as '累计收益' 
from table_test_v1 s1;

方法三：（有扩展，使用了关键字unbounded）

select 
    date_id as '年份',earnings as '收益'
    ,sum(earnings) over(order by date_id rows unbounded preceding) as '累计收益'
    ,sum(earnings) over(order by date_id rows 3 preceding) as '累加指定的行数'
from dbo.table_test_v1