窗口函数必须在from子句中用cte或子查询封装，不可在select中定义后于where引用；务必显式指定order by和rows/range帧，lag/lead需明确偏移量和默认值。
窗口函数别写在 SELECT 里再引用
很多人图省事，在 SELECT 子句里写一遍 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)，然后在 WHERE 或 HAVING 里又想用这个编号过滤——直接报错：Window function is not allowed in WHERE clause。
真正能维护的写法是把窗口计算提到 FROM 子句里，用 CTE 或子查询封装：
WITH ranked_orders AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
)
SELECT user_id, order_id
FROM ranked_orders
WHERE rn = 1;
- CTE 不仅解决语法限制，还让逻辑分层清晰：原始数据 → 计算指标 → 过滤使用
- 如果后续要加
RANK()、LAG()等多个函数，全堆在同一个 CTE 里比散落在多层嵌套里好读得多 - 注意：PostgreSQL 和 SQL Server 支持在 CTE 中定义多个命名窗口（
WINDOW w AS (PARTITION BY ...)），但 MySQL 8.0 不支持，得重复写PARTITION BY
ORDER BY 在窗口定义里不是可选项
漏写 ORDER BY 是最隐蔽的维护陷阱。比如写 COUNT(*) OVER (PARTITION BY category)，表面看没错，但结果会随查询优化器或数据加载顺序变化——因为没指定排序，窗口帧默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，而 CURRENT ROW 的“位置”在无序时是不确定的。
哪怕你只想要分区总数，也该显式写成：
COUNT(*) OVER (PARTITION BY category ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
-
ROWS BETWEEN ...比默认的RANGE更稳定，尤其当排序字段有重复值时 - 如果业务真不需要排序语义（例如纯计数），至少用
ORDER BY id（选一个非空、唯一、索引字段）占位，避免后期数据变更引发结果漂移 - BigQuery 对无
ORDER BY的窗口函数会警告；Trino 直接拒绝执行
慎用 LAG/LEAD 的默认 offset=1 和 default value
LAG(amount) 看起来简洁，但实际埋了两个坑：一是它默认取前 1 行，二是遇到分区首行时返回 NULL。一旦需求变成“对比上上笔订单”，或者“首笔订单要填 0 而不是 NULL”，代码就得大改。
规范化写法是始终显式声明参数：
LAG(amount, 2, 0) OVER (PARTITION BY user_id ORDER BY created_at)
- 第二个参数
2明确偏移量，避免靠注释或记忆理解意图 - 第三个参数
0控制边界值，比到处写COALESCE(LAG(...), 0)更直白且少一层函数调用 - 注意：Snowflake 中
default值类型必须和表达式类型严格一致，LAG(created_at, 1, '1970-01-01')会报错，得写TO_TIMESTAMP('1970-01-01')
WHERE 和窗口函数的执行顺序别搞混
窗口函数总在 WHERE 之后执行，这是硬规则。所以想“先筛出近 30 天订单，再对这些订单按用户分组排序”，必须把时间过滤放到 CTE 内部，而不是外层：
WITH recent_orders AS (
SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days' -- ✅ 这里过滤
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM recent_orders -- ✅ 基于已过滤的数据窗口计算
)
SELECT * FROM ranked WHERE rn
- 如果把
WHERE created_at >= ...放到最外层，窗口函数其实是在全表上计算的，性能差，结果还可能错（比如某用户近 30 天只有 2 笔单，但全表有 10 笔，rn = 3就会漏掉） - 某些 BI 工具生成的 SQL 会自动把过滤条件挪到外层，得人工检查执行计划里的
WindowAgg节点输入行数是否合理
窗口函数本身不难，难的是每一步都得想着“这行数据此刻属于哪个分区、帧怎么划、边界在哪”。写完别急着跑，先盯三秒 OVER 里的每个词有没有歧义。
