Oracle 9i 分析函数详解：开窗函数与聚合操作

"Oracle 9i 分析函数参考手册" Oracle 9i 分析函数是一种强大的数据库查询工具，它们在数据库查询中用于执行聚合操作，但与传统的聚合函数（如 SUM, COUNT, AVG 等）不同，分析函数能够针对每个分组返回多行结果，而不仅仅是单行汇总。这些函数在处理复杂的数据分析任务时特别有用，例如计算排名、移动平均、差异等。 分析函数的关键概念是数据窗口（Window），这定义了函数作用的行集合。数据窗口可以通过 `OVER` 子句来指定，并可以依据 `ORDER BY` 或 `PARTITION BY` 子句进行定制。`ORDER BY` 用于确定行的顺序，而 `PARTITION BY` 将数据分为不同的分区。 1. `ORDER BY`：默认情况下，分析函数会按照 `ORDER BY` 子句指定的顺序对数据进行处理。例如，`OVER(ORDER BY salary)` 会按照薪水对员工进行排序，然后计算薪水的累计值。 2. `PARTITION BY`：将数据分割成多个分区，每个分区内的分析函数独立计算。例如，`OVER(PARTITION BY deptno)` 按照部门对员工进行分组，然后在每个部门内部计算分析函数。 3. `RANGE BETWEEN` 和 `ROWS BETWEEN`：这两个子句用于定义数据窗口的范围，根据行的相对位置来确定窗口大小。例如，`RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING` 会包含当前行前50行到后150行的数据，而 `ROWS BETWEEN 50 PRECEDING AND 150 FOLLOWING` 会包含前50行和后150行的绝对行数。 4. `UNBOUNDED PRECEDING` 和 `UNBOUNDED FOLLOWING`：这两个关键字表示数据窗口的边界可以扩展到分区或排序的起始和结束，即包括所有之前的行和之后的所有行。 分析函数的一个示例是 `AVG`，它计算指定表达式的平均值。在 `OVER` 子句中使用 `AVG` 可以得到每个分区或每个排序组的平均值，而不仅仅是整个数据集的平均值。例如，在员工表中，可以计算每个员工相对于其所在部门的平均薪水。 ```sql SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_per_dept FROM employees; ``` 此查询将返回每个员工的薪水以及他们所在部门的平均薪水。 其他常见的分析函数还包括 `RANK`, `DENSE_RANK`, `ROW_NUMBER` 用于生成排名，`LEAD` 和 `LAG` 用于访问当前行前后行的数据，`SUM` 和 `COUNT` 的累积版本，以及 `FIRST_VALUE`, `LAST_VALUE` 获取窗口内的第一个或最后一个值等。 通过灵活运用这些分析函数，数据库管理员和开发人员可以构建出复杂的查询，以满足各种数据分析和报表生成的需求。在 Oracle 9i 中，理解并熟练掌握分析函数的使用是提升数据库查询效率和数据洞察力的关键。