前言:
此刻我们对“oracle开窗函数sum”都比较关切,小伙伴们都需要了解一些“oracle开窗函数sum”的相关文章。那么小编也在网上网罗了一些对于“oracle开窗函数sum””的相关内容,希望同学们能喜欢,咱们一起来了解一下吧!开窗函数
什么是开窗函数? 开窗函数是在MySQL8.0以后才新加的功能,因此,要想直接使用开窗函数,则mysql版本要8.0以上。其实开窗函数是在满足某种条件的记录集合上执行的特殊函数。开窗函数的本质还是聚合运算,只不过它更具灵活性,它对数据的每一行,都使用与该行相关的行进行计算并返回计算结果。接下来,我们介绍开窗函数的相关语法;具体语法如下:
开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [< 细分窗口>])
rows between xxx and xxx 可出现的关键词: unbounded :无限的 preceding :在…之前 following:在…之后 current row:当前行
-- over()中的关键字:
-- 行数在前2行和后4行
over(order by salary rows between 2 preceding and 4 following)
-- 下面三条语句等效(都是查所有数据):
-- 行数前无限到后无限
over(order by salary rows between unbounded preceding and unbounded following)
over(order by salary range between unbounded preceding and unbounded following)
-- 等效
over(partition by null)
row_number() over(partition by … order by …)
rank() over(partition by … order by …)
dense_rank() over(partition by … order by …)
count() over(partition by … order by …)
max() over(partition by … order by …)
min() over(partition by … order by …)
sum() over(partition by … order by …)
avg() over(partition by … order by …)
first_value() over(partition by … order by …)
last_value() over(partition by … order by …)
lag() over(partition by … order by …)
lead() over(partition by … order by …)
引入 oracle 数据库的 Scott 测试表数据
数据说明
1.emp员工表
2.dept部门表
3.salgrade工资等级表
查询所有员工的平均工资
select avg(sal) from emp;
使用开窗函数实现
select *,avg(sal) over () from emp;
从结果上看我们的窗口函数显示的是每一行,而普通的聚合函数则是只有一个结果
查询部门的平均工资
select deptno, avg(sal) avg_sal from emp group by deptno;
接下来使用开窗函数查询部门的平均工资
select *,avg(sal) over(partition by deptno) avg_sal from emp;
既然开窗函数都可以通过我们的一般聚合函数实现,那么为什么还要有开窗函数呢?其实,我们在前下面的一个案例,就只能用开窗函数实现了,其它的函数不可以实现的;
查询员工工资,并显示第一行到最后一行的工资汇总
select emp.*, sum(sal)over(order by sal rows between unbounded preceding and unbounded following )from emp;
查询员工工资,并显示第一行到当前行的工资汇总
select emp.*, sum(sal)over(order by sal rows between unbounded preceding and current row )from emp;
查询员工工资,并显示从当前行到最后一行的汇总
select emp.*, sum(sal)over(order by sal rows between current row and unbounded following)from emp;
查询员工工资,并显示从上一行到当前行的汇总
select e.*,sum(sal) over(order by sal rows between 1 preceding and current row ) from emp e;
查询员工工资,并显示上一行和下一行的汇总
select emp.*,sum(SAL) over ( order by SAL rows between 1 preceding and 1 following ) from emp;
查询员工工资,按照部门分组并显示每个组的工资总和
select emp.*,sum(sal) over (partition by emp.deptno order by sal rows between unbounded preceding and unbounded following )from emp;
查询员工工资,按照部门分组并显示每个组从第一行到当前行的工资总和
select emp.*,sum(sal)over(partition by deptno order by sal rows between unbounded preceding and current row )from emp;
普通的聚合函数用group by分组,每个分组返回一个统计值。 分析函数采用partition by分组,并且每组每行都可以返回一个统计值。
first_value()与last_value()
取首尾记录值
select emp.*,last_value(sal) over (order by sal) from emp;
select emp.*,first_value(sal) over (order by sal) from emp;
select emp.*,last_value(sal) over (order by sal rows between unbounded preceding and unbounded following ) from emp;
rank(),dense_rank()与row_number():求排序 以下三个分析函数用于计算一个行在一组有序行中的排位 序号从1开始 ROW_NUMBER 返回连续的排序,不论值是否相等 DENSE_RANK 具有相等值的行排序相同,序号是连续的 RANK 具有相等值的行排序相同,序数随后跳跃
select emp.*,row_number() over (order by sal desc) from emp;
select emp.*,rank() over (order by sal) from emp;
select emp.*,dense_rank() over (order by sal) from emp;
lag() 允许您回顾多行并从当前行访问行的数据
lead()从同一结果集中的当前行访问后续行的数据。
select emp.*,lag(SAL,1) over (order by sal) from emp;
select emp.*,lead(SAL,1) over (order by sal) from emp;