orcle逐年递增累加、逐行累加(分析函数、开窗函数)
最近做数据统计碰到的一些问题。
需求:统计当年累计数量以及累计总和。
遇到这个问题我们可能会首先想到sum(),但是聚合函数是只返回一行的,而我们需要的结果是逐行累加,且需要多行,所以需要使用到开窗函数order by与分析函数partition by,开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化。
就拿我写的sql为例:
当年累计:按照年份分组
sum(a.installNum) over (partition by to_char(a.inst_date, 'yyyy') order by a.inst_date) as 'year_num'
累计总和
sum(a.installNum) over (order by a.inst_date) as 'total_num'
下面列一下开窗函数与分析函数搭配使用的情况:
1.有partition by有order by : 在partition by分组下,按照不同的order by 字段 实现递增汇总
2.有partition by无order by: 实现分组内所有数据的汇总
3.无partition by有order by : 直接按order by 字段实现递增汇总
4.无partition by无order by: 所有数据相加
当然,除了使用sum() over(),还有
count() over(partition by ... order by ...):求分组后的总数。
max() over(partition by ... order by ...):求分组后的最大值。
min() over(partition by ... order by ...):求分组后的最小值。
avg() over(partition by ... order by ...):求分组后的平均值。
lag() over(partition by ... order by ...):取出前n行数据。
lead() over(partition by ... order by ...):取出后n行数据。
ratio_to_report() over(partition by ... order by ...):Ratio_to_report() 括号中就是分子,over() 括号中就是分母。percent_rank() over(partition by ... order by ...)