分析函数
分析函数是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个功能
- 排序
- 按排序字段累计
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
分析函数和聚合函数的不同之处
- 聚合函数分组用 group by ,分析函数使用 partition by
- 聚合函数使用 order by 的时候只是排序,而分析函数中 order by 既可以排序,还会累计计算
- 聚合函数使用的限制非常多,而分析函数几乎没有使用限制
- 聚合函数可以放在 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;
总结
排序的方式:
- ORDER BY + ROWNUM
- ROW_NUMBER()
- DENSE_RANK()
- RANK()
去重的方式:
- DISTINCT : 在SELECT后面直接去重
- GROUP BY : 分组后对分组字段直接去重
- ROWID : 通过物理地址去重
- 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;