窗口函数可高效替代关联子查询，适用于累计值、移动平均、并列排名等场景，性能提升3–10倍；须注意RANK()与ROW_NUMBER()语义差异、ORDER BY的强制性、ROWS优于RANGE、窗口函数不可用于WHERE/HAVING等关键规则。
窗口函数替代关联子查询的典型场景
当报表需要对每行数据计算「当前分组内的累计值」「前后N行的移动平均」「排名但保留并列」时，用子查询或自连接往往导致全表扫描多次。窗口函数在单次扫描中完成这些计算，性能提升常达3–10倍。
常见错误是把 ROW_NUMBER() 和 RANK() 混用：前者强制唯一序号，后者对相同值给相同排名、跳过后续序号。做销售TOP10排行榜时若要求“并列第3名后是第5名”，必须用 RANK()；若要严格按出现顺序编号（如抽奖抽签），才用 ROW_NUMBER()。
- 子查询里写
WHERE order_date = (SELECT MAX(order_date) FROM orders)→ 改成MAX(order_date) OVER ()配合过滤 - 用
LEFT JOIN关联汇总表求每个客户的订单总数 → 直接COUNT(*) OVER (PARTITION BY customer_id) - 多个子查询分别算月均、环比、同比 → 全部合并进一个
SELECT，用不同OVER子句隔离窗口范围
ORDER BY 在窗口定义里的关键作用
没写 ORDER BY 的窗口（如 COUNT(*) OVER (PARTITION BY dept)）默认是逻辑无序集，结果不可预测——尤其在 PostgreSQL 或 Oracle 中，同一语句多次执行可能返回不同排序的累计值。只要涉及 SUM() OVER、AVG() OVER、LAG() 等依赖顺序的函数，ORDER BY 就不是可选，而是必需。
容易踩的坑是只按业务字段排序，忽略时间精度。例如用 ORDER BY create_time 但该字段只有秒级精度，多条记录时间相同，数据库会随机打乱它们的窗口内顺序。正确做法是补上唯一字段： ORDER BY create_time, id。
-
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date)→ 安全，日期天然有序 -
LAG(amount) OVER (PARTITION BY customer_id ORDER BY created_at)→ 危险，created_at 可能重复 -
LAG(amount) OVER (PARTITION BY customer_id ORDER BY created_at, log_id)→ 推荐，保证确定性
ROWS BETWEEN 与 RANGE BETWEEN 的性能差异
ROWS BETWEEN 按物理行数切片（快），RANGE BETWEEN 按值范围切片（慢）。比如计算「过去7天销售额」，用 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW 看似直观，但数据库需对每行重新扫描匹配值范围，无法利用索引；而先用 sale_date::date 做分区键 + ROWS BETWEEN 6 PRECEDING AND CURRENT ROW（配合按日期排序），效率高得多。
MySQL 8.0+ 和 SQL Server 对 RANGE 支持有限，PostgreSQL 虽支持但实际执行计划常退化为嵌套循环。生产环境优先选 ROWS，除非业务逻辑真依赖连续值区间（如信用分段统计）。
- 移动平均（最近5笔订单）→
AVG(amount) OVER (ORDER BY order_time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) - 滚动求和（当天及前3天）→ 先生成日期序列视图，再
JOIN+ROWS，别硬扛RANGE -
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW在大宽表上易触发内存溢出，改用ROWS并确认排序字段有索引
窗口函数不能替代 GROUP BY 的地方
窗口函数不减少行数，GROUP BY 会聚合掉原始明细。想同时看到「每个部门总薪资」和「每个人的薪资占部门比例」，得用窗口： SUM(salary) OVER (PARTITION BY dept)；但若只要最终一张部门汇总表，硬套窗口再 DISTINCT 是低效的——此时 GROUP BY dept 加聚合函数更直接。
另一个高频误用：在 WHERE 或 HAVING 里直接引用窗口函数结果。SQL 标准规定窗口函数只能出现在 SELECT 和 ORDER BY 子句中。想筛出「部门内薪资前3名」，必须用子查询或 CTE 包一层：SELECT * FROM (SELECT *, RANK() OVER (...) rnk FROM t) WHERE rnk 。
- 报表最终要 1 行/部门 → 用
GROUP BY，别加窗口后去重 - 需要保留明细行 + 添加计算列 → 窗口函数是唯一选择
-
WHERE salary > AVG(salary) OVER ()会报错，必须写成WHERE salary > (SELECT AVG(salary) FROM t)或用 CTE 提前算出均值
复杂报表里窗口和聚合常混用，真正难的是判断哪一步该“压缩行数”，哪一步该“扩展维度”。这个边界没理清，优化就变成换汤不换药。
