MySQL窗口函数实战：用ROW_NUMBER()和SUM() OVER()解决复杂统计案例

一、当SQL遇上窗口函数

"为什么同样的统计需求，别人用3行SQL就能实现，我却要写30行嵌套查询？" 这是许多SQL开发者都曾有过的困惑。在后端程序员日常工作中，排名统计、累计计算、分组对比等需求就像顽固的"钉子户"，直到我们遇见MySQL 8.0带来的窗口函数——这个数据库领域的"瑞士军刀"，彻底改变了我们处理复杂统计的方式。

今天小编通过三个真实业务场景，手把手教大家用ROW_NUMBER()和SUM() OVER()两大神器，轻松攻克那些曾让你头疼的数据库统计难题！

二、窗口函数基础：数据分析的透视镜

2.1 什么是窗口函数？

窗口函数（Window Function）是一种特殊类型的SQL函数，它能够在保持原有行数据完整性的同时，对数据集的特定子集（称为"窗口"）进行计算。就像给数据安装了一面"透视镜"，既能看到整体数据全貌，又能聚焦局部细节。

2.2 与普通聚合函数的区别

<!--br {mso-data-placement:same-cell;}--> td {white-space:nowrap;border:0.5pt solid #dee0e3;font-size:10pt;font-style:normal;font-weight:normal;vertical-align:middle;word-break:normal;word-wrap:normal;}

2.3 核心语法结构

函数名() OVER (
    [PARTITION BY 分区字段] 
    [ORDER BY 排序字段]
    [ROWS/RANGE 窗口范围]
)

• PARTITION BY：将数据划分为多个窗口（类似GROUP BY）
• ORDER BY：决定窗口内的数据排序方式
• ROWS/RANGE：定义窗口范围（前N行/后N行/当前行等）

2.4 常用窗口函数清单

<!--br {mso-data-placement:same-cell;}--> td {white-space:nowrap;border:0.5pt solid #dee0e3;font-size:10pt;font-style:normal;font-weight:normal;vertical-align:middle;word-break:normal;word-wrap:normal;}

2.5 MySQL版本要求

• MySQL 8.0+ 原生支持窗口函数
• MySQL 5.7及以下版本可通过变通方法模拟部分功能
• 执行 SELECT VERSION(); 查看数据库版本

三、ROW_NUMBER()：排名统计的终极方案

场景1：销售团队业绩龙虎榜

-- 创建示例表
CREATE TABLE sales (
    sales_id INT PRIMARY KEY,
    salesperson VARCHAR(20),
    region VARCHAR(10),
    amount DECIMAL(10,2),
    sale_date DATE
);

-- 插入测试数据
INSERT INTO sales VALUES
(1, '张三', '华东', 15800, '2023-06-01'),
(2, '李四', '华南', 24500, '2023-06-02'),
(3, '王五', '华东', 18200, '2023-06-03'),
(4, '赵六', '华北', 31500, '2023-06-04'),
(5, '魏七', '华东', 22700, '2023-06-05');

-- 按地区分区排名
SELECT 
    salesperson,
    region,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
FROM sales;

执行结果：

技巧延伸：

• 分页查询优化：WHERE region_rank BETWEEN 6 AND 10实现高效分页
• 去重处理：通过ROW_NUMBER() = 1筛选最新记录
• TOP N统计：结合CTE公用表达式进行多层级排名

二、SUM() OVER()：累计统计的魔法棒

场景2：电商平台GMV趋势分析

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_amount DECIMAL(10,2),
    order_date DATE
);

-- 插入测试数据
INSERT INTO orders VALUES
(1, 101, 1500, '2023-06-01'),
(2, 102, 2300, '2023-06-01'),
(3, 101, 800, '2023-06-02'),
(4, 103, 4500, '2023-06-03'),
(5, 102, 1200, '2023-06-03');

-- 按日期累计统计
SELECT 
    order_date,
    SUM(order_amount) OVER (ORDER BY order_date) AS running_total,
    SUM(order_amount) OVER (ORDER BY order_date 
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3d
FROM orders
GROUP BY order_date;

执行结果：

高级用法：

• 移动平均线：ROWS BETWEEN 6 PRECEDING AND CURRENT ROW实现7日均线
• 同期对比：SUM() OVER (PARTITION BY MONTH(order_date))按月分区
• 累计占比：结合LAG函数计算增长率

三、组合技：窗口函数综合实战

场景3：人力资源薪酬分析

-- 创建员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(20),
    department VARCHAR(20),
    salary DECIMAL(10,2),
    hire_date DATE
);

-- 插入测试数据
INSERT INTO employees VALUES
(1, '陈强', '技术部', 25000, '2020-03-15'),
(2, '李娜', '市场部', 18000, '2021-07-22'),
(3, '王伟', '技术部', 32000, '2019-11-05'),
(4, '张敏', '财务部', 21000, '2022-02-18'),
(5, '赵刚', '市场部', 19500, '2020-09-30');

-- 多维度分析
SELECT 
    department,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    salary/SUM(salary) OVER (PARTITION BY department) AS salary_ratio
FROM employees;

执行结果：

业务价值：

1. 快速识别部门薪资TOP N员工
2. 直观查看部门人力成本构成
3. 计算员工薪资在部门中的占比
4. 为薪酬调整提供数据支持

四、避坑指南与性能优化

常见问题解决方案：

1. 重复排名问题：改用DENSE_RANK()或RANK()
   代码语言：javascript

   复制

   SELECT 
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
   FROM employees;

2. 动态窗口设置：使用RANGE模式处理日期区间
   代码语言：javascript

   复制

   SUM(sales) OVER (ORDER BY sale_date 
                   RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW)

3. 性能优化技巧：
   1. 优先使用ROWS子句指定物理行
   2. 在PARTITION BY字段上建立索引
   3. 避免在窗口函数中使用复杂表达式

总结

通过以上内容，相信大家已经感受到窗口函数在数据分析中的强大威力，大大降低了SQL编写的难度。大家是否有其他更好的使用场景欢迎评论区沟通交流！