sql之累计求和

需求：求出每个域名的当月点击总量和累计点击总量
样本数据：

domain	time	traffic(T)
gifshow.com	2019/01/01	5
yy.com	2019/01/01	4
huya.com	2019/01/01	1
gifshow.com	2019/01/20	6
gifshow.com	2019/02/01	8
yy.com	2019/01/20	5
gifshow.com	2019/02/02	7

解答思路1：

1. 先求出每个域名的每月点击总量和。
2. 将上一步生成的结果表进行自关联，做笛卡尔积。
3. 从上一步的结果中，进行分组查询，分组的字段是a.domain a.month
   求月累计值： 将b.month <= a.month的所有b.salary求和即是月累计值。

select A.domain, A.month, max(A.traffics) as traffics, sum(B.traffics) as totals from 
(select t.domain, t.month, cast(sum(t.traffic) as Int) as traffics from (select domain, concat(substr(time,0,4),substr(time,6,2)) as month, traffic from user_flow)t group by domain, month) A 
inner join 
(select t.domain, t.month,cast(sum(t.traffic) as Int) as traffics from (select domain, concat(substr(time,0,4),substr(time,6,2)) as month, traffic from user_flow)t group by domain, month) B
on A.domain=B.domain
where B.month <= A.month
group by A.domain,A.month
order by A.domain,A.month;

解答思路2：（一般采用这种方式）

select 
    t.domain,
    t.month,
    sum(t.traffic) as traffics,
    SUM(sum(t.traffic)) OVER (PARTITION BY domain ORDER BY month) as totals 
from 
    (select domain, from_unixtime(unix_timestamp(time,'yyyy/MM/dd'),'yyyy-MM') as month, traffic from user_flow) t 
group by domain, month;

输出：

domain	time	traffics	totals
gifshow.com	2019-01	11	11
gifshow.com	2019-02	15	26
yy.com	2019-01	9	9
huya.com	2019-01	1	1