9.6 窗口函数：排名、聚合、偏移类窗口函数用法与场景

窗口函数（Window Function）是 MySQL 8.0 引入的重量级新特性，它解决了一个长久以来的痛点：在不改变结果集行数的情况下，对每一行进行分组相关的计算。以往你需要借助子查询、临时表甚至应用层代码才能实现的复杂逻辑，现在往往一条 SQL 就能干净利落地完成。

9.6.1 窗口函数是什么、为什么要用

普通的聚合函数（如 SUM、AVG）配合 GROUP BY，会把多行合并为一行输出，你想在保留原始明细的同时看到分组统计，就必须用子查询或者联表，这既难写又难读。窗口函数的设计思路完全不同：它也是分组计算，但计算结果作为新的一列附加到每一行上，原始行的数量不变。

举个例子：你想查询每个员工的薪资，同时显示其所在部门的平均薪资。传统的做法是子查询：

SELECT 
  emp_name,
  dept_id,
  salary,
  (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) AS dept_avg
FROM employees e;

而窗口函数的写法是：

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

PARTITION BY dept_id 定义了分组的依据（称为“窗口”），计算结果直接挂载到每个员工的行上，SQL 更简洁，执行效率也往往更好（减少子查询的嵌套循环）。

9.6.2 基本语法与执行顺序

窗口函数的通用结构是：

函数名([参数]) OVER (
  [PARTITION BY 列名1, 列名2, ...]
  [ORDER BY 列名3 [ASC|DESC], ...]
  [frame_clause]
)

PARTITION BY：对行进行分组，类似于 GROUP BY，但不合并行。省略时，整个结果集视为一个窗口。
ORDER BY：定义窗口内行的排序顺序，很大程度上影响排名类、偏移类函数的行为。
frame_clause（框架子句）：用于聚合类窗口函数，指定计算时包含哪些行。默认窗口框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从分区第一行到当前行）。

在 SQL 的执行顺序中，窗口函数是在 WHERE、GROUP BY、HAVING 之后，ORDER BY、LIMIT 之前执行的。这意味着你可以在 WHERE 里过滤掉不需要的行，然后用窗口函数在剩余的行上计算；窗口函数的结果也可以在外层的 ORDER BY 或 LIMIT 中使用。

9.6.3 排名类窗口函数

排名函数最常见的三类：

ROW_NUMBER()：为窗口内的每一行分配一个唯一的连续编号，即使排序字段相同也递增。典型场景：实现各类“第几名”或分页去重。
RANK()：值相同时同序号，后面的序号会出现跳跃。例如两个并列第一，下一个就是 3（没有 2）。
DENSE_RANK()：值相同时同序号，但序号连续，不跳跃。两个并列第一，下一个是 2。

实际场景1：每个部门内按薪资排名

SELECT
  emp_name,
  dept_id,
  salary,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

输出每一行员工，并在最后一列标记他在所属部门的薪资排名（使用 RANK，薪资相同的并列，下一名会跳号）。

实际场景2：取每个分类下最贵的 N 件商品

通过子查询结合 ROW_NUMBER()，可以轻松实现“分组 TopN”：

SELECT * FROM (
  SELECT
    product_id,
    category_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM products
) t
WHERE rn <= 3;

这在分页或排行榜需求中极为常用。重要的是，ROW_NUMBER() 在这里保证了每个分类内行号唯一，即使价格相同也不会并列。

实际场景3：连续签到或连续登陆的计数

利用 ROW_NUMBER() 和日期差值的技巧，可以找出连续日期的序列（常被称为“岛屿问题”），这是面试和实际分析中的经典应用。

9.6.4 聚合类窗口函数

所有普通的聚合函数（SUM、AVG、COUNT、MAX、MIN 等）都可以作为窗口函数使用，结合 OVER 子句。它们的核心价值在于保持行的粒度同时提供聚合信息。

典型场景：计算累计值和移动平均

累计求和：常用于财务报表，比如每月营收的累计总和。

SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly_sales;

这里 ORDER BY month 意味着窗口按月份排序，默认框架是从开头到当前行，因此每行得到的是截至当月的总和。

移动平均：比如计算每件商品最近 7 天的平均销量，可以用 AVG 配合指定行范围实现。例如：

SELECT
  sale_date,
  product_id,
  quantity,
  AVG(quantity) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7days
FROM sales;

注意这里使用了 ROWS 框架，精确控制为当前行及前 6 行的平均，而不是从分区开头截止当前行。

需要注意：聚合窗口函数最频繁的问题是不加 ORDER BY 导致整个分区所有行得到相同的值，或者框架定义不对导致累计不符合预期。实际使用时要非常清楚排序和框架的默认行为。

9.6.5 偏移类窗口函数

偏移类窗口函数用于访问同一分区内“前一行”或“后一行”的数据，而无需自关联。最常用的两个：

LAG(列, N, 默认值)：返回当前行往前第 N 行的值。
LEAD(列, N, 默认值)：返回当前行往后第 N 行的值。
FIRST_VALUE(列) 和 LAST_VALUE(列)：获取窗口内第一行和最后一行的值。

场景1：计算相邻行的差值（环比增长）

SELECT
  date,
  visit_count,
  LAG(visit_count, 1) OVER (ORDER BY date) AS prev_visit,
  visit_count - LAG(visit_count, 1) OVER (ORDER BY date) AS growth
FROM site_traffic;

直接用 LAG 取上一期的数据，直接相减得到环比变化，无需自关联和子查询。

场景2：获取用户的最后一条购买记录

LAST_VALUE 需要配合窗口框架才能正确工作，因为默认框架是到当前行，看到的是当前行而不是分区最后一行。通常我们写成：

SELECT
  user_id,
  order_date,
  LAST_VALUE(order_date) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_date
FROM orders;

或者更简便地直接用 MAX(order_date) OVER (PARTITION BY user_id) 替代，但偏移函数在顺序敏感的场景更加灵活。

场景3：时间间隔分析

LAG 可以轻松计算两次事件之间的时间差，例如用户连续两次登录的时间间隔，这对活跃度分析极其有用。

9.6.6 使用窗口函数的注意事项

MySQL 8.0 才支持，如果还在 5.7，请尽快升级或寻找奇技淫巧（如使用变量模拟）。
不要滥用：窗口函数虽然强大，但复杂的窗口计算仍会消耗较多内存（尤其 PARTITION BY 粒度很大时），执行计划使用 Using temporary 是常态，未必比精心优化后的子查询更快。
命名窗口：MySQL 8.0 允许使用 WINDOW w AS (PARTITION BY ... ORDER BY ...) 前置定义窗口，避免重复代码。例如：

  SELECT
    emp_name,
    dept_id,
    salary,
    RANK() OVER w AS rk,
    AVG(salary) OVER w AS avg_sal
  FROM employees
  WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);

与普通聚合区分：窗口函数不能直接用在 WHERE、HAVING 或 GROUP BY 中，因为它们的计算在 WHERE 之后。要筛选窗口函数的结果，必须使用子查询或者 CTE。

总之，窗口函数是让 SQL 变得更聪明、更简洁的利器，把以往需要多步操作或应用代码拼接的逻辑移入数据库内部完成，提升了开发效率和代码可维护性。当你遇到“分组但不合并”的需求时，第一时间就应想到窗口函数。