MySQL开窗函数|极客笔记

MySQL开窗函数

简介

在MySQL中，开窗函数是一种强大的分析函数，用于在查询结果集中执行诸如分组计算、排序、排名等操作。开窗函数通常与GROUP BY子句一起使用，能够处理复杂的查询需求，帮助我们更高效地处理数据。

本文将详细介绍MySQL中常用的开窗函数以及它们的使用方法，包括窗口排序、排序排名、累计求和、计算移动平均值等。

语法

MySQL开窗函数的语法一般如下所示：

<函数名>(<函数参数>) OVER ([PARTITION BY <列名1>, ...] [ORDER BY <列名2> {ASC|DESC}])

其中，常用的函数名包括ROW_NUMBER、RANK、DENSE_RANK、SUM、AVG等。PARTITION BY可以对函数进行分组，ORDER BY可以对分组内的数据进行排序。

示例

为了更好地理解开窗函数的使用，下面将通过一些示例来演示各个函数的具体用法。

示例数据

首先，我们创建一个名为orders的示例表，用于存储订单信息：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    cust_id INT,
    order_date DATE,
    total_amount DECIMAL(8, 2)
);

INSERT INTO orders (id, cust_id, order_date, total_amount) VALUES
    (1, 101, '2022-01-01', 100.00),
    (2, 101, '2022-01-02', 200.00),
    (3, 102, '2022-01-01', 150.00),
    (4, 102, '2022-01-03', 120.00),
    (5, 103, '2022-01-02', 180.00),
    (6, 103, '2022-01-03', 300.00);

1. ROW_NUMBER函数

ROW_NUMBER函数用于为结果集中的每一行生成一个唯一的行号。例如，下面的查询将为orders表中的每一行添加一个行号：

SELECT id, cust_id, order_date, total_amount,
       ROW_NUMBER() OVER (ORDER BY order_date) AS row_num
FROM orders;

执行上述查询后，输出如下：

| id | cust_id | order_date | total_amount | row_num |
|----|---------|------------|--------------|---------|
| 1  | 101     | 2022-01-01 | 100.00       | 1       |
| 3  | 102     | 2022-01-01 | 150.00       | 2       |
| 2  | 101     | 2022-01-02 | 200.00       | 3       |
| 5  | 103     | 2022-01-02 | 180.00       | 4       |
| 4  | 102     | 2022-01-03 | 120.00       | 5       |
| 6  | 103     | 2022-01-03 | 300.00       | 6       |

可以看到，每一行都附带了一个递增的行号。

2. RANK和DENSE_RANK函数

RANK函数用于为结果集中的每一行分配一个排名，并可能跳过某些排名。具有相同值的行将具有相同的排名，下一个排名将跳过同样数量的行。相比之下，DENSE_RANK函数会为具有相同值的行分配相同的排名，而且不会跳过任何排名。

例如，我们希望对orders表中的订单按照总金额进行排序，并为其分配排名和压缩排名：

SELECT id, cust_id, order_date, total_amount,
       RANK() OVER (ORDER BY total_amount DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY total_amount DESC) AS dense_rank
FROM orders;

执行上述查询后，输出如下：

| id | cust_id | order_date | total_amount | rank | dense_rank |
|----|---------|------------|--------------|------|------------|
| 6  | 103     | 2022-01-03 | 300.00       | 1    | 1          |
| 2  | 101     | 2022-01-02 | 200.00       | 2    | 2          |
| 5  | 103     | 2022-01-02 | 180.00       | 3    | 3          |
| 3  | 102     | 2022-01-01 | 150.00       | 4    | 4          |
| 1  | 101     | 2022-01-01 | 100.00       | 5    | 5          |
| 4  | 102     | 2022-01-03 | 120.00       | 6    | 6          |

可以看到，最高金额的订单的排名为1，而压缩排名是连续的，不会跳过。

3. SUM和AVG函数

SUM和AVG函数用于在结果集中计算各种汇总统计值，例如总和、平均值等。可以结合开窗函数使用，对分组后的数据进行统计。

例如，我们希望计算每位客户的累计订单金额和移动平均订单金额。下面的查询演示了如何使用开窗函数进行这些计算：

SELECT id, cust_id, order_date, total_amount,
       SUM(total_amount) OVER (PARTITION BY cust_id ORDER BY order_date) AS cumulative_sum,
       AVG(total_amount) OVER (PARTITION BY cust_id ORDER BY order_date) AS moving_avg
FROM orders;

执行上述查询后，输出如下：

| id | cust_id | order_date | total_amount | cumulative_sum | moving_avg |
|----|---------|------------|--------------|----------------|------------|
| 1  | 101     | 2022-01-01 | 100.00       | 100.00         | 100.00     |
| 2  | 101     | 2022-01-02 | 200.00       | 300.00         | 150.00     |
| 3  | 102     | 2022-01-01 | 150.00       | 150.00         | 150.00     |
| 4  | 102     | 2022-01-03 | 120.00       | 270.00         | 135.00     |
| 5  | 103     | 2022-01-02 | 180.00       | 180.00         | 180.00     |
| 6  | 103     | 2022-01-03 | 300.00       | 480.00         | 240.00     |

可以看到，每位客户的累计订单金额和移动平均订单金额都得到了计算。