Oracle之分析函数 常用的分析函数

Oracle分析函数是数据库查询中的一种高级工具，特别设计用于处理复杂的报表统计需求。它们与传统的聚合函数（如SUM, AVG, COUNT等）有所不同，因为分析函数能够在数据集上进行分组计算，同时为每组的每一行返回一个统计值，而不仅仅是每个分组一个值。这使得分析函数在处理诸如排名、累计和移动平均等任务时非常有用。 分析函数的核心在于`OVER()`函数，它定义了一个“分析窗口”，在这个窗口内执行特定的计算。`OVER()`函数包括三个主要的子句： 1. **分组（PARTITION BY）**：这个子句将数据划分为多个分区，每个分区内的计算是独立的。例如，如果我们按部门（DEPTNO）分组，那么每个部门内部的计算都是独立的。 2. **排序（ORDER BY）**：这个子句决定了在每个分组内如何对行进行排序。排序顺序影响了分析函数的结果，因为某些函数（如LEAD, LAG, RANK等）依赖于行的相对位置。 3. **窗口（ROWS）**：窗口定义了分析函数作用的行范围。它可以指定为无界（UNBOUNDED PRECEDING/FOLLOWING）来涵盖所有行，也可以指定具体的行边界，比如当前行（CURRENT ROW）或特定行数之前或之后的行。 举个例子，如果我们想要显示每个部门中每个员工的工资以及该部门的最高工资，可以使用`LAST_VALUE()`函数，如下所示： ```sql SELECT DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_SAL FROM EMP; ``` 在这个查询中，`OVER()`函数的`PARTITION BY DEPTNO`将数据按部门分组，`ORDER BY SAL`指定了排序依据，`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`表示窗口包含了所有行，因此`LAST_VALUE(SAL)`返回每个部门工资排序后的最后一个值，即最高工资。 关于`OVER()`函数的执行机制，它通常在SQL查询的其他操作（如`ORDER BY`）之后进行。如果SQL查询中的`ORDER BY`与`OVER()`中的`ORDER BY`相同，那么`OVER()`可以直接使用已排序的数据。如果不同，`OVER()`会在其自己的排序上下文中执行，而SQL查询的`ORDER BY`则在最后应用，以确定最终的输出顺序。 在不指定窗口范围时，分析函数会有默认的行为。如果存在`ORDER BY`，窗口默认是`UNBOUNDED PRECEDING AND CURRENT ROW`，即从当前行开始的无界前向范围。如果没有`ORDER BY`，窗口默认是`UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`，覆盖整个分组。 了解并熟练掌握Oracle的分析函数对于优化复杂查询和报表生成至关重要，它们能够帮助数据库管理员和开发人员更高效地处理大量数据，提供更精细的数据分析能力。