一步步掌握 Oracle 分析函数的强大能力

Oracle 中的 OVER() 函数及其数据窗口

在 Oracle 数据库中，OVER() 函数提供了一种强大的方式来进行分析计算，它允许对每行数据指定一个“数据窗口”，并在该窗口内计算聚合结果。这个“数据窗口”是一个动态的子集，会随着每一行数据的变化而变化。通过 OVER() 函数，你可以在不影响查询的情况下，使用多种窗口函数如 SUM()、AVG()、ROW_NUMBER() 等来分析数据。

1. OVER() 函数基础

1.1 什么是 OVER() 函数

OVER() 函数是用于定义数据窗口的一个子句，它可以与多种分析函数一起使用，例如：

• 聚合函数：SUM()、AVG()、COUNT()、MIN()、MAX() 等
• 排名函数：ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE() 等
• 位移函数：LAG()、LEAD()

1.2 OVER() 函数的基本语法

FUNCTION_NAME(<参数>) OVER (
  [PARTITION BY <列名>]
  [ORDER BY <列名> ASC|DESC]
  [ROWS|RANGE BETWEEN <value> PRECEDING AND <value> FOLLOWING]
)

• FUNCTION_NAME：分析函数，如 SUM()、AVG() 等。
• PARTITION BY：用于将数据分成多个分区，每个分区独立进行计算。
• ORDER BY：定义在每个分区内的排序方式，通常用于排名函数。
• ROWS 和 RANGE：指定数据窗口的范围。

2. PARTITION BY 和 ORDER BY 示例

有一个名为 employees 的表，包含以下数据：

CREATE TABLE employees (
    department_id NUMBER,
    employee_id   NUMBER,
    salary        NUMBER
);

INSERT INTO employees VALUES (10, 1, 5000);
INSERT INTO employees VALUES (10, 2, 6000);
INSERT INTO employees VALUES (20, 3, 7000);
INSERT INTO employees VALUES (20, 4, 8000);
INSERT INTO employees VALUES (20, 5, 9000);

示例 1：按部门计算每个员工的工资排名

SELECT department_id, employee_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

• 数据根据 department_id 分区，即每个部门的数据独立计算。
• 在每个分区内，根据 salary 降序排序。
• RANK() 函数返回每个员工的工资排名。
• 输出结果：

  DEPARTMENT_ID EMPLOYEE_ID SALARY  SALARY_RANK
  ------------ ----------- ------- -----------
            10           2   6000            1
            10           1   5000            2
            20           5   9000            1
            20           4   8000            2
            20           3   7000            3
  

3. 使用 ROWS 和 RANGE 的 OVER() 函数

ROWS 和 RANGE 子句用于定义数据窗口的范围。它们的区别在于：

• ROWS：基于物理行的数目来定义窗口范围。
• RANGE：基于逻辑值的范围来定义窗口。

示例 2：计算每行数据的前后两行的和（ROWS）

SELECT salary,
       SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_salary
FROM employees;

• 对每个 salary 计算其前一行、当前行和后一行的 salary 之和。
• 输出结果：

  SALARY SUM_SALARY
  ------- ----------
     5000      11000
     6000      18000
     7000      21000
     8000      24000
     9000      17000
  

示例 3：计算每行数据的前后相差最多1000的行的和（RANGE）

SELECT salary,
       SUM(salary) OVER (ORDER BY salary RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS sum_salary
FROM employees;

• 对每个 salary 计算在其上下相差不超过1000的行的 salary 之和。
• 输出结果：

  SALARY SUM_SALARY
  ------- ----------
     5000      11000
     6000      11000
     7000      15000
     8000      15000
     9000      17000
  

4. 更复杂的 OVER() 函数示例

示例 4：复杂的开窗操作

有一个更复杂的数据表 sales_data，包含销售数据，并希望计算每个销售额在前50个和后150个记录范围内的总和。

创建并插入数据：

CREATE TABLE sales_data (
    sales_id NUMBER,
    sales_value NUMBER
);

-- 插入假数据
INSERT INTO sales_data VALUES (1, 100);
INSERT INTO sales_data VALUES (2, 200);
INSERT INTO sales_data VALUES (3, 300);
-- (继续插入数据)

执行 SQL 查询：

SELECT sales_id, sales_value,
       SUM(sales_value) OVER (ORDER BY sales_value RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS sum_sales
FROM sales_data;

• 对每一行数据，计算其销售额在前50个和后150个范围内的总和。
• 这将生成一个窗口，该窗口随着每行数据的变化而调整。

OVER() 函数通过定义数据窗口提供了一种灵活的方式进行高级数据分析。在使用时：

• PARTITION BY 可将数据分组。
• ORDER BY 用于排序数据。
• ROWS 和 RANGE 子句决定了窗口的大小和形态。
  这些功能组合在一起，为数据分析提供了极大的灵活性和控制力。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

让AI工具成为你的得力助手，感受AI工具的无限可能，让复杂的任务变得简单，让你的工作更加轻松和高效。