Oracle-分析函数（累计求和，排序等）

在Oracle中分析函数又称为开窗函数
分为以下两类：
第一类：是聚合分析函数，主要包含（sum，count，AVG、MAX、MIN等），主要是对内部分组的数值按照要求内部聚合处理；
第二类：是排序分析函数，主要包含（ROW_NUMBER、DENSE_RANK、RANK等），根据不同要求呈现不同的排序方式。
结构函数介绍：

SELECT table.column,
analysis_function() OVER ( [PARTITION BY 字符] [ORDER BY 字段 [rows]] ) as 统计值
 FROM table;
1
2
3

analysis_function表示分析函数，对应的有sum，count，AVG、MAX、MIN，ROW_NUMBER、DENSE_RANK、RANK等；
OVER：开窗函数名；
partition by :为分组字段；
order by :指定排序字段；
rows：指定数据窗口（即指定分析函数要操作的行数）

聚合分析函数

一、聚合分析函数与聚合函数的不同
聚合分析函数可以在查询语句中，每一组每一行进行聚合，聚合函数只能通过group by 整体聚合。
二、聚合分析函数
聚合分析函数：（）中需要填值

--组内计数
count() over(partition by ... order by ...)
--组内最大值
max() over(partition by ... order by ...)
--组内最小值
min() over(partition by ... order by ...)
--组内求和，可计算累计求和
sum() over(partition by ... order by ...)
--组内均值
avg() over(partition by ... order by ...)
--组内的第一个值
first_value() over(partition by ... order by ...)
--组内的最后一个值
last_value() over(partition by ... order by ...)
--Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。
lag() over(partition by ... order by ...)
--Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。
lead() over(partition by ... order by ...)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

举例：
sum() over(partition by … order by …) 累计求和

select 营销中心,事业部,产品编码,产品名称,品类,年份,月份,月,当月指标金额,sum(当月指标金额) over(partition by 营销中心, 事业部,产品编码,产品名称,品类,年份
ORDER BY 月
) 月累计指标,
sum(当月指标金额) over(partition by 营销中心,事业部,产品编码,年份
ORDER BY 产品编码
) 全年指标
FROM   TABLE;
1
2
3
4
5
6
7

排序分析函数

分为 rank，dense_rank,row_number 三种排序方式。
对应的函数结构如下所示：

rank ( ) over ( [query_partition_clause]order_by_clause )

--rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如：当第2名和第3名的利润相同时,rank的结果是1,2,2,4；
 select d,l,m,rank() over(partition by d order by l)  from aaa;
1
2

dense_rank ( ) over ([query_partition_clause] order_by_clause )

--dense_rank则不会跳过这个排名,结果是1,2,2,3
select d,l,m,dense_rank() over(partition by d order by l)  from aaa;
1
2

row_number ( ) over ( [query_partition_clause]order_by_clause )

--row_number哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4
select d,l,m,row_number() over(partition by d order by l)  from aaa;

1
2
3

通过上面的结构可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序

排序分析函数：（）中不填值

--重复排序（1，2，3，4）
row_number() over(partition by ... order by ...)
--重复跳过排序（1，2，2，4）
rank() over(partition by ... order by ...)
--重复不跳过排序（1，2，2，3）
dense_rank() over(partition by ... order by ...)

1
2
3
4
5
6
7