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 ...)