oracle分析函数中的开窗函数
分析函数中的开窗函数
--分析函数和聚合函数的区别
----普通的聚合函数用group by分组，每个分组返回一个统计值，
----分析函数采用partition by分组，并且每组每行都可以返回一个统计值。
--分析函数语法结构
FUNCTION_NAME(<参数>,…)
OVER ( <PARTITION BY 表达式,…>
<ORDER BY 表达式 <ASC DESC>
<NULLS FIRST NULLS LAST>>
<WINDOWING子句> )
--PARTITION子句 ORDER BY子句 WINDOWING子句 缺省时相当于RANGE UNBOUNDED PRECEDING。
分析函数带有一个开窗函数over()，包含三个分析子句:
分组(partition by)
排序(order by)
窗口(rows)
数据用例：
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) -- 当月总收入
)
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;
1.连续求和分析函数 sum(…) over(…)
功能：发现，如果分组或排序中包含sum（）中的关键字段，就会变成连续求和，否则就是求总和
用法：
--sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
sum(personincome) over (partition by earnmonth,area order by personincome ) 连续收入
from earnings;
--sum(sal) over (partition by deptno)
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
sum(personincome) over (partition by earnmonth,area ) 按月份地区求总和
from earnings;
--sum(sal) over (order by deptno,ename)
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
sum(personincome) over (order by earnmonth,area ) 按月份地区求总和
from earnings;
--sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
sum(personincome) over () 总和
from earnings;
2.连续求最大分析函数 max(…) over(…)
用法：
--max(sal) over (partition by deptno order by ename)
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over (partition by earnmonth,area) 分组取最大收入
from earnings;
--max(sal) over (order by deptno,ename)
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over (order by earnmonth,area ) 按月份地区求总和
from earnings;
--max(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over () 求最大
from earnings;
3.连续求最小分析函数 min(…) over(…)
用法：
--max(sal) over (partition by deptno order by ename)
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over (partition by earnmonth,area) 分组取最大收入
from earnings;
--max(sal) over (order by deptno,ename)
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over (order by earnmonth,area ) 按月份地区求总和
from earnings;
--max(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over () 求最大
from earnings;
4.连续求平均分析函数 avg(…) over(…)
用法：
--max(sal) over (partition by deptno order by ename)
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over (partition by earnmonth,area) 分组取最大收入
from earnings;
--max(sal) over (order by deptno,ename)
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over (order by earnmonth,area ) 按月份地区求总和
from earnings;
--max(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
max(personincome) over () 求最大
from earnings;
--分析函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
5.rank() over开窗函数
用法：
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
rank() over (partition by earnmonth,area order by personincome desc) 排名
from earnings order by 排名 ;
--dense_rank在并列关系是，相关等级不会跳过。rank则跳过。
rank()中2个并列第一，接下来就是第三名，第二名被跳过了；
--dense_rank()是连续排序，有两个第一名时仍然跟着第二名。
6.dense_rank () over开窗函数
用法：
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
dense_rank() over (partition by earnmonth,area order by personincome desc) 排名
from earnings;
--表示根据earnmonth,area分组，在分组内部根据 personincome排序，
--而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
7.row_number() over开窗函数 返回的主要是“行”的信息，并没有排名.
功能：用于取前几名，或者最后几名等
用法：
select earnmonth 月份,area 地区,sname 打工者, personincome 收入,
row_number() over (partition by earnmonth,area order by personincome desc) 排名
from earnings;
8.lag和lead函数是偏移量函数，可以查出一个字段的上一个值或者下一个值，配合over来使用
功能：
lead函数，这个函数是向上偏移：LEAD(EXP_STR,OFFSET,DEFVAL) OVER()
lag函数是向下偏移一位：LAG(EXP_STR,OFFSET,DEFVAL) OVER()
EXP_STR要取的列
OFFSET取偏移后的第几行数据
DEFVAL:没有符合条件的默认值
用法：
SELECT T.ID
,LAG(T.NAME) OVER(ORDER BY ID) MIN_V_01
,LAG(T.NAME,1,0) OVER(ORDER BY ID) MAX_V
,T.NAME
,LEAD(T.NAME,1,0) OVER(ORDER BY ID) MIN_V
FROM (
SELECT 1 ID ,'1AA' NAME FROM DUAL
UNION ALL
SELECT 2 ,'2AA' FROM DUAL
UNION ALL
SELECT 3 ,'3AA' FROM DUAL
UNION ALL
SELECT 4 ,'4AA' FROM DUAL
UNION ALL
SELECT 5 ,'5AA' FROM DUAL
UNION ALL
SELECT 6 ,'6AA' FROM DUAL) T;
