开窗函数与聚合函数的区别:
聚合函数是将多条记录聚合为一条;而开窗函数是每条记录都会执行,有几条记录执行完还是几条
聚合函数也可以用于开窗函数中。
当over中没有指定分区、排序和滑动窗口,默认表中所有记录为一个分区,计算的是分区内的所有行
所有员工的平均工资:
select avg(sal) as avg_sal from emp; -- 聚合函数
select *,avg(sal) over() as avg_sal from emp; -- 开窗函数
当over中,指定了分区,没有指定排序和滑动窗口,默认计算的是分区内的所有行
各部门员工的平均工资
select deptno,avg(sal) as avg_sal
from emp
group by deptno; -- 聚合函数
select *,avg(sal) over(partition by deptno) as avg_sal from emp; -- 开窗函数
当over中指定了排序,但是没有指定滑动窗口,默认计算的是分区内的第一行到当前行
查询各部门按入职时间顺序的累计工资
select *, sum(sal) over(partition by deptno order by hiredate) as sum_sal from emp;
当over中指定了滑动窗口,默认计算的是分区内滑动窗口范围内的行
查询各部门按入职时间的前一行到后一行的员工平均工资
select *, avg(sal) over(partition by deptno
order by hiredate
rows between 1 preceding and 1 following) as avg_sal
from emp;
序号函数:静态函数
查询所有员工的工资排名
select *,row_number() over(order by sal desc) as 工资排名 from emp;
-- row_number():显示分区中不重复不间断的序号
-- dense_rank():显示分区中重复不间断的序号
-- rank():显示分区中重复间断的序号
查询各部门员工的工资排名
select *,
row_number() over(partition by deptno order by sal desc) as 工资排名1,
dense_rank() over(partition by deptno order by sal desc) as 工资排名2,
rank() over(partition by deptno order by sal desc) as 工资排名3
from emp;