单SELECT语句实现按ID分组的累计版本号计算SQL需求

完整SQL代码 #

SELECT 
    ID,
    Date,
    Person,
    Status,
    COALESCE(
        SUM(CASE WHEN Person != 'API' THEN 1 ELSE 0 END) OVER (
            PARTITION BY ID 
            ORDER BY Date ASC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ), 0
    ) + 1 AS VERSION
FROM your_table_name;

逻辑说明 #

1. 版本规则落地：版本号从1开始，每出现一条非API人员的记录，后续所有记录的版本号累加1。核心逻辑是统计当前记录之前（不含当前）的非API记录总数，再加1得到当前版本号。
2. 分组排序：PARTITION BY ID确保仅在同一ID内计算版本，ORDER BY Date ASC保证按时间顺序处理记录，贴合业务的时间逻辑。
3. 范围限定：ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING指定统计范围为当前记录之前的所有记录，避免把当前记录的非API状态算入当前版本号（比如第二条非API记录的版本号仍沿用之前的版本，后续记录才升级）。
4. 边界处理：COALESCE(..., 0)解决第一条记录无前置记录的问题，将SUM的null结果替换为0，加1后得到初始版本1。

原写法的问题 #

你之前的写法用STATUS判断是否累加，既不符合需求（需求是按Person是否为API，而非Status），也无法处理连续非API记录的场景——连续非API记录的Status都是OKAY类，原写法的SUM会直接累加，但逻辑上应该是每条非API记录都让后续版本加1，当前记录的版本号仍沿用之前的版本，直到下一条记录才升级。

验证结果 #

执行上述SQL后，输出结果将完全匹配预期：

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