窗口函数

语法：SELECT 聚合函数（字段） OVER(分组,排序),字段 FROM 表

–窗口函数的分组 PARTITION BY

1
SELECT ENAME,SAL,MAX(SAL) OVER(PARTITION BY DEPTNO) "MAX_SAL" FROM EMP;

相当于字段，可以取别名。

聚合函数和窗口函数的==区别==：

1.聚合函数针对一组数据时只会返回一个值，窗口函数针对一组数据会返回相同数量的值。

2.聚合函数分组的关键词为GROUP BY，窗口函数分组的关键词为PARTITION BY

1
2
3
4
5
6
7
8
9
10
1.查询每个部门的最高薪资
SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;
2.查询每个部门最高工资员工的姓名和薪资
SELECT ENAME,SAL FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO) ;
SELECT ENAME,SAL FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO) GROUP BY ENAME,SAL ; 
SELECT * FROM EMP FOR UPDATE;
SELECT ENAME,SAL FROM EMP WHERE (SAL,DEPTNO) IN (SELECT MAX(SAL),DEPTNO FROM EMP GROUP BY DEPTNO) ;
3.查询每个员工的姓名和薪资，以及对应部门的最高薪资
SELECT ENAME,SAL,MAX(SAL) FROM EMP GROUP BY ENAME,SAL
SELECT ENAME,SAL , MAX_SAL  FROM EMP A JOIN (SELECT DEPTNO,MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO) B USING(DEPTNO);
1
2
3
4
5
6
7
SELECT ENAME,SAL,MIN(SAL)OVER (PARTITION BY DEPTNO) MAX_SAL FROM EMP;

SELECT ENAME,SAL,AVG(SAL)OVER (PARTITION BY DEPTNO) MAX_SAL FROM EMP;

SELECT ENAME,SAL,SUM(SAL)OVER (PARTITION BY DEPTNO) MAX_SAL FROM EMP;

SELECT ENAME,SAL,COUNT(SAL)OVER (PARTITION BY DEPTNO) MAX_SAL FROM EMP;

什么时候会用到窗口函数

  • 当只要求输出聚合函数的值，只需要用到分组（GROUP BY ）,如果这种时候用开窗函数，会得到14个值,有点多余。
  • 当每个人需要自己对应部门的聚合函数的值，如果只用GROUP BY ,是以个人为一个组，得不到自己想要的值，可以用表连接，但是用窗口函数会很方便
  1. 一些专用的窗口函数，随后会举例介绍其用法功能
  • 序号函数：row_number() / rank() / dense_rank()
  • 分布函数：percent_rank() / cume_dist()
  • 前后函数：lag() / lead()
  • 头尾函数：first_val() / last_val()
  • 其他函数：nth_value() / 用途：将分区中的有序数据分为n个桶，记录桶号。
  1. 原有的聚合函数也可用作窗口函数，如下
  • sum()，avg()，count()，max()，min()

窗口函数和普通聚合函数也很容易混淆，二者区别如下：

  • 聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，查询结果并不会改变记录条数，有几条记录执行完还是几条。
  • 普通聚合函数也可以用于窗口函数中，赋予它窗口函数的功能。

原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP BY，HAVING之后，在ORDER　BY，LIMIT，SELECT　DISTINCT之前。它执行时GROUP BY的聚合过程已经完成了，所以不会再产生数据聚合。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
查询每个部门的最高薪资
SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;

