Oracle 窗口函数

Oracle 窗口函数是SQL语言中一项极其强大的功能，它赋予了你在保留原始行集的同时，对相关数据子集（“窗口”）进行复杂计算的能力。与将多行压缩为一行的标准聚合函数 (GROUP BY) 不同，窗口函数为结果集中的每一行都返回一个独立的计算值。

思维导图

一、窗口函数的通用语法结构

所有窗口函数都遵循一个核心的 OVER() 子句结构，它定义了计算的上下文——“窗口”。

function_name([arguments]) OVER (
  [PARTITION BY partition_expression, ...]
  [ORDER BY sort_expression [ASC|DESC] [NULLS FIRST|NULLS LAST], ...]
  [windowing_clause]
)

• PARTITION BY: 分区子句。将数据集逻辑上分割成多个独立的组（分区），窗口函数在每个分区内部独立计算。若省略，整个结果集被视为单个分区。
• ORDER BY: 排序子句。它定义了分区内各行的处理顺序。对于排名和位置函数，此子句至关重要。
• windowing_clause: 窗口范围子句。它更精确地定义了计算窗口的边界（例如 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示当前行、前一行和后一行）。如果省略（但有ORDER BY），默认通常是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

二、窗口函数分类与实战

背景表：
我们将使用一个简化的 emp 表进行所有演示，包含 empno, ename, job, deptno, sal, hiredate 等列。

### 2.1 排名窗口函数 ROW_NUMBER()
功能：为窗口内的每一行分配一个从1开始的唯一且连续的排名。即使行具有相同的值，排名也不会重复。 代码示例：按部门为员工按薪水降序进行唯一排名。
sql SELECT ename, deptno, sal, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num_rank FROM emp;

RANK()
功能：计算排名。如果值相同，则排名相同，但后续排名会跳过相应的位置（例如：1, 2, 2, 4）。 代码示例：按部门为员工按薪水降序进行跳跃排名。
sql SELECT ename, deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank_val FROM emp;

DENSE_RANK()
功能：计算排名。如果值相同，则排名相同，且后续排名不会跳过位置（例如：1, 2, 2, 3）。 代码示例：按部门为员工按薪水降序进行连续排名。
sql SELECT ename, deptno, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dense_rank_val FROM emp;

NTILE(n)
功能：将分区内的行分成 n 个大致相等的组（桶），并返回每行所在的桶号。 代码示例：将每个部门的员工按薪水降序分为4个等级。
sql SELECT ename, deptno, sal, NTILE(4) OVER (PARTITION BY deptno ORDER BY sal DESC) AS salary_quartile FROM emp;

### 2.2 聚合窗口函数 SUM() / COUNT() / AVG() / MAX() / MIN()
功能：将标准聚合函数应用于窗口。 代码示例 (分区聚合)：计算每个员工的薪水，并显示其所在部门的总薪水和平均薪水。
sql SELECT ename, deptno, sal, SUM(sal) OVER (PARTITION BY deptno) AS total_dept_salary, ROUND(AVG(sal) OVER (PARTITION BY deptno), 2) AS avg_dept_salary FROM emp;
代码示例 (累计聚合/移动求和)：计算每个部门内，按入职日期排序的累计薪水。
sql SELECT ename, deptno, sal, hiredate, SUM(sal) OVER (PARTITION BY deptno ORDER BY hiredate) AS running_total_salary FROM emp; 代码示例 (滑动窗口/移动平均)：计算每个部门内，基于当前行及前两行（共三行）的移动平均薪水。
sql SELECT ename, deptno, sal, hiredate, ROUND(AVG(sal) OVER (PARTITION BY deptno ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3_rows FROM emp;

### 2.3 位置/偏移窗口函数 LAG(expression, [offset], [default_value])
功能：访问当前行之前特定偏移量 (offset，默认为1) 的行的值。 代码示例：显示每个员工的薪水，以及其同部门内按薪水降序排列的上一名员工的薪水（若无则为0）。
sql SELECT ename, deptno, sal, LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal DESC) AS previous_salary FROM emp;

LEAD(expression, [offset], [default_value])
功能：访问当前行之后特定偏移量 (offset，默认为1) 的行的值。 代码示例：显示每个员工的薪水，以及其同部门内按入职日期排序的下一名入职员工的姓名（若无则为'N/A'）。
sql SELECT ename, deptno, hiredate, LEAD(ename, 1, 'N/A') OVER (PARTITION BY deptno ORDER BY hiredate) AS next_hired_employee FROM emp;

FIRST_VALUE(expression)
功能：返回窗口内第一行的指定表达式的值。 代码示例：显示每个员工及其所在部门最早入职的员工姓名。
sql SELECT ename, deptno, hiredate, FIRST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY hiredate) AS first_hired_in_dept FROM emp;

