Oracle之开窗函数使用

Oracle中的开窗函数（Window Functions）是一种强大的工具，用于在SQL查询中对数据进行复杂的分析和聚合操作，而无需改变原始查询结果的行数或顺序。以下是关于Oracle开窗函数的使用方法和常见示例：
1. 开窗函数的基本语法
开窗函数的基本语法如下：
<窗口函数> OVER (
    [PARTITION BY <列名,...>] -- 定义窗口的分区
    [ORDER BY <列名,...>] -- 定义窗口内的排序顺序
    [ROWS BETWEEN <范围>] -- 定义窗口内的行范围（可选）
)

•  <窗口函数>：如SUM、AVG、COUNT、ROW_NUMBER等。
•  PARTITION BY：用于将数据划分为多个分区，每个分区独立计算。
•  ORDER BY：用于在分区内部定义行的排序顺序。
•  ROWS BETWEEN：用于定义窗口的范围，例如计算当前行及其前后几行的值。
2. 常见开窗函数
2.1 排名函数
•  ROW_NUMBER()：为每一行分配一个唯一的序号。
•  RANK()：为每一行分配排名，相同值的行会分配相同的排名，但会导致排名跳跃。
•  DENSE_RANK()：与RANK()类似，但排名连续。
2.2 聚合函数
•  SUM(column) OVER (...)：计算指定列的总和。
•  AVG(column) OVER (...)：计算指定列的平均值。
•  COUNT(column) OVER (...)：计算分区中的行数。
•  MIN(column) OVER (...) 和 MAX(column) OVER (...)：分别返回分区中的最小值和最大值。
2.3 其他函数
•  LEAD(column, n)：获取当前行之后第n行的值。
•  LAG(column, n)：获取当前行之前第n行的值。
3. 使用示例
3.1 计算每个部门的平均工资
SELECT department, AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

此查询计算每个部门的平均工资，同时保留每个员工的行。
3.2 计算每个员工的工资排名
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

此查询为每个员工按工资降序分配排名。
3.3 计算移动平均
SELECT employee_id, salary, AVG(salary) OVER (ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_avg_salary
FROM employees;

此查询计算每个员工工资的移动平均值，窗口大小为当前行及其前后两行。
4. 优势
•  提高效率：避免使用子查询或临时表，减少查询复杂度。
•  灵活分析：可以在整个结果集上进行复杂的计算，而不仅仅是分组。
•  简化代码：将复杂的计算逻辑封装在一个查询中，使代码更简洁。
5. 注意事项
•  性能影响：在处理大数据集时，开窗函数可能会对性能产生影响。建议优化索引和查询结构。
•  兼容性：开窗函数是较新的特性，可能不在所有Oracle版本中完全支持。
通过合理使用Oracle开窗函数，可以显著提升数据处理效率和查询灵活性。