查询SMITH所在部门的最高薪资的员工信息
SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH';
SELECT MAX(SAL) FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');
SELECT * FROM EMP WHERE (DEPTNO,SAL) IN
(SELECT DEPTNO,MAX(SAL) FROM EMP WHERE DEPTNO IN(SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH') GROUP BY DEPTNO);--单行多列

查询每个部门最高工资的员工姓名和工资
SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO ;
SELECT ENAME ,SAL FROM EMP WHERE (DEPTNO,SAL) IN(SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO);--多行多列

查询每个员工的姓名和工资，以及对应部门的最高工资
SELECT ENAME,SAL FROM EMP;
SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;

SELECT * FROM (SELECT DEPTNO,ENAME,SAL FROM EMP) A JOIN (SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO) B USING(DEPTNO);

SELECT ENAME,SAL,MAX_SAL FROM EMP A JOIN (SELECT DEPTNO,MAX(SAL) MAX_SAL FROM EMP GROUP BY DEPTNO) B ON A.DEPTNO=B.DEPTNO;

1.聚合函数类

语法：SELECT 聚合函数(字段)OVER(分析子句) FROM 表;

分析子句里面有分组和排序
之前学的分组关键词：GROUP BY
之前学的排序关键词：ORDER BY
窗口函数的分组关键词：PARTITION BY
窗口函数的排序关键词：ORDER BY

写在SELECT后面，相当于一个字段，可以取别名

1
2
3
4
5
6
7
8
9
10
查询每个员工的姓名和工资，以及对应部门的最高工资
SELECT ENAME,SAL,DEPTNO,MAX(SAL)OVER(PARTITION BY DEPTNO) R FROM EMP;--对应每个部门的最高工资

SELECT ENAME,SAL,DEPTNO,MIN(SAL)OVER(PARTITION BY DEPTNO) FROM EMP;--对应每个部门的最低工资

SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO) FROM EMP;--对应每个部门的总计工资

SELECT ENAME,SAL,DEPTNO,AVG(SAL)OVER(PARTITION BY DEPTNO) FROM EMP;--对应每个部门的平均工资

SELECT ENAME,SAL,DEPTNO,COUNT(SAL)OVER(PARTITION BY DEPTNO) FROM EMP;--对应每个部门的人数

窗口函数和聚合函数的区别
1.当聚合函数针对一组数据进行分析时，只会返回一个值；窗口函数针对一组数据进行分析时，会返回相应数量的值；
2.聚合函数的分组关键词为GROUP BY ，窗口函数的分组关键词为PARTITION BY

窗口函数属于–OLAP(联机分析处理)，主要是进行复杂的分析操作，提供直观易懂的查询结果
窗口可以理解为一个范围，以PARTITION BY 为例，分组后的结果就可以理解为窗口
OLTP(联机事务处理)，主要进行简单的数据增删改查。

什么时候用两种函数？
1.当只要求输出聚合函数的值的时候，用聚合函数，因为会去重；这种时候用窗口函数会得到冗余数据

2.当要求 需要 对应部门的聚合函数的值，如果用GROUP BY ，去重后只会得到三个值，少于预期的数量，需要用表连接进行扩展，用窗口函数更方便

3.当需要按每组排序时，用窗口函数更方便 （查询每个部门工资前三的员工信息 拓展内容）UNION ALL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--窗口函数中使用ORDER BY ，可以理解为从第一行到当前行
SELECT ENAME,SAL,DEPTNO,MAX(SAL)OVER(ORDER BY SAL) FROM EMP;--对应从第一行到当前行的最高工资
SELECT ENAME,SAL,DEPTNO,MAX(SAL)OVER(ORDER BY SAL DESC) FROM EMP;--对应从第一行到当前行的最高工资

SELECT ENAME,SAL,DEPTNO,MIN(SAL)OVER(ORDER BY SAL) FROM EMP;--对应从第一行到当前行的最低工资
SELECT ENAME,SAL,DEPTNO,MIN(SAL)OVER(ORDER BY SAL DESC) FROM EMP;--对应从第一行到当前行的最低工资

SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(ORDER BY SAL) FROM EMP;--对应从第一行到当前行的累计工资

SELECT ENAME,SAL,DEPTNO,AVG(SAL)OVER(ORDER BY SAL) FROM EMP;----对应从第一行到当前行的平均工资

SELECT ENAME,SAL,DEPTNO,COUNT(SAL)OVER(ORDER BY SAL) FROM EMP;----对应从第一行到当前行的人数（会并列，前面会跳级）


窗口函数的分析子句中可以只写分组和排序中的一个，也可以全部都写
SELECT ENAME,SAL,DEPTNO,MAX(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;--对应部门的从第一行到当前行的最高工资
SELECT ENAME,SAL,DEPTNO,MAX(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) FROM EMP;--对应部门从第一行到当前行的最高工资

SELECT ENAME,SAL,DEPTNO,MIN(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;--对应部门的从第一行到当前行的最低工资
SELECT ENAME,SAL,DEPTNO,MIN(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) FROM EMP;--对应从第一行到当前行的最低工资

SELECT ENAME,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;--对应部门的从第一行到当前行的累计工资

SELECT ENAME,SAL,DEPTNO,AVG(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;----对应部门的从第一行到当前行的平均工资

SELECT ENAME,SAL,DEPTNO,COUNT(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) O_SAL FROM EMP;----对应部门的从第一行到当前行的人数（会并列，会跳级）

注意：
1.PARTITION BY 能放在ORDER BY 后面吗？
–不可以
2.当分析子句同时含有分组和排序时，同时包含他们两个的特点。
3.能不能在一个SELECT 语句中出现多个窗口函数呢？
–可以

2.排序类

1 ROW_NUMBER()OVER(分析子句)–得到从1开始的序列

与 ROWNUM 相似–自动生成从1开始的连续序号，出现ROWNUM则必须从1开始
–都是简单排序，重复时不并列，不跳级。1 2 3 4 5 6

1
2
3
4
5
6
查询工资从小到大不算并列在第四位到第八位的员工信息
 select * from(select a.*,rownum b from emp a order by sal) b where b between 4 and 8;--错误
select * from(select a.*,rownum 排序 from(select * from emp order by sal) a) where 排序 between 4 and 8;
SELECT * FROM (SELECT A.*,ROWNUM 排序 FROM (SELECT * FROM EMP ORDER BY SAL DESC)A) WHERE 排序 BETWEEN 4 AND 8; --是降序，应该为升序

SELECT * FROM (SELECT ENAME ,SAL,ROW_NUMBER()OVER(ORDER BY SAL) R FROM EMP) where R BETWEEN 4 AND 8 ;

2 RANK()OVER(分析子句)

–非紧密排序，重复时并列，并且后面跳级 1 2 3 3 5 6

3 DENSE_RANK()OVER(分析子句)

–紧密排序，重复时并列，不跳级 1 2 3 3 4 5
–排序类前面的括号内不能写东西，不能省略括号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT ENAME,SAL,DEPTNO,RANK()OVER( ORDER BY SAL) FROM EMP;--1 2 3 4 4 6

SELECT ENAME,SAL,DEPTNO,RANK()OVER( ORDER BY SAL DESC) FROM EMP;
SELECT ENAME,SAL,DEPTNO,RANK()OVER( ORDER BY SAL DESC) FROM EMP ORDER BY SAL DESC;

SELECT ENAME,SAL,DEPTNO,RANK()OVER( ORDER BY SAL ) FROM EMP ORDER BY SAL DESC;
--首先运行SELECT的窗口函数的ORDER BY，其中工资升序生成序号，在运行ORDER BY ，工资再次排序成降序，再输出结果

SELECT A.* ,ROWNUM FROM EMP A  ORDER BY SAL DESC ;--用ROWNUM会打乱顺序，没有窗口函数智能

SELECT ENAME,SAL,DEPTNO,DENSE_RANK()OVER( ORDER BY SAL) FROM EMP;--1 2 3  4 4 5

SELECT ENAME,SAL,DEPTNO,COUNT(SAL)OVER( ORDER BY SAL DESC) FROM EMP;-- 1 2 3 5 5 6
SELECT ENAME,SAL,DEPTNO,COUNT(SAL)OVER( ORDER BY SAL ) FROM EMP ORDER BY SAL DESC;

问：查询每个部门薪资排在第二名的员工信息（要求考虑重复数据，不跳级）
SELECT * FROM (SELECT A.*,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R FROM EMP A) WHERE R = 2;

查询每个部门薪资排在第一名的员工信息
SELECT * FROM (SELECT A.*,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R FROM EMP A) WHERE R = 1;--四条数据

SELECT * FROM (SELECT A.*,RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R FROM EMP A) WHERE R = 1;--四条数据

SELECT * FROM (SELECT A.*,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R FROM EMP A) WHERE R = 1;--三条数据

–ROW_NUMBER重复时不并列，所以能输出指定的结果
–RANK和DENSE_RANK重复时并列，所以输出的结果可能超过预期

表连接的内容：
条件写在WHERE和ON后面的区别
1.名字不一样
2.所属不一样，ON跟在FROM子句，WHERE属于WHERE子句
3.SELECT * FROM EMP A LEFT JOIN DEPT B ON A.DEPTNO = B.DEPTNO AND A.DEPTNO =10;
–条件写在ON后面，是先对表进行数据筛查，再以筛查结果作为一个新的数据源进行表连接
–条件写在WHERE后面，先进行表连接，再进行条件筛查

3.偏移类

LEAD(字段,偏移的行数,填充的内容(DEFAULT))OVER(分析子句) –LEAD 向上偏移 –LEAD 引领
LAG(字段,偏移的行数,填充的内容(DEFAULT))OVER(分析子句) –LAG 向下偏移 –LAG 退后

1
2
3
SELECT SAL,LEAD(SAL,3,0)OVER(ORDER BY SAL) FROM EMP;--整体向上偏移三行，没有的部分用‘填充的内容’填充

SELECT SAL,LAG(SAL,3,100)OVER(ORDER BY SAL) FROM EMP;--整体向下偏移三行，没有的部分用‘填充的内容’填充

–1.填充的内容的数据类型必须和字段保持一致；
–2.可以认为是整体向上/向下移动，没有的部分用‘填充的内容’填充
–3.填充的内容可以不写，默认填充空值

FIRST_VALUE(字段)OVER(分析子句) –某个窗口范围内的第一个值
LAST_VALUE(字段)OVER(分析子句) –某个窗口范围内的最后一个值

1
2
3
4
SELECT SAL,FIRST_VALUE(SAL)OVER(ORDER BY SAL) FROM EMP;--升序排列后，整个表从第一行到当前行的第一个值

SELECT SAL,LAST_VALUE(SAL)OVER(ORDER BY SAL) FROM EMP;--升序排列后，整个表从第一行到当前行的最后一个值
--ORDER BY 遵循从第一行到当前行

4.窗口子句

又叫WINDOW子句

语法：窗口函数()OVER(分析子句 ROWS BETWEEN … AND …)

–这里的窗口函数可以写哪些？

关键词：
CURRENT ROW –当前行
数字 PRECEDING –当前行的前‘数字’行
数字 FOLLOWING –当前行的后‘数字行
UNBOUNDED PRECEDING –第一行 –UNBOUNDED 无限制的
UNBOUNDED FOLLOWING –最后一行

1.（窗口函数的默认值）从第一行到当前行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

1
2
3
SELECT ENAME,SAL,MAX(SAL)OVER(ORDER BY SAL ) FROM EMP;

SELECT ENAME,SAL,MAX(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM EMP;--与前面的结果一样

2.第一行到最后一行
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

1
2
3
4
SELECT ENAME,SAL,MAX(SAL)OVER(ORDER BY SAL ) FROM EMP;

SELECT ENAME,SAL,MAX(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM EMP;
--每个组在第一行到最后一行这个范围中选最大值

3.从当前行到当前行的后2行
ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING

1
2
3
4
5
6
7
SELECT ENAME,SAL,MAX(SAL)OVER(ORDER BY SAL ) FROM EMP;

SELECT ENAME,SAL,MAX(SAL)OVER( ORDER BY SAL ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) FROM EMP;
--从当前行到当前行的后2行中找最大值

SELECT ENAME,SAL,MIN(SAL)OVER( ORDER BY SAL ROWS BETWEEN 1PRECEDING AND 2 FOLLOWING) FROM EMP;
--从当前行的前1行到当前行的后2行中找最小值
1
2
3
4
思考题：按照工资升序排序，输出姓名，工资，以及每人工资前一行和工资后两行；（两种方法）
SELECT ENAME,SAL,MAX(SAL)OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),MAX(SAL)OVER(ORDER BY SAL ROWS BETWEEN 2 FOLLOWING AND  2 FOLLOWING) FROM EMP; 

SELECT ENAME,SAL,LAG(SAL,1)OVER(ORDER BY SAL),LEAD(SAL,2)OVER(ORDER BY SAL) FROM EMP;