LAST_VALUE(expression)
功能：返回窗口内最后一行的指定表达式的值。 重要提示：默认窗口范围是到 CURRENT ROW，要获取整个分区的最后一个值， 必须显式定义窗口范围。
代码示例：显示每个员工及其所在部门薪水最高的员工姓名。
sql SELECT ename, deptno, sal, LAST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY sal ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_paid_in_dept FROM emp;
(这里通过薪水升序排列，然后取窗口的最后一行来找到薪水最高者)

NTH_VALUE(expression, n) 功能：返回窗口内 第 n 行的指定表达式的值。
代码示例：显示每个员工及其所在部门薪水第二高的员工薪水。
sql SELECT ename, deptno, sal, NTH_VALUE(sal, 2) OVER (PARTITION BY deptno ORDER BY sal DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest_salary FROM emp;

### *2.4 统计/分布窗口函数 RATIO_TO_REPORT(expression)
功能：计算当前行的值占分区内总和的比例。 代码示例：计算每个员工的薪水占其所在部门总薪水的百分比。
sql SELECT ename, deptno, sal, TO_CHAR(RATIO_TO_REPORT(sal) OVER (PARTITION BY deptno) * 100, '990.99') || '%' AS percentage_of_dept_sal FROM emp;

PERCENT_RANK()
功能：计算行的百分比排名，计算公式为 (rank - 1) / (rows_in_partition - 1)。 代码示例：计算每个员工薪水在其部门内的百分位排名。
sql SELECT ename, deptno, sal, ROUND(PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal ASC) * 100, 2) AS percentile_rank FROM emp;

CUME_DIST()
功能：计算行的累积分布，即小于等于当前值的行数占分区总行数的比例。 代码示例：计算薪水小于等于当前员工薪水的员工在其部门内的累积占比。
sql SELECT ename, deptno, sal, ROUND(CUME_DIST() OVER (PARTITION BY deptno ORDER BY sal ASC) * 100, 2) AS cumulative_distribution FROM emp;

## 三、综合实战案例：构建员工绩效分析报告

这个案例整合了多种窗口函数来生成一份详细的员工分析报告。

目标：对于每一位员工，我们希望得到他/她在其部门内的薪水排名、与部门平均薪水的差距、薪水占部门总额的比例，以及其上司（按薪水排名的上一位）的薪水。

代码示例：

WITH emp_analysis AS (
  SELECT
    empno,
    ename,
    deptno,
    sal,
    -- 使用聚合窗口函数计算部门的统计数据
    AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal,
    SUM(sal) OVER (PARTITION BY deptno) AS total_dept_sal,
    -- 使用排名窗口函数计算薪水排名
    RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dept_sal_rank,
    -- 使用位置窗口函数获取上一位员工的薪水
    LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal DESC) AS prev_rank_sal
  FROM emp
)
SELECT
  a.ename AS employee_name,
  a.deptno,
  a.sal AS current_salary,
  a.dept_sal_rank,
  ROUND(a.avg_dept_sal, 2) AS department_avg_salary,
  a.sal - ROUND(a.avg_dept_sal, 2) AS diff_from_avg,
  TO_CHAR(a.sal / a.total_dept_sal * 100, '990.99') || '%' AS percentage_of_total,
  a.prev_rank_sal AS superior_salary
FROM emp_analysis a
ORDER BY a.deptno, a.dept_sal_rank;

解析：

1. 我们使用公用表表达式 (CTE) WITH emp_analysis AS (...) 来分步处理，使查询更清晰。
2. 在 emp_analysis CTE 内部：
   • AVG(sal) OVER (...) 和 SUM(sal) OVER (...) 为每行计算出其所在部门的平均和总薪水。
   • RANK() OVER (...) 计算出部门内的薪水排名。
   • LAG(...) OVER (...) 找到了排名紧邻的上一位员工的薪水。
3. 在最终的 SELECT 语句中，我们引用 CTE emp_analysis 的结果，并进行简单的算术运算和格式化，生成了最终的报告列，如 diff_from_avg (与平均薪水差额) 和 percentage_of_total (薪水占比)。

总结： Oracle 窗口函数是 进行复杂数据分析的 核心技能。通过 灵活运用 PARTITION BY, ORDER BY, 和 窗口范围子句，你可以 用简洁的SQL实现 过去需要通过 自连接、子查询或过程化代码才能完成的 复杂逻辑。

---

## 练习题

背景表结构：

CREATE TABLE sales_data (
    sale_id          NUMBER(10),
    product_category VARCHAR2(50 CHAR),
    region           VARCHAR2(50 CHAR),
    sale_amount      NUMBER(10, 2),
    sale_date        DATE
);

请为以下每个场景编写使用窗口函数的SQL查询。

题目：

