Oracle-分析函数_总结

一、分析函数是什么？

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后基于组计算某种统计值，并且每一组的每一行都可以返回一个统计值。

说白了,分析函数就是 over([query_partition_clase] order_by_clause)。比如说，我采用sum求和，rank排序等等，根据什么来呢？over提供一个窗口,使用partition by进行分组，在组内使用order by进行排序。over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用

二、Oracle分析函数与聚合函数的区别：

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。

三、分析函数：

用于合计的函数:

• sum()函数;

• rollup()函数;

• cube()函数;

• grouping()函数;

• max() over;

• min() over;

• avg() over

用于排列的函数:

• rank() over 函数;

• dense_rank() over 函数;

• row_number() over 函数;

其他:

• lag() over;

• lead() over

---

1. sum()函数:

许多分析函数同时也是聚合函数，比如sum()函数，下面这样使用就是聚合函数。

--按照月份，统计每个地区的总收入

SELECT earnmonth 月份,area 地区,SUM(personincome) 总收入 FROM earnings GROUP BY earnmonth,area;

而这样使用就是分析函数:

SELECT DISTINCT earnmonth 月份,area 地区,

       sum(personincome) OVER (PARTITION BY earnmonth,area) 总收入

       FROM earnings;

它们得出的结果是相同的，都是：

image.png

请注意，这里我们用到了distinct 关键字，如果不用distinct，第2个查询将返回20行数据，即earnings表的每行记录都将返回一行总收入，因为不用distinct的含义是：针对每个打工者计算他/她所在的月份和地区的总收入。

SELECT earnmonth 月份,area 地区,

       sum(personincome) OVER (PARTITION BY earnmonth,area) 总收入

       FROM earnings;

image.png

在这个例子中，聚合函数是更好的选择，但在另外一些情形下，我们更应该使用分析函数。下面通过几个实例来介绍排序分析函数的用途。

问题：统计每个月份,不同地区工资最高的前3名。

2. rank()函数

    语法:rank() over([query_partition_clause]order_by_clause)

利用我们传统的聚合函数max可以方便地取出工资最高的一个员工,但是取出多个就无能为力了,同样，如果不分组我们可以通过排序取出工资最高的前3名，但无法实现对多个月份和地区的分组。而采用rank()分析函数，可以方便地实现我们的要求。

完整的语句如下:

SELECT t.earnmonth 月份,t.area 地区,t.sname 打工者姓名,t.personincome 收入,t.run 排名
FROM (
  SELECT earnmonth,area,sname,personincome,
  rank() OVER(PARTITION BY earnmonth,area ORDER BY personincome desc) run
  FROM earnings
) t WHERE t.run<=3;

结果为：

image.png

我们在开窗函数over()中使用earnmonth(月份)和area(地区)作为分组标志,并按照personincome(收入)倒序排列。

注意:RANK()函数有3组,分别是rank(), dense_rank(), row_number(),它们的区别是：

RANK()如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如：当第2名和第3名的利润相同时,rank的结果是1,2,2,4;而dense_rank()则不会跳过这个排名,结果是1,2,2,3;而row_number()哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4.

3. dense_rank()

    语法:  dense_rank() over([query_partition_clause] order_by_clause)

完整的语句如下：

SELECT t.earnmonth 月份,t.area 地区,t.sname 打工者姓名,t.personincome 收入,t.run 排名
FROM (
  SELECT earnmonth,area,sname,personincome,
  dense_rank() OVER(PARTITION BY earnmonth,area ORDER BY personincome desc) run
  FROM earnings
) t WHERE t.run<=3;

结果为：

image.png

4. row_number()

    语法:row_number() over([query_partition_clause]order_by_clause)

完整的语句如下：

SELECT t.earnmonth 月份,t.area 地区,t.sname 打工者姓名,t.personincome 收入,t.run 排名
FROM (
  SELECT earnmonth,area,sname,personincome,
  row_number() OVER(PARTITION BY earnmonth,area ORDER BY personincome desc) run
  FROM earnings
) t WHERE t.run<=3;

