一、分析函数是什么？
分析函数是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;
它们得出的结果是相同的，都是：
请注意，这里我们用到了distinct 关键字，如果不用distinct，第2个查询将返回20行数据，即earnings表的每行记录都将返回一行总收入，因为不用distinct的含义是：针对每个打工者计算他/她所在的月份和地区的总收入。
SELECT earnmonth 月份,area 地区,
sum(personincome) OVER (PARTITION BY earnmonth,area) 总收入
FROM earnings;
在这个例子中，聚合函数是更好的选择，但在另外一些情形下，我们更应该使用分析函数。下面通过几个实例来介绍排序分析函数的用途。
问题：统计每个月份,不同地区工资最高的前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;
结果为：
我们在开窗函数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;
结果为：
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;
结果为：
5. rollup()函数:
按照月份,地区统计收入
--rollup函数:(分组统计之后,再按照月份做一个汇总)
--按照月份,统计每个地区的总收入
SELECT earnmonth,area,SUM(personincome) FROM earnings GROUP BY ROLLUP(earnmonth,area);
结果为:
6. cube()函数:
按照月份,地区进行收入总汇总
--cube函数:(分组统计之后,按照月份做一个汇总,再按照地区做一个汇总,最后再来一个收入的总汇总)
SELECT earnmonth,area,sum(personincome)
FROM earnings
GROUP BY cube(earnmonth,area) ORDER by earnmonth,area NULLS last;
结果为:
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;
结果为:
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;
结果为:
实验数据:
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;