1. 查询所有销售记录，并为每条记录添加一列 category_rank，表示该笔销售额 (sale_amount) 在其所属产品类别 (product_category) 内的排名 (销售额越高，排名越靠前)。使用 RANK() 函数。
2. 查询所有销售记录，并为每条记录添加一列 total_region_sales，显示该记录所在地区 (region) 的总销售额。
3. 查询所有销售记录，并为每条记录添加一列 monthly_running_total，计算每个地区内，按销售日期 (sale_date) 排序的累计销售额。
4. 查询所有销售记录，并为每条记录添加一列 prev_sale_amount，显示同一地区内，按销售日期排序的上一笔销售的销售额。如果不存在上一笔，则显示0。
5. 查询所有销售记录，并为每条记录添加一-列 next_sale_amount，显示同一产品类别内，按销售日期排序的下一笔销售的销售额。如果不存在下一笔，则显示-1。
6. 找出每个产品类别中销售额最高的两条销售记录。
7. 查询所有销售记录，并为每条记录添加一列 highest_sale_in_category，显示该记录所在产品类别的单笔最高销售额。
8. 查询所有销售记录，并为每条记录添加一列 sale_percentage_of_region，计算该笔销售额占其所在地区销售总额的百分比。
9. 将每个地区的销售记录按销售额分为3个等级 (1为最高，3为最低)。为每条记录添加一列 sales_tier 来表示这个等级。
10. 查询所有销售记录，并为每条记录添加一列 moving_avg_3_sales，计算每个地区内，按销售日期排序，当前行及其前两行 (共三行) 的移动平均销售额。

答案与解析

1. 类别内销售额排名：

   SELECT
   s.*,
   RANK() OVER (PARTITION BY product_category ORDER BY sale_amount DESC) AS category_rank
   FROM sales_data s;
   

   • 解析： PARTITION BY product_category 将数据按类别分片，ORDER BY sale_amount DESC 在每个片内按销售额降序排，RANK() 计算排名。

2. 地区总销售额：

   SELECT
   s.*,
   SUM(sale_amount) OVER (PARTITION BY region) AS total_region_sales
   FROM sales_data s;
   

   • 解析： SUM(...) OVER (PARTITION BY region) 对每个地区分区内的所有 sale_amount 求和，并将这个总和赋给分区内的每一行。

3. 地区内月度累计销售额：

   SELECT
   s.*,
   SUM(sale_amount) OVER (PARTITION BY region ORDER BY sale_date) AS monthly_running_total
   FROM sales_data s;
   

   • 解析： ORDER BY sale_date 的加入，使得 SUM 的计算窗口默认为 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，从而实现了从分区开始到当前行的累计求和。

4. 获取上一笔销售额：

   SELECT
   s.*,
   LAG(sale_amount, 1, 0) OVER (PARTITION BY region ORDER BY sale_date) AS prev_sale_amount
   FROM sales_data s;
   

   • 解析： LAG(sale_amount, 1, 0) 在按地区分区、按日期排序的窗口中，获取往前1行的 sale_amount 值，如果不存在（即第一行），则返回默认值0。

5. 获取下一笔销售额：

   SELECT
   s.*,
   LEAD(sale_amount, 1, -1) OVER (PARTITION BY product_category ORDER BY sale_date) AS next_sale_amount
   FROM sales_data s;
   

   • 解析： LEAD(sale_amount, 1, -1) 在按类别分区、按日期排序的窗口中，获取往后1行的 sale_amount 值，如果不存在（即最后一行），则返回默认值-1。

6. 每个类别销售额最高的两条记录：

   SELECT * FROM (
   SELECT
   s.*,
   ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY sale_amount DESC) AS rn
   FROM sales_data s
   )
   WHERE rn <= 2;
   

   • 解析： 窗口函数不能直接用在 WHERE 子句中。因此，我们先用一个子查询（或CTE）计算出每个类别内的行号排名 rn，然后在外部查询中筛选出 rn <= 2 的记录。这里使用 ROW_NUMBER() 可以确保每个类别不多不少正好取两条（如果销售额相同）。

7. 类别内最高销售额：

   SELECT
   s.*,
   MAX(sale_amount) OVER (PARTITION BY product_category) AS highest_sale_in_category
   FROM sales_data s;
   

   • 解析： 类似于第2题，MAX(...) OVER (PARTITION BY ...) 会找到每个分区内的最大值，并将其赋给该分区的所有行。

8. 销售额占地区总额百分比：

   SELECT
   s.*,
   RATIO_TO_REPORT(sale_amount) OVER (PARTITION BY region) AS sale_percentage_of_region
   FROM sales_data s;
   

   • 解析： RATIO_TO_REPORT 在按 region 分区的窗口内计算，得出当前销售额占该地区总销售额的比例。

9. 销售额分等级：

   SELECT
   s.*,
   NTILE(3) OVER (PARTITION BY region ORDER BY sale_amount DESC) AS sales_tier
   FROM sales_data s;
   

   • 解析： NTILE(3) 将每个地区 (region) 的销售记录按销售额降序分成3个桶，并返回每条记录所在的桶号 (1, 2, 或 3)。

10. 3行移动平均销售额：

    SELECT
    s.*,
    AVG(sale_amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_sales
    FROM sales_data s;
    

    • 解析： 这里必须显式定义 windowing_clause。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了一个包含当前行和它前面两行（共三行）的滑动窗口，AVG 在这个窗口上计算平均值。