目录
定义
参数含义
over
partition by
order by
rank()/dense_rank()
rank()
dense_rank()
测试一
方法一
方法二
方法三
first_value()
last_value()
测试二
方法一
方法二
测试三
lead(col_name,x,y)
lag()
定义
用于计算给予组的某种聚合值，和聚合函数不同之处是：对于每个组返回多个行，而聚合函数对于每个组只返回一行，开窗函数制定了分析函数工作的数据窗口大小，它可以随着行的变化而变化
参数含义
over
表示在什么条件之上
partition by
表示按什么划分
order by
表示排列顺序(rank()/dense_rank()时，必须要带order by否则报非法)
rank()/dense_rank()
分级
rank()
跳跃排序，如果有两个第一名时，再后面就是第三名
| SQL> select ename,deptno,sal,rank() over(partition by deptno order by sal) a from emp;
ENAME DEPTNO SAL A
---------- ------- ----- ----------
MILLER 10 1300 1
CLARK 10 2450 2
KING 10 5000 3
SMITH 20 800 1
ADAMS 20 1100 2
JONES 20 2975 3
SCOTT 20 3000 4
FORD 20 3000 4
JAMES 30 950 1
MARTIN 30 1250 2
WARD 30 1250 2
TURNER 30 1500 4
ALLEN 30 1600 5
BLAKE 30 2850 6 14 rows selected. |
dense_rank()
连续排序，如果有两个第一名，那么后面还是第二名
| SQL> select ename,deptno,sal,dense_rank() over(partition by deptno order by sal) a from emp;
ENAME DEPTNO SAL A
---------- ------- ----- ----------
MILLER 10 1300 1
CLARK 10 2450 2
KING 10 5000 3
SMITH 20 800 1
ADAMS 20 1100 2
JONES 20 2975 3
SCOTT 20 3000 4
FORD 20 3000 4
JAMES 30 950 1
MARTIN 30 1250 2
WARD 30 1250 2
TURNER 30 1500 3
ALLEN 30 1600 4
BLAKE 30 2850 5
14 rows selected. |
测试一
想要得到emp表中每个部门工资最高的员工姓名、职位、工资以及部门编号
方法一
可以使max()
| SQL>
select ename 姓名,job 职位,sal 工资,deptno 部门 from (select ename, job, e.sal, e.deptno from emp e, (select deptno, max(sal) sal from emp group by deptno) t where e.deptno = t.deptno and e.sal = t.sal) order by 部门;
姓名 职位 工资 部门
---------- ---------- ---------- -----
KING PRESIDENT 5000 10
SCOTT ANALYST 3000 20
FORD ANALYST 3000 20
BLAKE MANAGER 2850 30 |
方法二
使用开窗函数
| SQL> select ename 姓名,job 职位,sal 工资,deptno 部门 from ( select ename, job,sal, deptno, dense_rank() over(partition by deptno order by sal desc) r from emp ) where r=1;
姓名 职位 工资 部门
---------- ---------- ---------- -----
KING PRESIDENT 5000 10
SCOTT ANALYST 3000 20
FORD ANALYST 3000 20
BLAKE MANAGER 2850 30 |
这句话的意思是按部门划分的基础上，把工资进行降序排列分级，级别从小到大排列其中最小值必定为一
单独执行from后面的查询可以看出具体是怎么分级
| SQL> select ename,job,sal,deptno,dense_rank() over(partition by deptno order by sal desc) r from emp;
ENAME JOB SAL DEPTNO R
---------- ----------- ----- ------- ----------
KING PRESIDENT 5000 10 1
CLARK MANAGER 2450 10 2
MILLER CLERK 1300 10 3
SCOTT ANALYST 3000 20 1
FORD ANALYST 3000 20 1
JONES MANAGER 2975 20 2
ADAMS CLERK 1100 20 3
SMITH CLERK 800 20 4
BLAKE MANAGER 2850 30 1
ALLEN SALESMAN 1600 30 2
TURNER SALESMAN 1500 30 3
MARTIN SALESMAN 1250 30 4
WARD SALESMAN 1250 30 4
JAMES CLERK 950 30 5
14 rows selected. |
方法三
利用first_value()/last_value()配合partition by
first_value()
记录结果最前面的值
last_value()
记录结果最后面的值
| SQL>
select ename 姓名,job 职位,deptno 部门,max_sal 工资 from ( select ename,job,sal,deptno,first_value(sal) over(partition by deptno order by sal desc) max_sal from emp) where max_sal=sal;
姓名 职位 部门 工资
---------- ------------ ----- -----
KING PRESIDENT 10 5000
SCOTT ANALYST 20 3000
FORD ANALYST 20 3000
BLAKE MANAGER 30 2850 |
单独使用firet_value()
| SQL> select ename,job,sal,deptno,first_value(sal) over(partition by deptno order by sal desc) max_sal from emp;
ENAME JOB SAL DEPTNO MAX_SAL
---------- ------------ ---------- ---------- ----------
KING PRESIDENT 5000 10 5000
CLARK MANAGER 2450 10 5000
MILLER CLERK 1300 10 5000
SCOTT ANALYST 3000 20 3000
FORD ANALYST 3000 20 3000
JONES MANAGER 2975 20 3000
ADAMS CLERK 1100 20 3000
SMITH CLERK 800 20 3000
BLAKE MANAGER 2850 30 2850
ALLEN SALESMAN 1600 30 2850
TURNER SALESMAN 1500 30 2850
MARTIN SALESMAN 1250 30 2850
WARD SALESMAN 1250 30 2850
JAMES CLERK 950 30 2850
14 rows selected. |
单独使用last_value()
测试二
想要得到emp表中每个员工的姓名，部门编号，工资，与低工资的差额，与最高工资差额
方法一
依旧使用max()/min()
| SQL>
select e.ename 姓名,e.job 职位,e.sal 工资,e.sal-f.min 最低差额,f.max-e.sal 最高差额,e.deptno 部门 from emp e, (select deptno,min(sal) min,max(sal) max from emp group by deptno) f
where e.deptno=f.deptno
order by e.deptno,e.sal;
姓名 职位 工资 最低差额 最高差额 部门
---------- ---------- ---------- ---------- ---------- -----
MILLER CLERK 1300 0 3700 10
CLARK MANAGER 2450 1150 2550 10
KING PRESIDENT 5000 3700 0 10
SMITH CLERK 800 0 2200 20
ADAMS CLERK 1100 300 1900 20
JONES MANAGER 2975 2175 25 20
SCOTT ANALYST 3000 2200 0 20
FORD ANALYST 3000 2200 0 20
JAMES CLERK 950 0 1900 30
WARD SALESMAN 1250 300 1600 30
MARTIN SALESMAN 1250 300 1600 30
TURNER SALESMAN 1500 550 1350 30
ALLEN SALESMAN 1600 650 1250 30
BLAKE MANAGER 2850 1900 0 30
14 rows selected. |
方法二
使用开窗函数
| SQL> select
ename 姓名,
job 职位,
nvl(sal-min(sal) over(partition by deptno),0) 最低差额,
nvl(max(sal) over(partition by deptno)-sal,0) 最高差额,
deptno 部门
from emp
order by deptno,sal;
姓名 职位 最低差额 最高差额 部门
---------- ---------- ---------- ---------- -----
MILLER CLERK 0 3700 10
CLARK MANAGER 1150 2550 10
KING PRESIDENT 3700 0 10
SMITH CLERK 0 2200 20
ADAMS CLERK 300 1900 20
JONES MANAGER 2175 25 20
SCOTT ANALYST 2200 0 20
FORD ANALYST 2200 0 20
JAMES CLERK 0 1900 30
MARTIN SALESMAN 300 1600 30
WARD SALESMAN 300 1600 30
TURNER SALESMAN 550 1350 30
ALLEN SALESMAN 650 1250 30
BLAKE MANAGER 1900 0 30
14 rows selected. |
测试三
查询emp表中员工姓名、职位、比自己工资低一名的差额，比自己工资高一名的差额、部门号
这种需求单纯用group by无法实现
不过可以使用开窗函数配合分析函数lead()/lag()解决
lead(col_name,x,y)
表示当前记录后面的第x行记录中col_name列的值,没有则默认值为y,如果不带x,y参数侧查找当前记录后面第一行col_name列中的值,没有则默认为null
lag()
表示当前记录前面的第x行记录中col_name列的值,没有则默认值为y,如果不带x,y参数侧查找当前记录前面第一行col_name列中的值,没有则默认为null
| SQL>
select
ename 姓名,
job 职位,sal 工资,
deptno 部门,
nvl(lead(sal) over(partition by deptno order by sal)- sal,0) 前一位高查额,
nvl(sal - lag(sal) over(partition by deptno order by sal),0) 后一位低差额
from emp;
姓名 职位 工资 部门 前一位高查额 后一位低差额
---------- ------------ ----- ----- ------------ ------------
MILLER CLERK 1300 10 1150 0
CLARK MANAGER 2450 10 2550 1150
KING PRESIDENT 5000 10 0 2550
SMITH CLERK 800 20 300 0
ADAMS CLERK 1100 20 1875 300
JONES MANAGER 2975 20 25 1875
SCOTT ANALYST 3000 20 0 25
FORD ANALYST 3000 20 0 0
JAMES CLERK 950 30 300 0
MARTIN SALESMAN 1250 30 0 300
WARD SALESMAN 1250 30 250 0
TURNER SALESMAN 1500 30 100 250
ALLEN SALESMAN 1600 30 1250 100
BLAKE MANAGER 2850 30 0 1250
14 rows selected. |