Oracle数据库中分析函数的使用详解与实践案例
引言
在现代数据处理和分析中，Oracle数据库以其强大的功能和灵活性脱颖而出。特别是其独特的分析函数，为复杂的数据查询和报表生成提供了强大的支持。本文将深入探讨Oracle数据库中的分析函数，通过详细的解释和丰富的实践案例，帮助读者掌握这些高级功能，提升数据处理能力。
一、认识分析函数
1.1 什么是分析函数？
分析函数是Oracle数据库中专门用于解决复杂报表统计需求的功能强大的函数。它们能够在数据中进行分组，然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。
1.2 分析函数与聚合函数的区别
普通的聚合函数（如SUM、AVG）使用GROUP BY子句进行分组，每个分组返回一个统计值。而分析函数采用PARTITION BY进行分组，并且每组中的每一行都可以返回一个统计值。
1.3 分析函数的形式
分析函数通常带有一个开窗函数OVER()，包含三个主要子句：
分组（PARTITION BY）：定义分组的依据。
排序（ORDER BY）：定义窗口内的排序顺序。
窗口（ROWS）：定义窗口的范围。
基本语法如下：
<分析函数> OVER (
[PARTITION BY <列名,...>]
[ORDER BY <列名,...>]
[ROWS BETWEEN <起始行> AND <结束行>]
)
二、常用的分析函数
2.1 RANK() 和 DENSE_RANK()
RANK()：返回当前行的排名，如果有并列排名，则跳过后续排名。
DENSE_RANK()：返回当前行的排名，如果有并列排名，则不跳过后续排名。
示例：
SELECT DEPTNO, EMPNO, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RANK,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) DENSE_RANK
FROM EMP;
2.2 ROW_NUMBER()
ROW_NUMBER()：为分区内的每一行分配一个唯一的序号。
示例：
SELECT DEPTNO, EMPNO, SAL, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) ROW_NUM
FROM EMP;
2.3 NTILE()
NTILE(n)：将分区内的数据分成n个等级，并返回当前行所在的等级。
示例：
SELECT DEPTNO, EMPNO, SAL, NTILE(4) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) QUARTILE
FROM EMP;
2.4 LEAD() 和 LAG()
LEAD()：返回当前行之后某行的值。
LAG()：返回当前行之前某行的值。
示例：
SELECT DEPTNO, EMPNO, SAL, LEAD(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) NEXT_SAL,
LAG(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL) PREV_SAL
FROM EMP;
三、窗口函数的应用
3.1 窗口函数的特点
无需分组：与普通的聚合函数不同，窗口函数不需要使用GROUP BY子句。
跨行操作：可以对当前行及其相关行进行操作。
与ORDER BY和PARTITION BY结合使用：定义窗口的分区和排序顺序。
3.2 基本语法
<窗口函数> OVER (
[PARTITION BY <列名,...>]
[ORDER BY <列名,...>]
[ROWS BETWEEN <起始行> AND <结束行>]
)
四、实践案例
4.1 员工薪资排名
需求：显示各部门员工的薪资及其在该部门的排名。
SELECT DEPTNO, EMPNO, ENAME, SAL,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) SAL_RANK
FROM EMP;
4.2 累积求和
需求：计算各部门员工的薪资累积总和。
SELECT DEPTNO, EMPNO, ENAME, SAL,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUM_SUM
FROM EMP;
4.3 显示部门最高薪资
需求：显示各部门员工的薪资，并附带显示该部门的最高薪资。
SELECT DEPTNO, EMPNO, ENAME, SAL,
MAX(SAL) OVER (PARTITION BY DEPTNO) MAX_SAL
FROM EMP;
五、模型子句的使用
5.1 模型子句简介
模型子句允许在SQL查询中进行复杂的计算和赋值操作，适用于多维数据分析。
5.2 基本语法
SELECT ...
FROM ...
MODEL
PARTITION BY (<列名,...>)
DIMENSION BY (<列名,...>)
MEASURES (<列名,...>)
RULES (
<赋值规则>...
)
5.3 应用案例
需求：计算各部门员工的薪资增长10%后的新薪资。
SELECT DEPTNO, EMPNO, ENAME, SAL, NEW_SAL
FROM EMP
MODEL
PARTITION BY (DEPTNO)
DIMENSION BY (EMPNO)
MEASURES (SAL, 0 AS NEW_SAL)
RULES (
NEW_SAL[ANY, ANY] = SAL[CV(), CV()] * 1.1
);
六、层次查询与递归查询
6.1 层次查询
使用CONNECT BY子句进行层次查询。
示例：查询员工及其上级关系。
SELECT LEVEL, EMPNO, ENAME, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
6.2 递归查询
使用递归子查询处理复杂层次结构。
示例：计算阶乘。
WITH FACTORIAL (N, RESULT) AS (
SELECT 1, 1 FROM DUAL
UNION ALL
SELECT N + 1, RESULT * (N + 1) FROM FACTORIAL WHERE N < 5
)
SELECT * FROM FACTORIAL;
七、模型子句与层次查询的结合
需求：计算各部门员工的薪资总和及其在总公司的占比。
SELECT DEPTNO, EMPNO, ENAME, SAL, DEPT_SUM, TOTAL_SUM, DEPT_RATIO
FROM EMP
MODEL
PARTITION BY (DEPTNO)
DIMENSION BY (EMPNO)
MEASURES (SAL, 0 AS DEPT_SUM, 0 AS TOTAL_SUM, 0 AS DEPT_RATIO)
RULES (
DEPT_SUM[ANY, ANY] = SUM(SAL)[CV(), ANY],
TOTAL_SUM[ANY, ANY] = SUM(SAL)[ANY, ANY],
DEPT_RATIO[ANY, ANY] = DEPT_SUM[CV(), CV()] / TOTAL_SUM[CV(), CV()]
)
ORDER BY DEPTNO, EMPNO;
八、总结
Oracle数据库的分析函数和高级功能为复杂的数据处理和分析提供了强大的工具。通过掌握这些功能，可以大大提升SQL查询的灵活性和效率。本文通过详细的解释和丰富的实践案例，帮助读者深入理解并应用这些高级功能，解决实际工作中的复杂查询需求。
希望本文能成为您在Oracle数据库学习和应用中的有力助手，助您在大数据处理和分析的道路上更进一步。