MySQL中的ROW_NUMBER窗口函数简单了解下

ROW_NUMBER() 是 MySQL8引入的窗口函数之一，它为查询结果集中的每一行分配一个唯一的顺序号（行号）。这个顺序号是基于窗口函数的 ORDER BY 子句进行排序的，可以根据指定的排序顺序生成连续的整数值。

ROW_NUMBER() 在分页、去重、分组内排序等场景中非常有用。

本文涉及到的脚本测试请在个人测试库进行。

使用场景

• 分页查询：使用 ROW_NUMBER() 可以生成每行的序号，结合 WHERE 或 LIMIT 子句实现高效的分页查询。尤其是在没有 OFFSET 支持的情况下，ROW_NUMBER() 允许你在分页时进行灵活的排序。
• 去除重复数据：可以利用 ROW_NUMBER() 来给每一行打上唯一标识，之后选择每组的第一行，从而有效地去除重复数据。
• 分组内排序：可以按组对数据进行排序，并为每个组中的行分配一个行号。这个场景通常用于比如给每个订单中的商品按价格排序，并为每个订单挑选排名第一的商品。
• 数据排名：使用 ROW_NUMBER() 可以为查询结果中的数据进行排名，适用于例如学生成绩排名、销售业绩排名等场景。

语法

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

• PARTITION BY：可选，按指定字段分组。相同分组内的行号会重新从 1 开始。
• ORDER BY：指定排序字段，行号的生成顺序由此决定。

示例

假设有一个电商数据库，包含 orders 和 order_items 表，使用 ROW_NUMBER() 来展示几种常见场景。

示例 1：为每个订单中的商品按价格排名

可以为每个订单中的商品按价格进行排序，并为每个商品分配一个排名。

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE
);

-- 创建 order_items 表
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 插入数据
INSERT INTO orders (customer_name, order_date) VALUES
('Alice', '2024-10-01'),
('Bob', '2024-10-02'),
('Charlie', '2024-10-03');

INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
(1, 'Laptop', 1, 1000.00),
(1, 'Phone', 2, 500.00),
(1, 'Tablet', 1, 300.00),
(2, 'Headphones', 2, 100.00),
(2, 'Mouse', 1, 50.00),
(3, 'Smartwatch', 1, 150.00),
(3, 'Laptop', 1, 800.00);

查询：为每个订单中的商品按 unit_price 排序，给出排名

SELECT 
    oi.order_id, 
    oi.product_name, 
    oi.unit_price,
    ROW_NUMBER() OVER (PARTITION BY oi.order_id ORDER BY oi.unit_price DESC) AS `rank`
FROM order_items oi;

结果

在这个例子中，使用 ROW_NUMBER() 按照每个 order_id 对商品按 unit_price 从高到低排序，并为每个商品分配了一个行号（排名）。

如果只想获取每个订单中价格最高的商品，可以在查询外层再加一个 WHERE rank = 1 来筛选。

示例 2：去除重复数据

假设 order_items 表中有重复的记录，可以利用 ROW_NUMBER() 给每一行编号，然后只保留每组中第一个出现的记录（行号为 1）。

插入重复数据

INSERT INTO order_items (order_id, product_name, quantity, unit_price) VALUES
(1, 'Laptop', 1, 1000.00),  -- 重复记录
(2, 'Mouse', 1, 50.00),     -- 重复记录
(3, 'Smartwatch', 1, 150.00);

查询：去除重复记录

WITH ranked_items AS (
    SELECT 
        oi.order_item_id, 
        oi.order_id, 
        oi.product_name, 
        oi.unit_price,
        ROW_NUMBER() OVER (PARTITION BY oi.order_id, oi.product_name ORDER BY oi.order_item_id) AS rn
    FROM order_items oi
)
SELECT 
    order_item_id, 
    order_id, 
    product_name, 
    unit_price
FROM ranked_items
WHERE rn = 1;

在这个查询中，ROW_NUMBER() 根据 order_id 和 product_name 为每一组商品打上编号，PARTITION BY 确保每个订单中同一个商品只保留一次。WHERE rn = 1 确保每个分组只保留第一条记录，从而去除了重复的商品条目。

示例 3：分页查询

假设需要分页展示订单项，每页展示 2 条数据。可以使用 ROW_NUMBER() 来为查询结果生成行号，并结合 WHERE 子句限制显示特定页的数据。

查询：分页显示第二页数据（每页显示 2 条）

WITH ranked_items AS (
    SELECT 
        oi.order_item_id, 
        oi.order_id, 
        oi.product_name, 
        oi.unit_price,
        ROW_NUMBER() OVER (ORDER BY oi.order_item_id) AS rn
    FROM order_items oi
)
SELECT 
    order_item_id, 
    order_id, 
    product_name, 
    unit_price
FROM ranked_items
WHERE rn BETWEEN 3 AND 4;

结果

在这个分页查询中，ROW_NUMBER() 为查询结果集中的每一行分配了一个行号，然后通过 WHERE rn BETWEEN 3 AND 4 获取第 2 页的结果（假设每页 2 条数据）。

总结

ROW_NUMBER() 在 MySQL 中是一个强大的窗口函数，具有以下几个主要用途：

• 分页查询：通过生成行号来实现高效分页。
• 去重：利用分组和行号，可以去除重复数据。
• 分组排序：对每个分组内的数据进行排序并生成排名。
• 数据排名：计算排名或为数据按某种规则分配顺序。

MySQL 8.0 引入的窗口函数使得许多复杂的查询变得更加简洁和高效，特别是在处理排名、去重和分页等场景时。

关于作者

来自全栈程序员nine的探索与实践，持续迭代中。