ORACLE —开窗函数

开窗函数：
（又名：分析函数，窗口函数，OLAP函数）

开窗函数：

将数据按照一定的规则分组，统一分析各组的某项情况，每行数据返回一行结果

OLTP：事务处理 OLAP：数据分析

开窗函数的语法形式:

分析函数名（）OVER（分析子句）
----OVER()是开窗函数的一个标志

分析函数名：

1.聚合类：SUM() AVG() MAX() MIN() COUNT() --功能与聚合函数上相同
2.排序类：ROW_NUMBER() RANK() DENSE_RANK()
3.偏移类：LAG() LEAD()

分析子句：

分组(PARTITION BY) 排序(ORDER BY) 窗口(ROWS)

tips：
分析子句不一定要要写，也不一定要全都写，分析子句的使用依照类型和函数的需求来定

聚合类：

用法（以SUM为例）：

SUM(COL_NAME)OVER([PARTITION] [ORDER] [ROWS])

聚合类开窗函数注意点：

1)分析函数名内必须包含需要分析的内容
2)分析子句没有硬性要求
3)采用默认窗口范围时，下一个相同值会被一并算入

排序类：

用法（以ROW_NUMBER为例）：

ROW_NUMBER()OVER([PARTITION] ORDER)

排序类开窗函数注意点：

1)分子函数名内不能包含任何内容
2)分析子句内必须添加ORDER BY，且不能指定窗口

偏移类：

用法（以LAG为例）
LAG(COL_NAME,OFFSET,DEFVAL)OVER():向前偏移N行取数

COL_NAME:要分析的字段
OFFSET:偏移量 --默认偏移一行
DEFVAL:默认返回值 --默认返回空null

偏移类开窗函数注意点：

1)分析函数名内必须包含要分析的内容，其他两项参数可以默认
2)分析子句内必须添加ORDER BY，且不能指定窗口
3)若不再有可供偏移的行，则返回默认值
4)偏移量不允许写负数
5)分析的字段与默认返回值数据类型要保持一致

例子：

/*查询每个部门工资最高的员工信息*/
SELECT *
  FROM EMP
 WHERE SAL IN(SELECT MAX(SAL)
                FROM EMP
              GROUP BY DEPTNO);



/*SELECT DISTINCT DEPTNO,MAX(SAL)OVER(PARTITION BY DEPTNO) FROM EMP;*/

SELECT * 
  FROM EMP 
 WHERE SAL IN (SELECT MAX(SAL) OVER(PARTITION BY DEPTNO) FROM EMP );



--统计各部门的工资总和，平均工资和各部门的人数
SELECT SUM(SAL) 工资总和,AVG(SAL) 平均工资,COUNT(1) 人数
  FROM EMP
GROUP BY DEPTNO;

SELECT DISTINCT SUM(SAL)OVER(PARTITION BY DEPTNO) 工资总和,
       AVG(SAL)OVER(PARTITION BY DEPTNO) 平均工资,
       COUNT(1)OVER(PARTITION BY DEPTNO) 人数
  FROM EMP;

查询员工工资与所在部门最低，最高的工资差


SELECT ENAME,SAL,DEPTNO,
       MIN(SAL)OVER(PARTITION BY DEPTNO) MIN,
       SAL - MIN(SAL)OVER(PARTITION BY DEPTNO) DC,
       MAX(SAL)OVER(PARTITION BY DEPTNO) MAX,
       ABS(SAL- MAX(SAL)OVER(PARTITION BY DEPTNO)) GC 
  FROM EMP ;

SELECT SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY EMPNO) 部门连续求和,
       SUM(SAL)OVER(PARTITION BY DEPTNO,ENAME ) 连续总和,--不按部门连续求总和
       SUM(SAL)OVER(ORDER BY DEPTNO ) 部门总和,
       SUM(SAL)OVER() 总和,
       100*round(SAL/SUM(SAL)OVER(PARTITION BY DEPTNO),2) "员工占部门总工资比(%)",
       100*round(SAL/SUM(SAL)OVER(),2) "员工占所有部门总工资比(%)"
  FROM EMP ;

查询员工工资与所在部门最低，最高的工资差


SELECT ENAME,SAL,DEPTNO,
       MIN(SAL)OVER(PARTITION BY DEPTNO) MIN,
       SAL - MIN(SAL)OVER(PARTITION BY DEPTNO) DC,
       round(MAX(SAL)OVER(PARTITION BY DEPTNO)) MAX,
       ABS(SAL- MAX(SAL)OVER(PARTITION BY DEPTNO)) GC FROM EMP ;

SELECT * FROM BUSINESS;

查询每天的营业额及整个月的营业总额
 
SELECT AMT AS RAMT,DATA_DT,
       SUM(AMT)OVER() YAMT 
                
  FROM BUSINESS;


查询每天的营业额及每周的营业总额

SELECT AMT AS DAYAMT,DATA_DT,
       SUM(AMT)OVER(PARTITION BY WEEK) AS WEEKAMT,WEEK
  FROM BUSINESS;
       
