MySQL如何使用窗口函数排名/分组/累计计算实战案例-mysql教程-PHP中文网

MySQL如何使用窗口函数排名/分组/累计计算实战案例

裘德小鎮的故事

发布： 2025-06-22 14:45:02

原创

926人浏览过

窗口函数是在sql中实现跨行计算的关键工具，它允许基于当前行与其他相关行进行计算，避免了复杂嵌套查询。语法为function_name() over (partition by column1 order by column2)，其中partition by用于分组，order by用于排序；若无partition by，则全表视为一个分区。其核心应用场景包括：1. 排名，如rank()、dense_rank()、row_number()实现部门内薪资排名；2. 分组计算，如通过avg(salary) over (partition by department)计算部门平均薪资并比较个体差异；3. 累计计算，如sum(sales) over (order by date)实现销售额逐日累加。与group by不同的是，窗口函数不减少结果集行数，仅新增计算列。性能优化方面需注意索引创建、避免过度使用、正确设置数据类型、合理定义窗口大小及必要时重写查询。支持的聚合函数包括avg、sum、min、max、count、排名函数及取值函数如lag、lead等。窗口函数广泛应用于用户行为分析、金融数据处理、电商分析和日志统计等领域，显著提升数据分析效率和灵活性。

MySQL如何使用窗口函数排名/分组/累计计算实战案例

窗口函数，说白了，就是在SQL查询中，给你一种能够跨行计算的能力。不再局限于当前行，而是可以访问与当前行相关的其他行。排名、分组、累计计算，这些以前得绕弯子才能搞定的事情，现在一条SQL就能优雅地解决。

解决方案

窗口函数的基本语法是 function_name() OVER (PARTITION BY column1 ORDER BY column2)。PARTITION BY 用于分组，ORDER BY 用于排序。如果没有 PARTITION BY，则整个结果集被视为一个分区。

实战案例：排名

假设我们有一张 employees 表，包含 id, name, department, salary 四个字段。我们要按照部门对员工的薪水进行排名。

SELECT
    id,
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM
    employees;

登录后复制

这个SQL语句会返回一个结果集，其中 salary_rank 列就是每个员工在其部门内的薪水排名。RANK() 函数会跳过重复的排名。如果想不跳过重复排名，可以使用 DENSE_RANK() 函数。ROW_NUMBER() 函数则会为每一行分配一个唯一的序号，即使薪水相同。

实战案例：分组

窗口函数的分组功能体现在 PARTITION BY 子句上。上面的排名案例实际上已经展示了分组的用法。我们可以利用分组进行更复杂的计算。比如，计算每个部门的平均薪水，并与每个员工的薪水进行比较。

SELECT
    id,
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as avg_salary_department,
    salary - AVG(salary) OVER (PARTITION BY department) as salary_difference
FROM
    employees;

登录后复制

这里，AVG(salary) OVER (PARTITION BY department) 计算的是每个部门的平均薪水，然后我们用每个员工的薪水减去这个平均薪水，得到 salary_difference，可以用来衡量员工薪水在部门内的相对水平。

实战案例：累计计算

累计计算，也称为 running total，经常用于统计一段时间内的总销售额、总用户数等。

SELECT
    date,
    sales,
    SUM(sales) OVER (ORDER BY date ASC) as cumulative_sales
FROM
    sales_data;

登录后复制

这个SQL语句会计算每天的累计销售额。SUM(sales) OVER (ORDER BY date ASC) 会从第一天开始，逐天累加销售额。ORDER BY date ASC 指定了累加的顺序。如果想按月累加，可以把 ORDER BY date ASC 改为 ORDER BY YEAR(date), MONTH(date) ASC。

窗口函数与GROUP BY的区别？

GROUP BY 会将结果集分组，并对每个组进行聚合计算，最终每个组只返回一行结果。而窗口函数不会改变结果集的行数，它只是为每一行添加额外的列，这些列的值是基于窗口的计算结果。换句话说，GROUP BY 是分组聚合，而窗口函数是分组计算。

性能优化：如何避免窗口函数导致的性能问题？

窗口函数虽然强大，但如果使用不当，可能会导致性能问题。特别是在处理大数据集时。

索引优化： 确保 PARTITION BY 和 ORDER BY 子句中使用的列都有索引。这可以显著提高查询速度。
避免过度使用： 尽量避免在一个查询中使用过多的窗口函数。过多的窗口函数会增加计算复杂度，降低查询效率。
数据类型： 确保窗口函数中使用的列的数据类型是正确的。例如，如果对日期进行排序，应该使用 DATE 或 DATETIME 类型。
合理使用窗口大小： 某些窗口函数支持指定窗口大小，例如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。合理设置窗口大小可以提高性能。
查询重写： 在某些情况下，可以使用子查询或临时表来替代窗口函数，以提高性能。

窗口函数支持哪些聚合函数？

窗口函数支持的聚合函数包括：