Oracle数据库窗口函数：高级数据处理，实现复杂查询需求（附实战案例）

# 1. Oracle数据库窗口函数概述**

窗口函数是一种强大的数据库函数，它允许用户在数据的一个子集（称为窗口）上执行计算。窗口函数通过将当前行与窗口中的其他行进行比较来计算结果，从而可以获得更深入的数据见解。

窗口函数广泛应用于各种数据分析场景，例如：

* 排序和分组数据
* 计算聚合值（如总和、平均值）
* 查找特定模式或趋势
* 进行时间序列分析

# 2.1 窗口函数的概念和分类

### 窗口函数的概念

窗口函数是一种特殊的聚合函数，它可以在数据的一个子集（称为窗口）上进行计算。窗口的大小和形状由窗口函数的语法指定。

窗口函数与传统聚合函数的主要区别在于，它们可以访问当前行之前或之后的行。这使得窗口函数能够执行诸如计算移动平均值、查找排名或识别模式等操作。

### 窗口函数的分类

窗口函数可以根据其窗口类型和聚合类型进行分类。

**窗口类型：**

- **行窗口：**窗口大小由行数指定，例如前 5 行或后 3 行。
- **范围窗口：**窗口大小由行范围指定，例如前 10% 的行或后 20 个单位的行。
- **无界窗口：**窗口大小不受限制，从当前行开始到数据集的末尾或开头。

**聚合类型：**

- **排序函数：**计算行的排名或顺序，例如 ROW_NUMBER() 和 RANK()。
- **聚合函数：**对窗口内的数据进行聚合，例如 SUM() 和 AVG()。
- **移动窗口函数：**计算当前行之前或之后的行的值，例如 LEAD() 和 LAG()。

### 代码块示例：

SELECT
  ROW_NUMBER() OVER (ORDER BY sales_date) AS row_num,
  SUM(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM sales_data;

**逻辑分析：**

此代码使用 ROW_NUMBER() 窗口函数为每个销售记录分配一个行号，并使用 SUM() 窗口函数计算每行前 3 行的销售额移动平均值。

**参数说明：**

- ROW_NUMBER()：指定行窗口，按 sales_date 排序。
- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW：指定范围窗口，计算前 2 行和当前行的销售额。

# 3.1 排序和分组函数

排序和分组函数用于对数据进行排序和分组，以便进行进一步的分析和处理。

#### 3.1.1 ROW_NUMBER() 函数

**语法：**

ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY order_expression)

**参数：**

* `partition_expression`：指定数据的分组依据，可以是单个列或多个列的组合。
* `order_expression`：指定排序依据，可以是单个列或多个列的组合。

**功能：**

`ROW_NUMBER()` 函数为每个分组中的每一行分配一个唯一的行号。行号从 1 开始，按 `order_expression` 指定的顺序递增。

**代码块：**

SELECT product_id, product_name, ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY sales_quantity DESC) AS row_num
FROM sales_data;

**逻辑分析：**

该查询按 `product_category` 分组，并按 `sales_quantity` 降序对每个分组中的产品进行排序。`ROW_NUMBER()` 函数为每个分组中的每一行