MySQL窗口函数ROW_NUMBER与RANK的区别与分页应用

基本概念与区别

认识 ROW_NUMBER和RANK这两个窗口函数时，首先需要了解它们都是用来为结果集中的行分配排名号的工具。

区分 这两个函数的关键点在于处理相同值时的行为：

• ROW_NUMBER：无论值是否相同，都会为每行分配一个唯一的连续排名号。

• RANK：当值相同时，会分配相同的排名，并且会跳过后续的排名号（例如，如果有两行都是第1名，下一行将是第3名）。

-- ROW_NUMBER示例
SELECT 
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num
FROM sales;

-- RANK示例
SELECT 
    product_name,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS rank_num
FROM sales;

理解 这种区别对后续的分页应用至关重要。当处理有并列排名的情况时，选择正确的函数可以显著影响分页结果。

实战场景与代码实现

创建 一个示例表格，用于演示这两个函数的使用：

CREATE TABLE student_scores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50),
    subject VARCHAR(50),
    score DECIMAL(5,2)
);

INSERT INTO student_scores (student_name, subject, score) VALUES
('Alice', 'Math', 95.50),
('Bob', 'Math', 88.75),
('Charlie', 'Math', 95.50),
('David', 'Math', 82.30),
('Eve', 'Math', 95.50),
('Frank', 'Math', 78.90);

ROW_NUMBER的应用

使用 ROW_NUMBER函数为每个学生成绩分配唯一排名：

SELECT 
    student_name,
    subject,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS score_rank
FROM student_scores
WHERE subject = 'Math';

查看 执行结果，即使有相同分数，每行也会有唯一的排名号：

RANK的应用

使用 RANK函数为每个学生成绩分配排名，相同分数获得相同排名：

SELECT 
    student_name,
    subject,
    score,
    RANK() OVER (ORDER BY score DESC) AS score_rank
FROM student_scores
WHERE subject = 'Math';

查看 执行结果，相同分数的学生获得相同排名，且后续排名会跳过：

分页应用

使用ROW_NUMBER实现分页

应用 ROW_NUMBER实现传统的分页逻辑：

WITH NumberedScores AS (
    SELECT 
        student_name,
        subject,
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS score_rank
    FROM student_scores
    WHERE subject = 'Math'
)
SELECT 
    student_name,
    subject,
    score
FROM NumberedScores
WHERE score_rank BETWEEN 2 AND 4;

理解 这个查询将获取排名在第2到第4的学生记录。注意，这里的排名是基于ROW_NUMBER的，因此即使有相同分数的记录，也会被视为不同的排名。

使用RANK实现分页

应用 RANK实现处理并列排名的分页逻辑：

WITH RankedScores AS (
    SELECT 
        student_name,
        subject,
        score,
        RANK() OVER (ORDER BY score DESC) AS score_rank
    FROM student_scores
    WHERE subject = 'Math'
)
SELECT 
    student_name,
    subject,
    score
FROM RankedScores
WHERE score_rank BETWEEN 2 AND 4;

注意 这里的分页结果可能与ROW_NUMBER不同，因为RANK会处理并列排名的情况。

实际应用中的选择

分析 两种函数在不同场景下的适用性：

1. ROW_NUMBER适用场景：

   • 需要严格区分每一行
   • 不需要考虑并列排名的情况
   • 需要确定数量的结果（如"第10到20条记录"）

2. RANK适用场景：

   • 需要反映实际排名情况
   • 需要处理并列排名
   • 结果数量可能超出预期（如"排名前5名"可能返回超过5条记录）

优化 分页查询时，考虑使用以下技巧：

-- 使用ROW_NUMBER处理固定数量的分页
WITH NumberedScores AS (
    SELECT 
        student_name,
        subject,
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS score_rank
    FROM student_scores
    WHERE subject = 'Math'
)
SELECT 
    student_name,
    subject,
    score
FROM NumberedScores
WHERE score_rank > (页码 * 每页条数) 
  AND score_rank <= ((页码 + 1) * 每页条数);

-- 使用RANK获取特定排名区间的记录
WITH RankedScores AS (
    SELECT 
        student_name,
        subject,
        score,
        RANK() OVER (ORDER BY score DESC) AS score_rank
    FROM student_scores
    WHERE subject = 'Math'
)
SELECT 
    student_name,
    subject,
    score
FROM RankedScores
WHERE score_rank BETWEEN 3 AND 5;

性能优化建议

考虑 窗口函数的性能影响，特别是处理大数据集时：

1. 添加适当的索引：确保用于排序的列上有索引。

-- 为排序列创建索引
CREATE INDEX idx_student_scores_score ON student_scores(score);

2. 限制结果集：在窗口函数前使用WHERE子句减少处理的数据量。

-- 先筛选再应用窗口函数
SELECT 
    student_name,
    subject,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS score_rank
FROM student_scores
WHERE subject = 'Math' AND score > 80; -- 先筛选分数大于80的记录

3. 使用更高效的窗口函数：根据业务需求选择最适合的窗口函数，避免不必要的计算。

高级应用场景

处理 复杂的分页逻辑，比如多级排序：

-- 多级排序示例
WITH NumberedScores AS (
    SELECT 
        student_name,
        subject,
        score,
        ROW_NUMBER() OVER (ORDER BY subject, score DESC) AS score_rank
    FROM student_scores
)
SELECT 
    student_name,
    subject,
    score
FROM NumberedScores
WHERE score_rank BETWEEN 1 AND 3;

组合 使用多个窗口函数获取更丰富的结果：

-- 组合使用多个窗口函数
SELECT 
    student_name,
    subject,
    score,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS subject_rank,
    PERCENT_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS percent_rank
FROM student_scores;

实现 复杂的排行榜分页逻辑：

-- 实现排行榜分页
WITH RankedScores AS (
    SELECT 
        student_name,
        subject,
        score,
        RANK() OVER (ORDER BY score DESC) AS overall_rank,
        COUNT(*) OVER (PARTITION BY subject) AS subject_count,
        RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS subject_rank
    FROM student_scores
)
SELECT 
    student_name,
    subject,
    score,
    overall_rank,
    subject_rank,
    ROUND((subject_rank / subject_count) * 100, 2) AS percentile
FROM RankedScores
WHERE overall_rank BETWEEN 1 AND 10;

掌握 这些技巧可以让你在各种分页场景中游刃有余，无论是简单的排行榜还是复杂的多维度排名系统。