MySQL 8.0窗口函数ROW_NUMBER分区去重与排名统计实战

窗口函数ROW_NUMBER解决分组去重的经典场景

MySQL 8.0窗口函数的引入彻底改变了分组去重和排名统计的写法。此前需要用子查询+自连接或用户变量模拟的复杂逻辑，现在用ROW_NUMBER()一行即可实现。窗口函数的核心语法结构为函数名() OVER (PARTITION BY 分组列 ORDER BY 排序列)，PARTITION BY定义分组边界，ORDER BY定义组内排序规则，两者配合实现精准的分组排名。

最常见的分组去重场景：每个用户取最近一条登录记录。

-- 传统写法：子查询+自连接
SELECT t1.*
FROM user_login t1
INNER JOIN (
    SELECT user_id, MAX(login_time) AS max_time
    FROM user_login
    GROUP BY user_id
) t2 ON t1.user_id = t2.user_id AND t1.login_time = t2.max_time;

-- 窗口函数写法
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_time DESC) AS rn
    FROM user_login
) t
WHERE rn = 1;

窗口函数写法的执行计划更简洁，MySQL只需一次排序扫描即可完成分组排名，而自连接写法需要两次表扫描和一次内连接。百万级数据测试中，窗口函数方案的执行时间约为自连接方案的40%-60%。

ROW_NUMBER与RANK、DENSE_RANK的差异选择

三种排名函数在处理并列值时的行为不同，选择错误会导致统计结果偏差：

-- 示例数据：学生成绩排名
-- student  score
-- 张三     95
-- 李四     95
-- 王五     90
-- 赵六     85

SELECT student, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_val
FROM student_scores;

-- 结果：
-- 张三  95  row_num=1  rank=1  dense_rank=1
-- 李四  95  row_num=2  rank=1  dense_rank=1
-- 王五  90  row_num=3  rank=3  dense_rank=2
-- 赵六  85  row_num=4  rank=4  dense_rank=3

ROW_NUMBER始终递增，适合分组去重（每组取一条）；RANK并列跳号，适合竞赛排名（并列第1，下一名第3）；DENSE_RANK并列不跳号，适合分档统计（第1档2人，第2档1人）。实际业务中选错排名函数是常见Bug来源，建议在SQL注释中明确标注排名逻辑。

多级分区与移动聚合窗口实战

窗口函数的PARTITION BY支持多列分区，ORDER BY支持多列排序，可应对复杂分组场景：

-- 每个部门每个岗位薪资排名
SELECT 
    department, position, name, salary,
    ROW_NUMBER() OVER (
        PARTITION BY department, position 
        ORDER BY salary DESC
    ) AS dept_pos_rank,
    -- 累计薪资占比
    salary / SUM(salary) OVER (
        PARTITION BY department 
        ORDER BY salary DESC 
        ROWS UNBOUNDED PRECEDING
    ) AS salary_pct
FROM employees;

移动聚合窗口（Window Frame）通过ROWS BETWEEN ... AND ...定义聚合范围，常用于移动平均、累计求和等场景：

-- 7日移动平均日活
SELECT 
    date,
    dau,
    AVG(dau) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS dau_ma7,
    -- 环比增长率
    (dau - LAG(dau, 1) OVER (ORDER BY date)) 
        / LAG(dau, 1) OVER (ORDER BY date) AS dau_growth_rate
FROM daily_stats;

窗口函数的性能优化需关注两点：一是PARTITION BY列应建立索引，避免全表排序；二是Window Frame的范围不宜过大，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（累计聚合）可利用排序扫描优化，ROWS BETWEEN 999 PRECEDING AND CURRENT ROW（大范围移动窗口）需要额外的缓冲区维护。对于日活级别的移动聚合，7日窗口的性能远优于30日窗口，差距可达5倍以上。