查询每天的营业额以及月每日营业额累计
SELECT AMT AS DAYAMT,DATA_DT,
       SUM(AMT)OVER( ORDER BY DATA_DT ) AS MDAYAMT
  FROM BUSINESS;

 SELECT DATA_DT,AMT,
   SUM(AMT)OVER(ORDER BY DATA_DT ROWS BETWEEN  UNBOUNDED PRECEDING AND CURRENT ROW)A
 FROM BUSINESS;

SELECT * FROM BUSINESS;

SELECT B.*,
       SUM(AMT)OVER(ORDER BY DATA_DT) 月累计营业额 
  FROM BUSINESS B;

查询每天的营业额以及周每日累计营业额
SELECT B.*,
       SUM(AMT) OVER(PARTITION BY WEEK ORDER BY DATA_DT) 周每日累计营额 
  FROM BUSINESS B;

ROW_NUMBER()分析函数
ROW_NUMBER()OVER ()

SELECT B.*,ROW_NUMBER()OVER( ORDER BY AMT) AA
  FROM BUSINESS B;


SELECT B.*,ROW_NUMBER()OVER(PARTITION BY WEEK ORDER BY AMT DESC) AA
  FROM BUSINESS B;

排序类开窗函数tips：
1.函数名内不能包含任何内容 ROW_NUMBER()括号里面不能添加东西
2.分析子句内必须添加 ORDER BY，并且不能指定任何窗口。



RANK()/DENSE_RANK()
RANK()OVER(PARTITION BY ...ORDER BY...)
RANK并列跳跃排序
OVER()在...条件之上
PARTITION BY 按哪个字段进行分组
ORDER BY 按哪个字段进行排序



查询每个部门工资最高的员工信息（RANK）
SELECT  *
  FROM (SELECT E.*,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RA
          FROM EMP E)  AA
 WHERE AA.RA=1;


DENSE_RANK()
并列不跳跃排序


SELECT B.*,DENSE_RANK()OVER(PARTITION BY WEEK ORDER BY AMT DESC) AA
  FROM BUSINESS B;
  
  
查询每个部门工资最高的员工信息（DENSE_RANK）
SELECT  *
  FROM (SELECT E.*,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RA
          FROM EMP E)  AA
 WHERE AA.RA=1;


SELECT  *
  FROM (SELECT E.*,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RA
          FROM EMP E)  AA
 WHERE AA.RA=1;
---只有3个，并列结果不显示


总结：
排序函数需要注意：
1.排序函数必须有over()子句
2.排序函数必须包含  order by
3.分组内是从1 开始排序的
row_number()排序方式：1，2，3
rank()排序方式 ：1，1，3
dense_rank()排序方式：1，1，2





LAG()

查询每天的营业额以及前一天的营业额

SELECT B.*,LAG(AMT,1)OVER(ORDER BY DATA_DT) QY
  FROM BUSINESS B;

查询个人工资比自己高一位，低一位的工资差额
SELECT ENAME,DEPTNO,SAL,
       LAG(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL) 低,
       ABS(SAL-LAG(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL)) 低差,
       SAL,
       LEAD(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL) 高,
       ABS(SAL-LEAD(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL)) 高差
       
  FROM EMP；
--查询个人工资比自己高一位、低一位的工资差额

SELECT T.EMPNO,T.DEPTNO,t.ename,T.SAL,
       LEAD(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL) 高一位,
       LAG(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL)  低一位,
       --LEAD(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL)-LAG(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL) 差值
       NVL(LEAD(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL)-T.SAL,0) 高一位差值,
       NVL(LAG(SAL,1,0)OVER(PARTITION BY DEPTNO ORDER BY SAL)-T.SAL,0) 低一位差值
  FROM EMP T;  

ROWS

上下求值范围说明：
UNBOUNDED:不受控制的，无线
PRECEDING:在...之前
FOLLOWING:在...之后
CURRENT:当前行

用法：




显示各部门员工的工资,并显示该部门的最高工资
SELECT ENAME,DEPTNO,SAL,
       LAST_VALUE(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) MAX_SAL
  FROM EMP;



SELECT E.*,
  FIRST_VALUE(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL DESC
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) MAX_SAL
  FROM EMP E;



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行到最后

总结：
SUM()OVER():对所有行求和
SUM()OVER(ORDER BY):从第一行到当前行，连续求和
SUM()OVER(PARTITION BY):同组内所有行求和
SUM()OVER(PARTITION BY…ORDERBY…):同组内从第一行到当前行，连需求和，只是范围限制在组内
tips:
如果没有order by 子句，求和就不是连续的

总结：

1.窗口子句不能单独出现，必须要有排序子句出现的情况下才能指定窗口范围
2.若出现排序子句，同时未指定窗口范围，默认的窗口范围是第一行到当前行；若未出现排序子句，同时未指定窗口范围，默认的窗口范围是第一行到最后一行
3.PARTITION BY 分组的范围 ROWS 统计分析的范围 分析范围不会超过分组范围