结果为：

image.png

5. rollup()函数:

按照月份,地区统计收入

--rollup函数:(分组统计之后,再按照月份做一个汇总)

--按照月份,统计每个地区的总收入
SELECT earnmonth,area,SUM(personincome) FROM earnings GROUP BY ROLLUP(earnmonth,area);

结果为:

image.png

6. cube()函数:

按照月份,地区进行收入总汇总

--cube函数:(分组统计之后,按照月份做一个汇总,再按照地区做一个汇总,最后再来一个收入的总汇总)

SELECT earnmonth,area,sum(personincome)
FROM earnings
GROUP BY cube(earnmonth,area) ORDER by earnmonth,area NULLS last;

结果为:

image.png

7. grouping()函数:

在以上例子中,是用rollup()和cube()函数都会对结果集产生null，这时候可用grouping函数来确认该记录是由哪个字段得出来的.

grouping函数用法，带一个参数，参数为字段名，如果当前行是由rollup或者cube汇总得来的，结果就返回1，反之返回0.

完整语句如下:

SELECT decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,
decode(grouping(area),1,'所有地区',area) 地区,SUM(personincome)
FROM earnings
GROUP BY cube(earnmonth,area) ORDER by earnmonth,area NULLS last;

结果为:

image.png

8. max(),min(),avg()和sum()函数综合运用

安装月份和地区统计打工收入最高值,最低值,平均值和总额.

SELECT DISTINCT earnmonth 月份,area 地区,
       MAX(personincome) over(PARTITION BY earnmonth,area) 最高值,
       min(personincome) OVER(PARTITION BY earnmonth,area) 最低值,
       AVG(personincome) over(PARTITION BY earnmonth,area) 平均值,
       sum(personincome) over(PARTITION BY earnmonth,area) 总额
       FROM earnings;

以上语句统计结果和如下语句使用group by的查询结果一样:

SELECT earnmonth 月份,area 地区,
       MAX(personincome) 最高值,
       min(personincome) 最低值,
       AVG(personincome) 平均值,
       sum(personincome) 总额
       FROM earnings GROUP BY earnmonth,area;

9. lag( )和lead( )函数

说明：Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙），原来没有分析函数的时候采用子查询方法，但是比较麻烦:

语法如下：

lag(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；

lead(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；

其中：

value_expression：可以是一个字段或一个内建函数。

offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，

default就是用于处理这样的信息，默认为空。

统计每个打工者上个月和下个月有没有赚钱(personincome大于0即为赚钱):

select earnmonth 本月,sname 打工者, 
       lag(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 上月, 
       lead(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 下月 
from earnings;

结果为:

image.png

---

---

---

实验数据:

1.建表

create table earnings -- 打工赚钱表 
( 
  earnmonth varchar2(6), -- 打工月份 
  area varchar2(20), -- 打工地区 
  sno varchar2(10), -- 打工者编号 
  sname varchar2(20), -- 打工者姓名 
  times int, -- 本月打工次数 
  singleincome number(10,2), -- 每次赚多少钱 
  personincome number(10,2) -- 当月总收入 
) ;

2.插入实验数据

insert into earnings values('200912','北平','511601','大魁',11,30,11*30); 
insert into earnings values('200912','北平','511602','大凯',8,25,8*25); 
insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25); 
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25); 
insert into earnings values('200912','北平','511605','贱敬',30,11,30*11); 
insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25); 
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67); 
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33); 
insert into earnings values('200912','金陵','511304','小俐',0,18,0); 
insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88); 
insert into earnings values('201001','北平','511601','大魁',0,30,0); 
insert into earnings values('201001','北平','511602','大凯',14,25,14*25); 
insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25); 
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25); 
insert into earnings values('201001','北平','511605','贱敬',21,11,21*11); 
insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25); 
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67); 
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33); 
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18); 
insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88); 
commit;

3.查询展示全表

SELECT * FROM earnings;