SQL嵌套查询与窗口函数结合使用技巧

窗口函数不能直接用在 WHERE、GROUP BY 或 HA VING 里，这个坑几乎每个写 SQL 的都踩过。必须先把窗口计算结果放到子查询或 CTE 中，再在外层引用，否则就会报错。

先说最典型的错误场景：在 WHERE 里直接写 ROW_NUMBER()。比如 SELECT * FROM t WHERE ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) —— 这行代码要么触发 Unknown column，要么提示 Window function is not allowed in this context。原因很简单：SQL 的执行顺序里，WHERE 的优先级高于 SELECT（包括窗口函数），所以系统根本不知道窗口函数在说什么。

• 正确做法：先用子查询或 CTE 算出编号，外层再 WHERE rn
• 别忘了 PARTITION BY 和显式的 ORDER BY，否则编号毫无意义
• 如果排序字段有重复值（比如多个订单时间相同），加一个唯一字段如 id 来保序：ORDER BY created_at DESC, id DESC

再来看 GROUP BY 的陷阱。想按“与前一日差值是否为正”来分组？GROUP BY LAG(amount) OVER (ORDER BY date) 这样的写法是绝对非法的。窗口函数的输出结果不能直接作为分组键，必须先把结果物化出来。

• 必须先用 CTE 或子查询生成 prev_amount 字段，再在外层的 GROUP BY 引用它
• MySQL 8.0+ 支持在派生表里用 LAG，但如果外层又加了 GROUP BY，一定要确保排序上下文不丢失
• PostgreSQL 虽然更宽松，但如果子查询用了 ORDER BY 而外层没重排，LAG 的结果很可能错位

嵌套子查询里的 ORDER BY 也是个容易混淆的点。子查询里写 ORDER BY date 只影响结果集的顺序，对内层的 ROW_NUMBER() OVER (ORDER BY ...) 完全没有作用。窗口函数的排序必须显式写在 OVER 子句里。

• ROW_NUMBER() OVER (ORDER BY date) 和子查询的 ORDER BY date 是两码事，不能互相替代
• 如果窗口函数里忘了写 ORDER BY，编号结果就没法预测了，尤其在 MySQL 中，不同次执行结果可能完全不同
• 日期字段是 VARCHAR 类型时，隐式转换会导致排序错乱，务必确认类型一致

说到多层窗口函数，CTE 比嵌套子查询可靠得多。当需要同时用 ROW_NUMBER() 和 LAG() 再做聚合时，CTE 能清晰隔离每一步逻辑，还能避免优化器多次扫描。

• MySQL 8.0 的 CTE 默认可以物化，比老式的 FROM (SELECT ...) t 更稳定
• 不要在一个 SELECT 里堆多个不同 PARTITION BY 和不同 ORDER BY 的窗口函数——PostgreSQL 和 SQL Server 很可能会分别排序，性能直接翻倍
• 如果只是要总行数这类全局指标，COUNT(*) OVER () 看似方便，但空 OVER() 在 PostgreSQL 中会触发全表排序，效率不如先查 COUNT(*) 再 JOIN

说到底，窗口函数的执行时机晚于 WHERE 但早于外层 ORDER BY，这意味着它的输入数据已经过过滤，但输出列不能反向影响过滤条件。所有依赖窗口结果的处理逻辑，都必须放在第二层查询里——这一点，才是最容易忽略的关键。