如何利用MySQL的窗口函数（Window Functions）实现复杂的OLAP操作，如移动平均（Moving Average）与排名（Rank）？

MySQL 窗口函数：OLAP 分析的利器

各位朋友，大家好。今天我们来聊聊 MySQL 窗口函数，以及如何利用它们进行复杂的 OLAP（Online Analytical Processing）操作，例如移动平均和排名。窗口函数是 MySQL 8.0 引入的强大特性，它允许我们在查询结果集的特定窗口（即一组相关的行）上执行计算，而无需使用自连接或子查询等复杂操作。这极大地简化了 OLAP 分析，提高了查询效率。

什么是窗口函数？

简单来说，窗口函数就像是聚合函数（例如 SUM, AVG, COUNT），但不同之处在于，聚合函数会将多行数据聚合为一行，而窗口函数则会为结果集中的每一行都返回一个值。这个值是基于与当前行相关的“窗口”计算得出的。

窗口函数的语法通常如下：

function_name(argument(s)) OVER (
  [PARTITION BY column_list]
  [ORDER BY column_list [ASC | DESC]]
  [frame_clause]
)

function_name: 这是要执行的窗口函数，例如 AVG, RANK, ROW_NUMBER 等。
argument(s): 窗口函数所需的参数，某些函数可能不需要参数。
OVER (...): 定义窗口的范围和排序方式。
- PARTITION BY column_list: 将结果集划分为多个分区。窗口函数将分别应用于每个分区。如果没有 PARTITION BY 子句，则整个结果集被视为一个分区。
- ORDER BY column_list [ASC | DESC]: 指定每个分区内行的排序方式。这会影响某些窗口函数的结果，例如排名和移动平均。
- frame_clause: 定义当前行的窗口范围。允许我们指定与当前行相关的哪些行用于计算。例如，我们可以定义一个窗口，包含当前行之前的 N 行和之后的 M 行。

窗口函数分类

窗口函数大致可以分为以下几类：

聚合窗口函数: AVG, SUM, MIN, MAX, COUNT。这些函数在窗口范围内执行聚合计算。
排名窗口函数: RANK, DENSE_RANK, ROW_NUMBER, NTILE。这些函数根据窗口内的排序为每一行分配一个排名。
值窗口函数: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE。这些函数允许我们访问窗口中其他行的值。

准备数据

为了演示窗口函数的使用，我们创建一个简单的 sales 表，并插入一些数据：

