SQL 窗口函数与聚合函数的根本差异_技术教程

窗口函数不压缩行数而聚合函数会，前者每行输出对应输入行，后者结果行数≤原表；窗口函数支持帧定义、排序敏感计算及NULL精细控制，聚合函数仅依赖分组边界。

窗口函数不会压缩行数，聚合函数会

这是最直观、也最容易被忽略的区别。执行 GROUP BY 后的 SUM()、COUNT() 等聚合函数，结果集行数一定 ≤ 原表行数；而 SUM() OVER (...) 这类窗口函数，输出行数严格等于输入行数——每行都带着自己所在窗口的计算结果。

常见错误现象：想在明细报表里加一列「部门销售额占比」，却误用 GROUP BY dept + SUM(sales)，结果只剩几行，没法和原始订单行对齐。

聚合函数必须搭配 GROUP BY（或全表无分组），否则报错：ERROR: column "x" must appear in the GROUP BY clause
窗口函数可直接出现在 SELECT 中，不改变原有行结构，也不强制要求 GROUP BY
同一个查询里可以混用：比如 COUNT(*) OVER (PARTITION BY dept) 统计部门人数，同时保留每人姓名、订单号等明细字段

窗口函数支持帧定义（ROWS/RANGE），聚合函数不支持

窗口函数能精确控制“当前行参考哪些邻近行”，比如“过去7天的平均销量”或“从第一行到当前行的累计和”。聚合函数没有这个能力——它只认分组边界，不认顺序或位置。

使用场景：时间序列分析、滚动统计、排名连续性处理（如剔除并列后取 Top 10）。

AVG(sales) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 是合法的
AVG(sales) GROUP BY dept ORDER BY order_date ROWS BETWEEN ... 语法错误，GROUP BY 后不接受 ROWS 子句
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 对时间字段更安全（自动合并相同值），但可能比 ROWS 慢，尤其数据有重复时间戳时

NULL 值处理逻辑不同

聚合函数默认跳过 NULL（COUNT(*) 除外），这没问题；但窗口函数在帧内遇到 NULL 时，行为更隐蔽——它仍计入帧范围，只是参与计算时被忽略，可能导致“窗口大小看似固定，实际有效行数浮动”。

例如用 ROW_NUMBER() OVER (ORDER BY score DESC) 排名，score IS NULL 的行会被排到最后，但具体位置取决于 NULLS LAST 设置；而 AVG() OVER (...) 遇到 NULL 不报错，但均值分母是去 NULL 后的行数，不是帧声明的行数。

COUNT(col) 和 COUNT(col) OVER (...) 都跳过 NULL，但前者返回单值，后者每行都返回同一组内的非空计数
想让窗口函数把 NULL 当 0 参与计算？得显式写 COALESCE(col, 0)，不能依赖默认行为
NTILE(4) OVER (...) 会把 NULL 分进某一个桶，但顺序由 ORDER BY 的 NULLS FIRST/LAST 决定，容易误判分布

性能开销模式完全不同

聚合函数通常触发一次哈希/排序分组，整体代价相对可控；窗口函数若带 ORDER BY 和大范围帧（如 UNBOUNDED PRECEDING），可能引发多次扫描或内存缓冲膨胀，尤其在未建索引的排序字段上。

容易踩的坑：在千万级订单表上直接跑 SUM(amount) OVER (ORDER BY create_time)，没索引时 PG 可能爆内存，MySQL 8.0 则可能退化为 N² 复杂度。

优先给 OVE R 子句中的 ORDER BY 字段建索引，特别是和 PARTITION BY 组合时（如 PARTITION BY user_id ORDER BY ts）
避免在子查询或视图里嵌套多层窗口函数，优化器未必能剪枝，中间结果集可能远超预期
LAG()/LEAD() 看似轻量，但如果 OFFSET 很大（如 LAG(x, 1000)）且无索引，性能下降明显