最近做项目真是忙到昏厥,抽个空写篇博客吧,保持良好的学习习惯~
今天要写的是工作中经常遇到的求累计值的问题。
从订单表中取出orderid,uid,time,ciiamount,数据格式如下:
现需要计算每个uid每天累计的成交金额。
SQL代码如下:
--汇总每个uid每天的订单成交金额
USE edw_htl;
DROP TABLE IF EXISTS tmp_linshi_cumulative_01;
CREATE TABLE tmp_linshi_cumulative_01 as
select uid
,time
,sum(ciiamount) as total_amount
from
( select orderid
,uid
,to_date(orderdate) as time
,ciiamount
from edw_htl.ordersnap
) a
group by uid,time
--求累计
--这里采用的思想是对uid、time和total_amount进行分组,然后在对小于等于的天数进行求和。代码如下:
select a.uid
,a.time
,max(a.amount_tatol) as amount_tatol
,sum(b.amount_tatol) as cumulative_amount
from edw_htl.tmp_linshi_cumulative_01 as a
join edw_htl.tmp_linshi_cumulative_01 as b
on a.uid=b.uid
where b.time <= a.time
group by a.uid,a.time
运行结果如下:
假如现在不需要统计到每个用户的情况,就看全量订单,求每天订单的总金额,然后按照天进行累计。此时可以用另外一种方法,代码如下:
select a.time
,sum(a.total_amount) as total
,sum(sum(a.total_amount)) over(order by a.time rows between UNBOUNDED PRECEDING AND CURRENT ROW) cumulative
from
( select time
,sum(ciiamount) as total_amount
from edw_htl.tmp_linshi_cumulative_01
group by time
) a
group by a.time
结果如下:
当然,这种不统计到每个用户的,上面一种方法也是可以实现的,但是上面到每个uid,此种方法就没有效果。