sum over函数

sum over函数,第1张

sum over函数用于统计累计求和值

select name,mon,amount ,sum(amount) over (partition by name order by mon from(select name,mon,sum(amount) as amount from order group by name,mon )t 

sum(tota_amount)的求和是针对后面over()窗口的求和,over中partition by name order by mon 针对name这一组按照月份排序,

rows between unbounded preceding and current row 限定了行是按照在当前行不限定的往前处理,通俗就是处理当前以及之前的所有行的sum,即3月时sum(amount)求的时1、2、3月的和,2月时sum(amount)求的是1、2月的和。unbounded意思无限的 preceding在之前的,current row当前行。

换其他数据库一个 sum() over (partition by ... )就解决了..

mysql比较麻烦, 可以试下

select a.Daytime,sum(new_Role)

from (select distinct DATE_FORMAT(createtime,'%Y-%m-%d') as Daytime)

from `actors` WHERE ( (`createtime` >= '2019-02-01 00:00:00') AND (`createtime` <= '2019-02-11 24:00:00') )

) a

left join (select * from `actors`

WHERE ( (`createtime` >= '2019-02-01 00:00:00') AND (`createtime` <= '2019-02-11 24:00:00') )

)b

on a.Daytime>=b.createtime

group by a.Daytime

order by 1

累加当日、昨日、明日:

select a,b,sum(c) over(partition by a order by b rows between 1 preceding and 1 following) from t

累加当日和昨天:

select a,b,sum(c) over(partition by a order by b rows between 1 preceding and current row) from t

累加历史:分区内当天及之前所有

select a,b,sum(c) over(partition by a order by b) from t

或者:

select a,b,sum(c) over(partition by a order by b rows between unbounded preceding and current row) from t

累加分区内所有:当天和之前之后所有

select a,b,sum(c) over(partition by a order by b rows between unbounded preceding and unbounded following) from t


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/8762079.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-20
下一篇 2023-04-20

发表评论

登录后才能评论

评论列表(0条)

保存