【重学MySQL】八十九、窗口函数的分类和使用

一、窗口函数分类

1. 聚合类窗口函数

• SUM()：计算窗口内数值总和
• AVG()：计算窗口内数值平均值
• COUNT()：统计窗口内行数
• MAX()/MIN()：获取窗口内最大/最小值

2. 排名类窗口函数

• ROW_NUMBER()：生成唯一行号（1,2,3…）
• RANK()：允许并列的排名（1,1,3…）
• DENSE_RANK()：密集排名（1,1,2…）
• NTILE(n)：将数据分为n个桶

3. 分布类窗口函数

• PERCENT_RANK()：计算百分比排名
• CUME_DIST()：计算累积分布

4. 前后行类窗口函数

• LAG(col, n)：获取前n行数据
• LEAD(col, n)：获取后n行数据

5. 头尾函数

• FIRST_VALUE(col)：获取窗口首行值
• LAST_VALUE(col)：获取窗口末行值

二、窗口函数语法

函数名() OVER (
  [PARTITION BY 分组字段]
  [ORDER BY 排序字段 [ASC|DESC]]
  [frame_clause]
)

• PARTITION BY：将结果集拆成多个分区，各自独立计算。
• ORDER BY：决定窗口内的顺序。
• ROWS|RANGE：限定窗口的物理/逻辑边界。

三、典型使用场景

场景1：分组聚合

-- 计算每个部门的平均工资
SELECT 
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

场景2：排名计算

-- 按成绩降序排名（允许并列）
SELECT 
  student_id,
  score,
  RANK() OVER (ORDER BY score DESC) AS ranking
FROM exam_results;

场景3：移动平均

-- 计算3天移动平均温度
SELECT 
  date,
  temperature,
  AVG(temperature) OVER (
    ORDER BY date 
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg
FROM weather_data;

场景4：前后值对比

-- 对比本月与上月销售额
SELECT 
  month,
  sales,
  LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales,
  sales - LAG(sales, 1) OVER (ORDER BY month) AS growth
FROM monthly_sales;

四、高级技巧

1. 多级排序：PARTITION BY + ORDER BY 组合使用
2. 窗口框架控制：使用 ROWS/RANGE 定义精确计算范围
3. 命名窗口：通过 WINDOW 子句复用窗口定义

窗口函数在OLAP分析、趋势计算、异常检测等场景中表现优异，可显著简化复杂查询逻辑。