什么是 SQL 窗口函数？它和 GROUP BY 有什么区别？

简化版

窗口函数在相关行集合上计算排名、累计值或聚合值，但不会像 GROUP BY 那样把多行压缩成一行。它通过 OVER 中的 PARTITION BY、ORDER BY 和窗口框架定义计算范围，常用于组内排名、Top N、累计求和和环比分析。

详细版

SELECT employee_id,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
       ROW_NUMBER() OVER (
         PARTITION BY department_id ORDER BY salary DESC, employee_id
       ) AS rn
FROM employee;

查询仍为每名员工保留一行，同时附加部门平均工资和组内排名。若改用 GROUP BY department_id，每个部门只输出一行，员工明细会消失。

窗口函数逻辑上晚于 WHERE、GROUP BY、HAVING，所以要筛选 rn <= 3，通常需在外层查询中处理。

完整版教学

一、窗口函数的三个核心部分

SUM(amount) OVER (
  PARTITION BY customer_id
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

PARTITION BY：把结果行分区，类似分组边界，但不合并行；
ORDER BY：定义分区内顺序，排名、累计和、前后行访问依赖它；
窗口框架：进一步限定当前行参与计算的前后范围。

省略 PARTITION BY 时，整个结果集是一个分区；省略窗口内 ORDER BY 时，某些函数没有稳定的先后含义。

二、GROUP BY 与窗口函数的根本差异

-- 每个部门一行
SELECT department_id, AVG(salary)
FROM employee
GROUP BY department_id;

-- 每名员工一行，同时显示部门平均值
SELECT e.*, AVG(salary) OVER (PARTITION BY department_id)
FROM employee AS e;

前者改变结果粒度，后者保留明细粒度。选择时先问“最终一行代表什么”：一行代表一个部门就用聚合分组，一行仍代表一名员工但要带组内统计就用窗口函数。

三、ROW_NUMBER、RANK 和 DENSE_RANK

ROW_NUMBER()：每行序号唯一，并列值也继续编号；
RANK()：并列同名次，下一名次跳号，如 1、1、3；
DENSE_RANK()：并列同名次，下一名次不跳号，如 1、1、2。

若要每组严格取三行，常用 ROW_NUMBER()；若业务要求并列第三名都入选，应考虑 RANK() 或 DENSE_RANK()。排序条件最好加唯一列作为最终裁决，避免同值行的顺序不稳定。

四、为什么不能在 WHERE 直接筛窗口结果

SELECT *
FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (
           PARTITION BY department_id ORDER BY salary DESC, id
         ) AS rn
  FROM employee AS e
) AS ranked
WHERE rn <= 3;

WHERE 处理时窗口函数尚未计算，因此要增加查询层级。部分数据库提供 QUALIFY 扩展语法，但 MySQL、PostgreSQL 等常见场景仍使用派生表或 CTE，写通用答案时不能假设所有数据库都支持 QUALIFY。

五、默认窗口框架的陷阱

聚合函数带窗口内 ORDER BY 时，默认框架可能只覆盖到当前行及其同序值，而不是整个分区。累计和正需要这种行为；若想在每一行显示整个分区总额，可以省略窗口内排序，或显式写完整框架。

LAST_VALUE() 尤其容易受默认框架影响，得到“当前框架最后一行”而非“整个分区最后一行”。面试中应说明窗口框架与分区不是同一个概念。

六、常见误区与追问

能力	`GROUP BY`	窗口函数
是否保留明细行	否，每组一行	是，原始行仍保留
典型用途	汇总统计	排名、累计、组内占比
筛选阶段	可配合 `HAVING`	需外层查询筛窗口结果
粒度变化	改变结果粒度	不改变结果粒度

误区：窗口函数和 GROUP BY 都会把多行压成一行。 GROUP BY 改变结果粒度，窗口函数保留原始行，只是在每行旁边增加分区内计算结果。
误区：ROW_NUMBER、RANK、DENSE_RANK 没区别。 并列时 ROW_NUMBER 仍给唯一序号，RANK 会跳号，DENSE_RANK 不跳号。
误区：窗口函数可以直接写在 WHERE 里筛选。 窗口计算晚于 WHERE，要筛排名通常用子查询或 CTE 包一层。
追问：PARTITION BY 和 GROUP BY 有什么本质差异？ PARTITION BY 只定义窗口计算范围，不合并行；GROUP BY 定义输出粒度并聚合成每组一行。
追问：默认窗口框架为什么容易出错？ 带 ORDER BY 的聚合窗口可能默认到“当前行”为止，导致得到累计值而不是整个分区总值。
追问：什么时候优先用窗口函数？ 需要明细行同时带排名、累计、组内占比、前后行比较时，窗口函数比先聚合再回表连接更自然。

记忆钩子：GROUP BY 改粒度，窗口函数不改粒度；看到“明细行 + 组内指标”，优先想到窗口函数。

七、加强记忆

GROUP BY 会合并行，窗口函数保留行；PARTITION 划分计算区域，ORDER BY 决定区内顺序，窗口框架决定当前行到底看多远。组内排名、累计值和明细带汇总优先想到窗口函数，筛窗口结果要再套一层查询。

简化版

详细版