还在写几十行子查询？窗口函数让你的SQL简洁10倍

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

很多数据分析师朋友问我：分组取前三、算累计占比、做移动平均，以前用子查询和自连接写几十行还容易错，有没有更简单的方法？有，窗口函数一行搞定。

1 名词解释

• 窗口函数：在一组与当前行相关的行（窗口）上执行计算，不合并行，保留原数据。
• 分区（PARTITION BY）：将数据分组，窗口函数在每个分组内独立计算。
• 排序（ORDER BY）：定义窗口内行的顺序，影响排名、累计等函数的计算结果。
• 框架（ROWS/RANGE）：定义窗口的边界，如 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW。

2 实际运用

2.1 三种排名函数

SELECT 
    product_id, category, sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn,
    RANK()       OVER (PARTITION BY category ORDER BY sales DESC) AS rk,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS dr
FROM products;

​
适用场景​：

• 分页取数据 → ROW_NUMBER()
• 比赛排名（允许并列但跳过名次） → RANK()
• 工资等级（并列不跳过） → DENSE_RANK()

2.2 累计和与累计占比（帕累托分析）

SELECT 
    product, sales,
    SUM(sales) OVER (ORDER BY sales DESC) AS running_total,
    SUM(sales) OVER (ORDER BY sales DESC) / SUM(sales) OVER () AS cum_pct
FROM products;

2.3 移动平均（MA3）

SELECT 
    date, sales,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM daily_sales;

2.4 同比/环比（LAG / LEAD）

SELECT 
    date, sales,
    LAG(sales, 1) OVER (ORDER BY date) AS prev_day_sales,
    (sales / LAG(sales, 1) OVER (ORDER BY date) - 1) * 100 AS growth_rate
FROM daily_sales;

2.5 分组内百分比

SELECT 
    category, product, sales,
    sales / SUM(sales) OVER (PARTITION BY category) AS pct_in_category
FROM products;

3 性能注意事项

• 窗口函数会在内存或磁盘中创建临时表，大量数据时注意监控 Created_tmp_disk_tables 状态。
• ORDER BY 会触发排序，如果窗口内不需要排序可以省略 ORDER BY 提升性能。
• MySQL 8.0及以上才支持窗口函数，低版本需要升级或用替代写法。

4 价值总结

• 学会窗口函数，你可以将几十行的子查询+自连接改写为一行，代码简洁且性能更优。
• 窗口函数能解决数据分析中的大部分分组统计、排名、累计计算需求，是SQL进阶的里程碑。
• 建议先从 ROW_NUMBER() 和 SUM() OVER() 入手，再逐步掌握 RANK()、LAG() 等高级函数。

小耶在手，SQL不愁。

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~