Oracle 窗口函数 目录Oracle 窗口函数1. 准备工作2. 认识 over() 函数2.1 分组、排序、窗口2.2 最值函数:first_value()与last_value()2.3 排序函数:rank()、dense_rank()与row_number()2.4 之前/后第N个值:lag() 与 lead()2.5 百分比:ratio_to_report()3. 小总结 Oracle 窗口函数 窗口函数是分析函数的一种,通常可以理解成over()函数 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,与普通的聚合函数不同,聚合函数用group by分组,每个分组返回一个统计值;而分析函数采用partition by 分组,并且每组每行都可以返回一个统计值。 构成格式:函数名①() over(partition by 分组的列名 order by 排序的列名 rows/range.. 子句) 函数名①一般来说有几种: 一种是聚合函数,像是sum、avg、count这种 一种是排序函数(序列函数),像是rank、dense_rank、row_number这种 一种是不好归类的,像是row_number这种行数范围的 1. 准备工作 练习SQL: 2. 认识 over() 函数 查询每个部门的总薪资,SQL1: 结果
2.1 分组、排序、窗口 分析函数包含三个分析子句:分组(partition by)、排序(order by)、窗口(rows)。 分组和排序和 group by、order by 没什么差别,不多说。 窗口子句是在进行排序统计时对数据作出的一些限制,窗口子句中有三个属性值: UNBOUNDED PRECEDING :第一行 CURRENT ROW :当前行 UNBOUNDED FOLLOWING :最后一行 窗口子句不能单独出现,必须有order by 子句时才能出现,如: 而出现order by 子句的时候,可以忽略不写窗口子句,这时候默认为当前组的第一行到当前行!即默认为:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 。 按部门查询累计薪资,SQL2: 结果:
【小总结】 当省略窗口子句时: 如果存在order by, 则默认的窗口是 unbounded preceding and current row. 如果同时省略order by, 则默认的窗口是 unbounded preceding and unbounded following. 如果省略分组,则把全部记录当成一个组: 如果存在order by 则默认窗口是unbounded preceding and current row 如果这时省略order by 则窗口默认为 unbounded preceding and unbounded following 2.2 最值函数:first_value()与last_value() 部门内最低和最高薪资: 执行结果:
first_value() 与 last_value() 用于第一个值和最后一个值 first_value()倒序排名也可获得 last_value() 的效果 2.3 排序函数:rank()、dense_rank()与row_number() 部门内员工薪资排序: 结果:
rank():值相同时排名相同,其后续排名跳跃不连续 dense_rank():值相同时排名相同,但后续排名连续不跳跃 row_number():值相同时排名不同 2.4 之前/后第N个值:lag() 与 lead() 查询当前行向下偏移n行对应的结果 函数 lag(arg1, arg2, arg3): arg1:要的行的表达式 arg2:偏移量,默认值为1 arg3:超出了分组的范围时返回的值,默认为null lead() 与lag() 效果相反。 结果为:
应用场景:在进行排名时,想知道前一名的积分,或者是还差多少分可排名上前一名 2.5 百分比:ratio_to_report() ratio_to_report(a) over(partition by b) :求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段。 【注意】:禁用order by 薪资占有的百分比: 结果:
3. 小总结 平时的开发中,不用分析函数也能达到我们想要的效果,只是有了分析函数会方便很多,所以当做一种辅助方式就好。 以上是日常用到较多的函数,后续持续更新。
2024最新激活全家桶教程,稳定运行到2099年,请移步至置顶文章:https://sigusoft.com/99576.html
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处:https://sigusoft.com/39993.html