MySQL窗口函数与应用实践

窗口函数核心原理

窗口函数（Window Function）是SQL标准中定义的分析型函数，其核心特征在于计算时不会合并行数据，而是在保持原有行记录的同时进行跨行计算。这种特性使得我们能够在保留明细数据的基础上，实现复杂的统计分析需求。

与GROUP BY聚合操作的本质区别在于：

• 聚合函数：SUM()/AVG()等函数配合GROUP BY时，每个分组只返回一行结果
• 窗口函数：SUM() OVER()等窗口函数会为每行生成独立计算结果，保持原始行数不变

基础语法结构

SELECT 
    window_function(arg) OVER (
        [PARTITION BY partition_expression]
        [ORDER BY sort_expression [ASC|DESC]]
        [frame_clause]
    ) AS alias
FROM table

核心语法组件解析

1. PARTITION BY子句：
   数据分组的依据，类似GROUP BY但不会合并行。支持多字段组合分区：

   PARTITION BY department, job_level
   

2. ORDER BY子句：
   定义窗口内的排序规则，直接影响以下函数的行为：

   • 排名函数（ROW_NUMBER/RANK等）
   • 累计聚合（运行总计等）
   • 偏移函数（LAG/LEAD）

3. 窗口帧（Frame Clause）：
   定义当前行的计算范围，语法支持多种模式：

   ROWS BETWEEN 3 PRECEDING AND CURRENT ROW  -- 包含前3行和当前行
   RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW  -- 时间范围
   

函数分类与应用场景

（完整函数列表详见MySQL官方文档）

1. 聚合型窗口函数：

   SUM(sales) OVER(PARTITION BY region)  -- 区域销售总额
   AVG(score) OVER(ORDER BY date ROWS 6 PRECEDING)  -- 7日移动平均
   

2. 排名函数：

   SELECT 
       product_id,
       ROW_NUMBER() OVER(ORDER BY sales DESC) AS row_num,
       RANK() OVER(ORDER BY sales DESC) AS rank,
       DENSE_RANK() OVER(ORDER BY sales DESC) AS dense_rank
   FROM sales_data;
   

   输出示例：

   product_id	sales	row_num	rank	dense_rank
   P1001	5000	1	1	1
   P1002	5000	2	1	1
   P1003	4800	3	3	2

3. 偏移函数：

   LAG(price, 1) OVER(PARTITION BY stock ORDER BY trade_date)  -- 昨日股价
   LEAD(temperature, 3) OVER(ORDER BY recorded_time)  -- 3小时后的温度
   

4. 分布分析函数：

   CUME_DIST() OVER(ORDER BY score)  -- 累积分布
   NTILE(4) OVER(ORDER BY salary DESC)  -- 工资四分位划分
   

高级应用案例

1. 动态分组统计：

   SELECT 
       employee_id,
       department,
       salary,
       AVG(salary) OVER(PARTITION BY department) AS dept_avg,
       salary - AVG(salary) OVER(PARTITION BY department) AS diff_from_avg
   FROM employees;
   

2. 会话划分（Sessionization）：

   WITH event_sequence AS (
       SELECT 
           user_id,
           event_time,
           LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time) AS prev_time
       FROM user_events
   )
   SELECT 
       user_id,
       event_time,
       SUM(CASE WHEN TIMESTAMPDIFF(MINUTE, prev_time, event_time) > 30 THEN 1 ELSE 0 END) 
           OVER(PARTITION BY user_id ORDER BY event_time) AS session_id
   FROM event_sequence;
   

3. 时间序列分析：

   SELECT 
       trade_date,
       close_price,
       AVG(close_price) OVER(ORDER BY trade_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma5,
       AVG(close_price) OVER(ORDER BY trade_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS ma20
   FROM stock_prices;
   

性能优化策略

1. 索引设计原则：

   • 窗口函数使用的PARTITION BY和ORDER BY字段需要复合索引
   • 示例：对(department, hire_date)建立索引优化部门内的入职时间排序

2. 执行计划分析： 使用EXPLAIN查看窗口函数执行阶段：

   EXPLAIN 
   SELECT 
       id, 
       ROW_NUMBER() OVER(ORDER BY score DESC) 
   FROM students;
   

   重点关注：

   • 是否出现filesort
   • window函数是否产生临时表

3. 内存控制参数：

   [mysqld]
   windowing_use_high_precision = ON  # 高精度计算模式
   max_execution_time = 30000  # 防止复杂查询超时
   

常见问题解决方案

1. 处理重复值排序：

SELECT 
    product_id,
    sales,
    DENSE_RANK() OVER(ORDER BY sales DESC) AS sales_rank
FROM products;

2. 动态计算累计占比：

SELECT 
    date,
    revenue,
    SUM(revenue) OVER(ORDER BY date) / SUM(revenue) OVER() AS cumulative_ratio
FROM daily_sales;

3. 处理NULL值：

SELECT 
    COALESCE(department, 'Unknown') AS department,
    AVG(salary) OVER(PARTITION BY COALESCE(department, 'Unknown')) 
FROM employees;

版本兼容方案

对于MySQL 5.7及以下版本，可通过变量模拟部分窗口函数功能：

SET @rank = 0;
SELECT 
    @rank := @rank + 1 AS rank,
    name,
    score
FROM students
ORDER BY score DESC;

最佳实践建议

1. 明确业务需求：优先使用简单窗口函数，避免过度嵌套
2. 数据验证：对比窗口函数结果与传统join方式的差异
3. 渐进式开发：逐步增加窗口函数复杂度，及时验证中间结果
4. 监控资源使用：关注内存和临时表空间消耗