用 OVER (PARTITION BY ...) 替代子查询或自连接更简洁高效，但需为 PARTITION BY 和 ORDER BY 字段建联合索引，否则可能比 GROUP BY 更慢。
直接说结论：用 OVER (PARTITION BY ...) 替代子查询或自连接做分组统计，90% 场景下更简洁、更稳、更快；但前提是 PARTITION BY 和 ORDER BY 字段有联合索引，否则可能比 GROUP BY 还慢。
为什么窗口函数能替代低效分组？
传统分组统计（比如查每个用户的最新订单、部门平均薪资、连续登录断点）常依赖多层子查询或自连接，逻辑绕、易出错、执行计划容易走全表扫描。窗口函数把“分组+排序+计算”压进一次扫描，保留原始行的同时追加结果列。
关键区别：
-
GROUP BY折叠行数，必须用JOIN或子查询把明细拉回来——多一次关联，就多一次数据膨胀风险 -
OVER (PARTITION BY user_id)不折叠行，直接在每行上算出该用户相关的聚合值，天然避免笛卡尔积 - 例如查“每个用户最新订单”，
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC)一行标序，WHERE rn = 1过滤，不用写NOT EXISTS或LEFT JOIN
ORDER BY 是强制项，不是可选项
只要用了 SUM() OVER、LAG()、ROW_NUMBER() 这类依赖顺序的函数，ORDER BY 就必须显式写出。PostgreSQL 不会默认按主键或插入顺序排——没它，结果不可复现，尤其在并发写入时。
常见踩坑点：
- 只写
ORDER BY created_at，但该字段精度是秒级，多条记录时间相同 → 数据库内部随机排序 → 每次执行ROW_NUMBER()结果不同 - 正确做法：
ORDER BY created_at DESC, id DESC，用唯一字段兜底保证稳定性 - 如果只是算
COUNT(*) OVER (PARTITION BY dept_id)（不依赖顺序），可以不写ORDER BY，但这类场景少，且仍建议加上以防后续扩展
索引没建对，窗口函数反而拖垮性能
窗口函数快的前提是数据库能利用索引完成 PARTITION BY + ORDER BY 的定位和排序。否则就会触发磁盘排序（Sort 节点出现在 EXPLAIN (ANALYZE) 中），内存不够时还会生成临时文件，比普通 GROUP BY 还慢。
必须做的两件事：
- 为
PARTITION BY a, ORDER BY b创建联合索引：CREATE INDEX idx_user_created ON orders (user_id, created_at) INCLUDE (amount); - 验证是否生效：运行
EXPLAIN (ANALYZE, BUFFERS) SELECT ..., SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) FROM orders WHERE user_id = 123;，看是否出现Index Scan而非Seq Scan或Sort - 别在
LAG()里套表达式，比如LAG(created_at + INTERVAL '1 day')，这会让优化器放弃索引
ROWS BETWEEN 比 RANGE BETWEEN 更可靠
当需要滑动窗口（如“过去7天销售额”），优先用 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW，而不是 RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW。
原因很实际：
-
ROWS按物理行数切片，数据库只需定位前后几行，能走索引 -
RANGE按值范围匹配，每行都要重新扫描整个分区找符合条件的行，无法利用 B-tree 索引，大数据量下极易超时 - 若业务真要按日期范围，先用
sale_date::date做分区键 +ORDER BY sale_date::date，再配ROWS，比硬上RANGE稳得多
最常被忽略的一点：窗口函数不能出现在 WHERE 或 HAVING 子句里。想过滤排名前3的用户？必须套一层子查询或 CTE，否则报错。这个限制不是 bug，是 SQL 标准决定的执行顺序——窗口计算发生在 WHERE 之后、ORDER BY 之前。
