ROW_NUMBER()无法自动按动态条件重置编号，必须用SUM(CASE WHEN...) OVER(ORDER BY... ROWS UNBOUNDED PRECEDING)构造分组ID，再嵌套PARTITION BY该ID实现重置；LAG()适用于依赖前后行对比的场景。
ROW_NUMBER() 遇到分组边界就重置编号的写法
直接结论：不能只靠 ROW_NUMBER() OVER (ORDER BY ...) 实现“条件触发重置”，必须用 CASE + 累计和构造分组键，再套一层窗口函数。
典型场景是：按时间排序，但每次 status = 'start' 就要重新从 1 开始编号；或者某字段值突变（如 user_id 变了、category 切换）时编号归零。这时候单纯 PARTITION BY 无法表达“动态边界”——它只能静态切分已知列组合。
- 错误做法：
ROW_NUMBER() OVER (PARTITION BY flag ORDER BY ts)——flag是单个字段，无法捕获“上一行是 start、当前行不是”的状态变化 - 正确思路：先用
SUM(CASE WHEN ... THEN 1 ELSE 0 END) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING)生成逻辑分组 ID，再对这个 ID 做PARTITION BY - 注意
ROWS UNBOUNDED PRECEDING必须显式写出，否则某些数据库（如 PostgreSQL 14+）默认是RANGE，遇到相同ts会合并计数
用累计和构造动态分组 ID 的实操细节
核心是把“重置条件”转成 0/1 标志位，再用累计求和形成递增的组号。例如：每次遇到 type = 'header' 就新开一个编号序列。
SELECT
id, type, ts,
SUM(CASE WHEN type = 'header' THEN 1 ELSE 0 END)
OVER (ORDER BY ts, id ROWS UNBOUNDED PRECEDING) AS grp_id,
ROW_NUMBER() OVER (
PARTITION BY SUM(CASE WHEN type = 'header' THEN 1 ELSE 0 END)
OVER (ORDER BY ts, id ROWS UNBOUNDED PRECEDING)
ORDER BY ts, id
) AS rn
FROM events;
-
grp_id不是最终编号，只是中间分组标识；它会在每个'header'行+1，后续行沿用该值直到下一个'header' -
ORDER BY子句里必须包含足够去重的字段（如id），否则同时间戳下多行会导致ROW_NUMBER()排序不确定 - MySQL 8.0+ 和 PostgreSQL 支持该写法；SQLite 3.25+ 也支持，但旧版不支持窗口函数嵌套，需拆成 CTE
替代方案：LAG() 判断前一行状态再累计
当重置条件依赖前后行对比（比如“当前值 ≠ 前一行值”），LAG() 比 CASE 更直接。
例如：按 user_id 排序，但只在 user_id 变化时重置编号（类似连续登录天数中的“断连重计”）：
SELECT
user_id, login_date,
SUM(is_new_group) OVER (ORDER BY user_id, login_date ROWS UNBOUNDED PRECEDING) AS grp,
ROW_NUMBER() OVER (
PARTITION BY SUM(is_new_group) OVER (ORDER BY user_id, login_date ROWS UNBOUNDED PRECEDING)
ORDER BY login_date
) AS seq
FROM (
SELECT *,
CASE WHEN LAG(user_id) OVER (ORDER BY user_id, login_date) != user_id THEN 1 ELSE 0 END AS is_new_group
FROM logins
) t;
-
LAG(user_id)返回前一行的user_id，与当前行比较得出是否切换用户 - 首次出现的
user_id，LAG()返回NULL，所以!=判断为TRUE，自动标记为新组起点 - 若用
IS DISTINCT FROM（PostgreSQL）可避免NULL问题，但多数数据库不支持，稳妥起见加OR LAG(...) IS NULL
性能和兼容性容易被忽略的点
这种嵌套窗口函数在大数据量下可能比单层慢 2–3 倍，尤其当排序字段无索引时。
- 确保
ORDER BY字段有联合索引，例如(ts, id)或(user_id, login_date)，否则全表排序开销极大 - SQL Server 中
ROWS UNBOUNDED PRECEDING是默认行为，可省略；但 PostgreSQL 和 MySQL 必须显式写出，否则语义不同 - 如果目标是“每 N 行重置一次”，不要硬套此逻辑——直接用
(ROW_NUMBER() OVER (...) - 1) % N + 1更高效
真正难的是定义清楚“重置条件”本身是否可转化为前置判断：它必须能基于当前行及有限历史行推导，不能依赖未来数据或聚合结果。