CREATE TABLE sales (
  sale_date DATE,
  product_id INT,
  sale_amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-01', 1, 100.00),
('2023-01-01', 2, 150.00),
('2023-01-02', 1, 120.00),
('2023-01-02', 2, 180.00),
('2023-01-03', 1, 110.00),
('2023-01-03', 2, 200.00),
('2023-01-04', 1, 130.00),
('2023-01-04', 2, 170.00),
('2023-01-05', 1, 140.00),
('2023-01-05', 2, 190.00);

移动平均

移动平均是一种常用的时间序列分析技术，用于平滑数据并识别趋势。我们可以使用窗口函数轻松地计算移动平均。

3日移动平均

下面的查询计算了 sales 表中 sale_amount 的 3 日移动平均值：

SELECT
  sale_date,
  product_id,
  sale_amount,
  AVG(sale_amount) OVER (ORDER BY sale_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM sales;

AVG(sale_amount) OVER (...)：计算 sale_amount 的平均值。
ORDER BY sale_date ASC: 按照 sale_date 升序排列。
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: 定义窗口范围。它包括当前行以及当前行之前的两行。这意味着，对于每一行，计算的是该行及其前两行 sale_amount 的平均值。

查询结果：

sale_date	product_id	sale_amount	moving_average
2023-01-01	1	100.00	100.00
2023-01-01	2	150.00	125.00
2023-01-02	1	120.00	123.33
2023-01-02	2	180.00	150.00
2023-01-03	1	110.00	136.67
2023-01-03	2	200.00	163.33
2023-01-04	1	130.00	146.67
2023-01-04	2	170.00	166.67
2023-01-05	1	140.00	146.67
2023-01-05	2	190.00	166.67

按产品计算移动平均

如果我们想按产品计算移动平均，可以使用 PARTITION BY 子句：

SELECT
  sale_date,
  product_id,
  sale_amount,
  AVG(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM sales;

PARTITION BY product_id: 将结果集按 product_id 分区。这意味着，对于每个产品，都会单独计算移动平均值。

查询结果（截取部分）：

sale_date	product_id	sale_amount	moving_average
2023-01-01	1	100.00	100.00
2023-01-02	1	120.00	110.00
2023-01-03	1	110.00	110.00
2023-01-04	1	130.00	120.00
2023-01-05	1	140.00	126.67
2023-01-01	2	150.00	150.00
2023-01-02	2	180.00	165.00
2023-01-03	2	200.00	176.67
2023-01-04	2	170.00	183.33
2023-01-05	2	190.00	183.33

排名

窗口函数可以用于对结果集进行排名。 MySQL 提供了多种排名函数，包括 RANK, DENSE_RANK, ROW_NUMBER 和 NTILE。

RANK

RANK 函数根据窗口内的排序，为每一行分配一个排名。如果两行或多行的值相同，则它们将获得相同的排名。下一个排名将跳过相应的排名数。

SELECT
  sale_date,
  product_id,
  sale_amount,
  RANK() OVER (ORDER BY sale_amount DESC) AS sales_rank
FROM sales;

RANK() OVER (ORDER BY sale_amount DESC): 根据 sale_amount 降序排列，并为每一行分配一个排名。

查询结果：

sale_date	product_id	sale_amount	sales_rank
2023-01-03	2	200.00	1
2023-01-05	2	190.00	2
2023-01-02	2	180.00	3
2023-01-04	2	170.00	4
2023-01-01	2	150.00	5
2023-01-05	1	140.00	6
2023-01-04	1	130.00	7
2023-01-02	1	120.00	8
2023-01-03	1	110.00	9
2023-01-01	1	100.00	10

DENSE_RANK

DENSE_RANK 函数与 RANK 函数类似，但它不会跳过排名。如果两行或多行的值相同，则它们将获得相同的排名，并且下一个排名将紧随其后。

为了演示 DENSE_RANK 的不同，我们稍微修改一下数据，让 sale_amount 出现重复值：

INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-06', 3, 180.00);

现在运行以下查询：

SELECT
  sale_date,
  product_id,
  sale_amount,
  DENSE_RANK() OVER (ORDER BY sale_amount DESC) AS sales_rank
FROM sales;

查询结果：

sale_date	product_id	sale_amount	sales_rank
2023-01-03	2	200.00	1
2023-01-05	2	190.00	2
2023-01-02	2	180.00	3
2023-01-06	3	180.00	3
2023-01-04	2	170.00	4
2023-01-01	2	150.00	5
2023-01-05	1	140.00	6
2023-01-04	1	130.00	7
2023-01-02	1	120.00	8
2023-01-03	1	110.00	9
2023-01-01	1	100.00	10

可以看到，sale_amount 为 180.00 的两行都获得了排名 3，而下一个排名是 4，没有跳过。

ROW_NUMBER

ROW_NUMBER 函数为窗口中的每一行分配一个唯一的连续整数，从 1 开始。即使两行或多行的值相同，它们也会获得不同的排名。

SELECT
  sale_date,
  product_id,
  sale_amount,
  ROW_NUMBER() OVER (ORDER BY sale_amount DESC) AS sales_rank
FROM sales;

查询结果：

sale_date	product_id	sale_amount	sales_rank
2023-01-03	2	200.00	1
2023-01-05	2	190.00	2
2023-01-02	2	180.00	3
2023-01-06	3	180.00	4
2023-01-04	2	170.00	5
2023-01-01	2	150.00	6
2023-01-05	1	140.00	7
2023-01-04	1	130.00	8
2023-01-02	1	120.00	9
2023-01-03	1	110.00	10
2023-01-01	1	100.00	11

可以看到，即使 sale_amount 为 180.00 的两行值相同，它们也分别获得了排名 3 和 4。

NTILE

NTILE 函数将窗口中的行划分为指定数量的组，并为每一行分配一个组号。

例如，将销售数据划分为 4 个等级：

SELECT
  sale_date,
  product_id,
  sale_amount,
  NTILE(4) OVER (ORDER BY sale_amount DESC) AS sales_rank
FROM sales;

查询结果：

sale_date	product_id	sale_amount	sales_rank
2023-01-03	2	200.00	1
2023-01-05	2	190.00	1
2023-01-02	2	180.00	1
2023-01-06	3	180.00	2
2023-01-04	2	170.00	2
2023-01-01	2	150.00	2
2023-01-05	1	140.00	3
2023-01-04	1	130.00	3
2023-01-02	1	120.00	3
2023-01-03	1	110.00	4
2023-01-01	1	100.00	4

按产品进行排名

与移动平均一样，我们也可以使用 PARTITION BY 子句按产品进行排名：

SELECT
  sale_date,
  product_id,
  sale_amount,
  RANK() OVER (PARTITION BY product_id ORDER BY sale_amount DESC) AS sales_rank
FROM sales;

PARTITION BY product_id: 将结果集按 product_id 分区。这意味着，对于每个产品，都会单独进行排名。

查询结果（截取部分）：

sale_date	product_id	sale_amount	sales_rank
2023-01-05	1	140.00	1
2023-01-04	1	130.00	2
2023-01-02	1	120.00	3
2023-01-03	1	110.00	4
2023-01-01	1	100.00	5
2023-01-03	2	200.00	1
2023-01-05	2	190.00	2
2023-01-02	2	180.00	3
2023-01-04	2	170.00	4
2023-01-01	2	150.00	5
2023-01-06	3	180.00	1

值窗口函数：访问窗口内的其他行

值窗口函数允许我们访问窗口中其他行的值。常用的值窗口函数包括 LAG, LEAD, FIRST_VALUE, LAST_VALUE 和 NTH_VALUE。

LAG 和 LEAD

LAG 函数允许我们访问窗口中当前行之前的 N 行的值。 LEAD 函数允许我们访问窗口中当前行之后的 N 行的值。

例如，获取每个产品前一天的销售额：

SELECT
  sale_date,
  product_id,
  sale_amount,
  LAG(sale_amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date ASC) AS previous_day_sales
FROM sales;

LAG(sale_amount, 1, 0): 获取 sale_amount 前一行（偏移量为 1）的值。如果前一行不存在，则返回 0（默认值）。

查询结果（截取部分）：

sale_date	product_id	sale_amount	previous_day_sales
2023-01-01	1	100.00	0.00
2023-01-02	1	120.00	100.00
2023-01-03	1	110.00	120.00
2023-01-04	1	130.00	110.00
2023-01-05	1	140.00	130.00
2023-01-01	2	150.00	0.00
2023-01-02	2	180.00	150.00
2023-01-03	2	200.00	180.00
2023-01-04	2	170.00	200.00
2023-01-05	2	190.00	170.00
2023-01-06	3	180.00	0.00

FIRST_VALUE 和 LAST_VALUE

FIRST_VALUE 函数返回窗口中第一行的值。 LAST_VALUE 函数返回窗口中最后一行的值。

例如，获取每个产品第一次销售的 sale_amount:

SELECT
  sale_date,
  product_id,
  sale_amount,
  FIRST_VALUE(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date ASC) AS first_sale_amount
FROM sales;

查询结果（截取部分）：

sale_date	product_id	sale_amount	first_sale_amount
2023-01-01	1	100.00	100.00
2023-01-02	1	120.00	100.00
2023-01-03	1	110.00	100.00
2023-01-04	1	130.00	100.00
2023-01-05	1	140.00	100.00
2023-01-01	2	150.00	150.00
2023-01-02	2	180.00	150.00
2023-01-03	2	200.00	150.00
2023-01-04	2	170.00	150.00
2023-01-05	2	190.00	150.00
2023-01-06	3	180.00	180.00

需要注意的是，LAST_VALUE 默认的 frame_clause 是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。这意味着，如果没有显式指定 frame_clause，LAST_VALUE 将返回当前行所在窗口范围内，截止到当前行的最后一行的值。如果需要获取整个窗口的最后一行，需要显式指定 frame_clause 为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

NTH_VALUE

NTH_VALUE 函数返回窗口中第 N 行的值。

例如，获取每个产品第二次销售的 sale_amount:

SELECT
  sale_date,
  product_id,
  sale_amount,
  NTH_VALUE(sale_amount, 2) OVER (PARTITION BY product_id ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_sale_amount
FROM sales;

NTH_VALUE(sale_amount, 2): 获取 sale_amount 第二行（N=2）的值。
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：如果没有这个子句，结果会和预期不符，因为默认窗口只包含当前行之前的行。

查询结果（截取部分）：

sale_date	product_id	sale_amount	second_sale_amount
2023-01-01	1	100.00	120.00
2023-01-02	1	120.00	120.00
2023-01-03	1	110.00	120.00
2023-01-04	1	130.00	120.00
2023-01-05	1	140.00	120.00
2023-01-01	2	150.00	180.00
2023-01-02	2	180.00	180.00
2023-01-03	2	200.00	180.00
2023-01-04	2	170.00	180.00
2023-01-05	2	190.00	180.00
2023-01-06	3	180.00	NULL

`frame_clause` 的更多用法

frame_clause 允许我们更精细地控制窗口的范围。除了前面提到的 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 和 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，还有以下选项：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 窗口包括从分区的第一行到当前行的所有行。这是大多数聚合窗口函数的默认 frame_clause。
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 窗口包括从当前行到分区的最后一行的所有行。
ROWS BETWEEN CURRENT ROW AND CURRENT ROW: 窗口只包含当前行。
ROWS BETWEEN N PRECEDING AND N FOLLOWING: 窗口包括当前行之前的 N 行和之后的 N 行。
RANGE BETWEEN value PRECEDING AND value FOLLOWING: 基于排序键的值范围定义窗口，而不是基于行数。 value 必须与排序键的数据类型兼容。

总结：高效分析的关键

窗口函数为我们提供了一种强大的方式来执行复杂的 OLAP 分析，包括移动平均、排名和访问窗口内其他行的值。通过使用 PARTITION BY 和 ORDER BY 子句，我们可以灵活地定义窗口的范围和排序方式。通过精细控制窗口范围，我们可以进行更深入的数据分析。

掌握窗口函数，提升数据处理能力

熟练掌握窗口函数，能够显著提高数据处理效率，简化复杂查询，并为数据分析提供更多可能性。希望通过今天的分享，大家能够对 MySQL 窗口函数有更深入的了解，并能够在实际工作中灵活运用。

MySQL 窗口函数：OLAP 分析的利器

什么是窗口函数？

窗口函数分类

准备数据

移动平均

排名

值窗口函数：访问窗口内的其他行

frame_clause 的更多用法

总结：高效分析的关键

掌握窗口函数，提升数据处理能力

发表回复 取消回复

`frame_clause` 的更多用法

发表回复取消回复