函数（开窗函数）

一.概述

开窗函数又叫分析函数

开窗函数用于定义一个窗口（这里的窗口是指运算将要操作的数据集合），它对一组值进行操作，
不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回对于一组值的处理结果。

两者区别：

聚合函数：将数据按照一定的规则分组，统一分析各组的某项情况，每个分组返回一行结果
开窗函数：将数据按照一定的规则分组，统一分析各组的某项情况，每行数据返回一行结果

二.语法
–不加窗口范围
函数名()OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

分析：
1.PARTITION BY COLUMN1->指明 按照COLUMN1分组
2.ORDER BY COLUMN2->按照COLUMN2列排序
3.分组、排序规则遵循分组排序查询的规则
4.OVER 为开窗函数的标志

三.分类
1.聚合类开窗函数(聚合函数和窗口组合)
SUM/MIN/MAX/AVG/COUNT(COLUMN1)OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2);

示例:
查询各个部门的员工编号，姓名，以及各部门的平均薪资
–表连接
SELECT EMPNO, ENAME, AVG_SAL，e.deptno
FROM EMP E,
(SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) D
WHERE E.DEPTNO = D.DEPTNO;

 SELECT TABLE_NAME FROM USER_TABLES;

–开窗函数
SELECT EMPNO,ENAME,JOB,DEPTNO,AVG(SAL)OVER(PARTITION BY DEPTNO) AVG_SAL FROM EMP;

2.排序类开窗函数(用于排序，实现组内排序)
–查询各个部门前两名的员工的工资

ROW_NUMBER() RANK() DENSE_RANK()

示例：
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO,ROW_NUMBER()OVER(ORDER BY SAL) AVG_SAL FROM EMP;–类似于ROWNUM

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY SAL) AVG_SAL FROM EMP;–类似于ROWNUM

四.聚合类开窗函数
1.函数名(参数)OVER()

SELECT EMPNO,ENAME,JOB,DEPTNO,SUM(SAL)OVER() AVG_SAL FROM EMP;–返回公司全体工资和
SELECT EMPNO,ENAME,JOB,DEPTNO,SUM()OVER() AVG_SAL FROM EMP;–报错 参数个数无效

–函数名后必须要含有分析的列

2. 函数名()OVER()

SELECT EMPNO,ENAME,JOB,DEPTNO,SUM(SAL)OVER() AVG_SAL FROM EMP;–返回公司全体工资和
–若OVER后括号内没有参数，，以整张表为一组分析

3.函数名()OVER(分组参数 )

SELECT EMPNO,ENAME,JOB,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO) FROM EMP;–返回各个部门工资和
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO,JOB) FROM EMP;–返回各个部门各工种工资和

SELECT EMPNO,ENAME,JOB,DEPTNO,SUM(SAL)OVER(PARTITION BY 1) FROM EMP;–返回工资和

–含有分组参数，按照分组列分组统计，返回函数的结果

4. 函数名()OVER(排序参数 )

SELECT EMPNO,ENAME,SAL,JOB,DEPTNO,SUM(SAL)OVER(ORDER BY SAL) FROM EMP;
SELECT EMPNO,ENAME,SAL,JOB,DEPTNO,COUNT(SAL)OVER(ORDER BY SAL) FROM EMP;

–含有排序参数，实现累加的效果,若遇到排序列相同的数据，会将所有相同的数据统计，然后在相同的数据后返回同一结果

5.函数名()OVER(分组参数 排序参数 )

SELECT EMPNO,ENAME,SAL,JOB,DEPTNO,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;
–实现组内累加，结果处理同上

6.作为条件使用
SELECT EMPNO, ENAME, SAL, JOB, DEPTNO, SUM(SAL) OVER(ORDER BY SAL)
FROM EMP
WHERE SUM(SAL) OVER(ORDER BY SAL) > 20000;–报错 不允许使用分组函数

SELECT *
FROM (SELECT EMPNO,
ENAME,
SAL,
JOB,
DEPTNO,
SUM(SAL) OVER(ORDER BY SAL) SMO
FROM EMP)
WHERE SMO > 20000;

–作为条件时，开窗函数需要和子查询联合使用

五.排序类开窗函数

1.三种函数的区分

ROW_NUMBER():将数据按照排序列进行排序，生成一组从1开始，递增的数组
RANK() :将数据按照排序列进行排序,遇到相同的数据，序号相同，下一条数据序号不连续
DENSE_RANK():将数据按照排序列进行排序,遇到相同的数据，序号相同，下一条数据序号连续

