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(”,’北平’,”,’大魁’,11,30,11*30); insert into earnings values(”,’北平’,”,’大凯’,8,25,8*25); insert into earnings values(”,’北平’,”,’小东’,30,6.25,30*6.25); insert into earnings values(”,’北平’,”,’大亮’,16,8.25,16*8.25); insert into earnings values(”,’北平’,”,’贱敬’,30,11,30*11); insert into earnings values(”,’金陵’,”,’小玉’,15,12.25,15*12.25); insert into earnings values(”,’金陵’,”,’小凡’,27,16.67,27*16.67); insert into earnings values(”,’金陵’,”,’小妮’,7,33.33,7*33.33); insert into earnings values(”,’金陵’,”,’小俐’,0,18,0); insert into earnings values(”,’金陵’,”,’雪儿’,11,9.88,11*9.88); insert into earnings values(”,’北平’,”,’大魁’,0,30,0); insert into earnings values(”,’北平’,”,’大凯’,14,25,14*25); insert into earnings values(”,’北平’,”,’小东’,19,6.25,19*6.25); insert into earnings values(”,’北平’,”,’大亮’,7,8.25,7*8.25); insert into earnings values(”,’北平’,”,’贱敬’,21,11,21*11); insert into earnings values(”,’金陵’,”,’小玉’,6,12.25,6*12.25); insert into earnings values(”,’金陵’,”,’小凡’,17,16.67,17*16.67); insert into earnings values(”,’金陵’,”,’小妮’,27,33.33,27*33.33); insert into earnings values(”,’金陵’,”,’小俐’,16,18,16*18); insert into earnings values(”,’金陵’,”,’雪儿’,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;
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/46942.html
