MySQL中的窗口函数

MySQL中的窗口函数

窗口函数（Window Functions）是MySQL 8.0及以上版本引入的高级分析功能，允许在结果集的特定"窗口"（行子集）上执行计算，不聚合结果，保留原始行数据。核心语法为：

<窗口函数> OVER (
  [PARTITION BY <列>] 
  [ORDER BY <列>] 
  [ROWS/RANGE <框架>]
)

---

核心功能分类

1. 排序类函数

   • ROW_NUMBER(): 唯一行号（无重复）

     SELECT name, salary, 
            ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
     FROM employees;
     

   • RANK(): 允许并列排名（如：1,2,2,4）
   • DENSE_RANK(): 密集排名（如：1,2,2,3）

2. 分布类函数

   • NTILE(n): 数据分桶（分成n组）

     SELECT name, salary,
            NTILE(4) OVER (ORDER BY salary) AS quartile
     FROM employees;
     

3. 偏移类函数

   • LAG(列, n): 获取前n行数据
   • LEAD(列, n): 获取后n行数据

     SELECT date, revenue,
            LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue
     FROM sales;
     

4. 聚合类函数

   • SUM(), AVG(), COUNT()等结合窗口使用

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

---

关键子句解析

1. PARTITION BY
   将数据分区（类似GROUP BY但不聚合），函数在每个分区独立计算：

   -- 计算每个部门的累计薪资
   SELECT department, name, salary,
          SUM(salary) OVER (PARTITION BY department ORDER BY hire_date) 
          AS running_total
   FROM employees;
   

2. ORDER BY
   定义分区内排序规则（影响RANK()、累计计算等）。

3. 框架子句（Frame Clause）
   定义窗口范围：

   • ROWS BETWEEN N PRECEDING AND M FOLLOWING: 物理行范围
   • RANGE BETWEEN ...: 逻辑值范围

   -- 计算近3行移动平均
   SELECT date, revenue,
          AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 
          AS moving_avg
   FROM sales;
   

---

典型应用场景

1. 排名计算

   SELECT product, sales,
          RANK() OVER (ORDER BY sales DESC) AS sales_rank
   FROM products;
   

2. 累计统计

   SELECT month, revenue,
          SUM(revenue) OVER (ORDER BY month) AS cumulative_rev
   FROM financials;
   

3. 同比/环比分析

   SELECT year_month, revenue,
          LAG(revenue, 12) OVER (ORDER BY year_month) AS prev_year_rev
   FROM sales;
   

---

注意事项

1. 仅支持MySQL 8.0+版本
2. 不能直接用于WHERE/GROUP BY子句
3. 性能优化：为PARTITION BY/ORDER BY列建索引
4. 默认窗口范围：
   • 有ORDER BY：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   • 无ORDER BY：整个分区

窗口函数极大简化了复杂分析查询，替代了传统自连接或子查询方案，显著提升可读性和性能。