分析函数

分析函数

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数
它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值

分析函数的语法格式

FUNCTION_NAME(<参数>,...) OVER (<PARTITION BY 表达式,...> <ORDER BY 表达式 <ASC DESC>)

分析函数的原始状态 OVER()
分析函数一级形态：结合聚合函数使用 聚合函数()OVER()

--查出员工的工资高于公司平均工资的员工
SELECT *
 FROM (SELECT E.*,AVG(SAL)OVER() 平均工资
        FROM EMP E)
 WHERE E.SAL > 平均工资;

--查出工资最高和最低的员工的信息
SELECT *
 FROM (SELECT E.*,MAX(E.SAL)OVER() MAXSAL,MIN(E.SAL)OVER() MINSAL
        FROM EMP E)
 WHERE SAL = MAXSAL
 OR SAL = MINSAL;

分析函数二级形态：聚合函数()OVER(PARTITION BY 分组字段1，分组字段2…)

--查出工资高于员工所在部门平均工资员工
SELECT *
 FROM (SELECT E.*,AVG(E.SAL)OVER(PARTITION BY E.DEPTNO) 部门平均工资
        FROM EMP E)
 WHERE SAL > 部门平均工资;

分析函数的三级形态：聚合函数()OVER(ORDER BY 排序&累计字段1，排序&累计字段2)
分析函数中 ORDER BY 有2个功能

1. 排序
2. 按排序字段累计

SELECT D.DEPTNO , SUM(DEPTNO)OVER(ORDER BY D.DEPTNO)
 FROM DEPT D ;

--按照入职时间先后，计算各部门的累计入职员工人数，累计的平均工资
SELECT ENAME,HIREDATE,SAL,DEPTNO,
       COUNT(EMPNO)OVER(PARTITION BY DEPTNO ORDER BY HIREDATE) 各部门累计人数,
       AVG(SAL)OVER(PARTITION BY DEPTNO ORDER BY HIREDATE) 各部门累计的平均工资
FROM EMP

分析函数和聚合函数的不同之处

1. 聚合函数分组用 group by ，分析函数使用 partition by
2. 聚合函数使用 order by 的时候只是排序，而分析函数中 order by 既可以排序，还会累计计算
3. 聚合函数使用的限制非常多，而分析函数几乎没有使用限制
4. 聚合函数可以放在 having 后面作为过滤条件，但是分析函数不可以

分析函数 排序

通过分析函数计算排名

  ROW_NUMBER() OVER(ORDER BY 排序字段)
        RANK() OVER(ORDER BY 排序字段)
  DENSE_RANK() OVER(ORDER BY 排序字段)

SAL	ROW_NUMBER()	DENSE_RANK()	RANK()
5000	1	1	1
3000	2	2	2
3000	3	2	2
2500	4	3	4

-- 查询员工的工资排名
SELECT *,ROWNUM 排名
 FROM (SELECT ENAME,SAL
        FROM EMP
       ORDER BY SAL DESC);

SELECT ENAME,SAL,
       ROW_NUMBER()OVER(ORDER BY SAL DESC) RN排名,
       DENSE_RANK()OVER(ORDER BY SAL DESC) DR排名,
       RANK()OVER(ORDER BY SAL DESC) RK排名
 FROM EMP;

总结

排序的方式：

1. ORDER BY + ROWNUM
2. ROW_NUMBER()
3. DENSE_RANK()
4. RANK()

去重的方式：

1. DISTINCT ： 在SELECT后面直接去重
2. GROUP BY ： 分组后对分组字段直接去重
3. ROWID ： 通过物理地址去重
4. UNION ： 合并结果集后去重

分析函数 环比和同比

分析函数：LAG() 与 LEAD() ：求之或之后的第N行

环比	（现阶段 - 同一周期上一阶段）/ 同一周期上一阶段
环比	（本月 - 上月）/ 上月
环比	（本季度 - 上季度） / 上季度
同比	（现阶段 - 上一周期相同阶段）/上一周期相同阶段
同比	（今年 - 去年同期） / 去年同期
同比	（今年1季度 - 去年1季度）/ 去年1季度

 --取前Y个的X字段的值，超出范围后 默认Z
LAG(X,Y,Z)OVER(ORDER BY SAL)
LEAD(X,Y,Z)OVER(ORDER BY SAL)
    X 取值字段
    Y 步长
    Z 超出范围的默认值

-- 按入职时间查询员工和前一个员工的工资，并计算员工和前一个员工的工资增长额度
SELECT HIREDATE,ENAME,SAL,
       LAG(SAL,1,0)OVER(ORDER BY HIREDATE ASC) LAGSAL, --按照入职日期进行排序，入职越早排名越靠前
       E.SAL-LAG(SAL,1,0)OVER(ORDER BY HIREDATE ASC) ZE 
  FROM EMP;

-- 查询员工和下一个入职员工的工资差额
SELECT HIREDATE,ENAME,SAL,
       LEAD(SAL,1,0)OVER(ORDER BY HIREDATE ASC) LEADSAL, 
       E.SAL-LAG(SAL,1,0)OVER(ORDER BY HIREDATE ASC) CE 
  FROM EMP;