示例：
1).将员工表中的工资列排序
SELECT EMPNO,ENAME,SAL,ROW_NUMBER()OVER(ORDER BY SAL) FROM EMP;–一组从1开始，递增的数组

SELECT EMPNO,ENAME,SAL,RANK()OVER(ORDER BY SAL) FROM EMP;--排序 ，遇到相同的数据，序号相同，下一条数据序号不连续

SELECT EMPNO,ENAME,SAL,DENSE_RANK()OVER(ORDER BY SAL) FROM EMP;--排序 ，遇到相同的数据，序号相同，下一条数据序号连续

2.函数名(参数)OVER()

SELECT EMPNO,ENAME,JOB,DEPTNO,ROW_NUMBER(SAL)OVER() AVG_SAL FROM EMP;–缺失右括号
SELECT EMPNO,ENAME,JOB,DEPTNO,ROW_NUMBER()OVER() AVG_SAL FROM EMP;–报错 丢失ORDER BY表达式
SELECT EMPNO,ENAME,JOB,DEPTNO,ROW_NUMBER()OVER(ORDER BY SAL) AVG_SAL FROM EMP;–报错 丢失ORDER BY表达式

–函数名后括号不含有参数，排序类函数必须要有ORDER BY表达式

3.函数名()OVER(分组参数 排序参数)

SELECT EMPNO,ENAME,JOB,DEPTNO,ROW_NUMBER()OVER(ORDER BY SAL PARTITION BY DEPTNO) FROM EMP;
SELECT EMPNO,ENAME,JOB,DEPTNO,ROW_NUMBER()OVER( PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;

–含有排序参数 、分组参数，按照组内排序列排序
–分组参数在排序参数之前

4.作为条件使用
SELECT EMPNO, ENAME, SAL, JOB, DEPTNO, ROW_NUMBER() OVER(ORDER BY SAL)
FROM EMP
WHERE ROW_NUMBER() OVER(ORDER BY SAL) > 20000;–报错 函数在此禁用

SELECT *
FROM (SELECT EMPNO,
ENAME,
SAL,
JOB,
DEPTNO,
ROW_NUMBER() OVER(ORDER BY SAL) SMO
FROM EMP)
WHERE SMO > 5;

–作为条件时，开窗函数需要和子查询联合使用

六.两者注意点

聚合类开窗函数注意点：

1)分析函数名内必须包含需要分析的内容
2)分析子句没有硬性要求
3)采用默认窗口范围时，下一个相同值会被一并算入
4)不能写在WHERE 后面 起别名在WHERE后无效

排序类开窗函数注意点

1)分析函数不包含需要分析的内容
2)对于分析字句，窗口字句最少得有排序子句
3)ROW_NUMBER() RANK() DENSE_RANK()区别
4)不能直接写在WHERE 后，WHERE后起别名也无效

七.添加窗口范围

1.用于限制分析函数分析的数据条数

2.语法
函数名()OVER(分组子句 排序子句 窗口范围)

窗口范围：
ROWS BETWEEN [UNBOUNDED PRECEDING][N PRECEDING] AND [N FOLLOWING ][UNBOUNDED FOLLOWING][CURRENT ROW])

UNBOUNDED 没有限制，无边界的
PRECEDING 之前的
FOLLOWING 之后的
CURRENT 当前的
ROW 行

示例：
–不同的作用范围组合

ROWS BETWEEN N PRECEDING AND N FOLLOWING —当前行的前N行到当前行的后N行(包括当前行)
ROWS BETWEEN CURRENT ROW AND N FOLLOWING —当前行到当前行的后N行(包括当前行)
ROWS BETWEEN N PRECEDING AND CURRENT ROW —当前行的前N行到当前行(包括当前行)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW —从头到当前行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING —从当前行到最后
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING —从头到尾
ROWS BETWEEN UNBOUNDED PRECEDING AND N FOLLOWING —从头到当前行的后N行(包括当前行)
ROWS BETWEEN N PRECEDING AND UNBOUNDED FOLLOWING —当前行的前N行到末尾(包括当前行)

1).统计员工表中的累加工资和
SELECT EMPNO,ENAME,SAL,SUM(SAL)OVER(ORDER BY SAL) FROM EMP;

SELECT EMPNO,
ENAME,
SAL,
SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM EMP;

2).统计2020/08/25前后三天的销售总额

SELECT * FROM PRODUCT_ORDER;

SELECT *
FROM (SELECT SALE_DATE,
SUM(AMOUNT) OVER(ORDER BY SALE_DATE ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
FROM PRODUCT_ORDER)
WHERE SALE_DATE = TO_DATE(‘2020/08/25’, ‘YYYY/MM/DD’);