PostgreSQL中如何在另一列值重置为1时新增递增迭代ID列

现有SQL表包含seq、sub_seq两个字段，需要新增第三列id，计算规则为每当sub_seq字段的值重置为1时，id的值对应递增1，预期计算结果如下：

不需要依赖PL/pgSQL这类存储过程语法，用标准窗口函数即可实现，符合SQL:2003标准，所有支持窗口函数的主流数据库（PostgreSQL、MySQL 8.0+、SQL Server、Oracle等）都可以直接运行。
核心逻辑：统计截止到当前行（按seq排序），累计出现过多少次sub_seq = 1的记录，这个累计值就是需要的id。
实现代码如下：

SELECT
  seq,
  sub_seq,
  SUM(CASE WHEN sub_seq = 1 THEN 1 ELSE 0 END) OVER (
    ORDER BY seq
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS id
FROM your_table_name;

兼容无窗口函数场景的写法 #

如果使用的数据库版本不支持窗口函数（比如MySQL 5.x），可以用自连接统计的方式实现，逻辑和窗口函数一致，适合小数据量场景：

SELECT
  t1.seq,
  t1.sub_seq,
  COUNT(t2.seq) AS id
FROM your_table_name t1
LEFT JOIN your_table_name t2
  ON t2.seq <= t1.seq
  AND t2.sub_seq = 1
GROUP BY t1.seq, t1.sub_seq
ORDER BY t1.seq;

• 两种写法都不需要自定义函数或存储过程，纯标准SQL语法即可得到预期结果
• 如果需要将id字段持久化到表结构中，可以基于上述计算逻辑，结合对应数据库的生成列或更新语句完成字段落地

内容的提问来源于stack exchange，提问作者Jscore