SQL计算PRINCOMP列连续零值对应最大到期日的方法

直接用窗口函数差值分组法实现，不需要自定义函数或者循环，所有支持SQL2003标准窗口函数的数据库（MySQL8+、PostgreSQL、Hive、Spark SQL、ClickHouse等）都可以直接用，比rank类函数逻辑更稳定，不会出现分组错误。

实现逻辑步骤 #

• 先按AGREEMENTID分区、DUEDATE升序排序，为每行生成全局行号rn1
• 再按AGREEMENTID+PRINCOMP是否为0双维度分区、DUEDATE升序排序，为每行生成分组内行号rn2
• 两个行号做差，同一连续区间（不管是零值还是非零值）的rn1-rn2差值完全固定，用AGREEMENTID+PRINCOMP+差值即可生成唯一的连续区间ID
• 对每个连续区间取最大DUEDATE，按照需求规则给END_DATE赋值即可

可直接运行的SQL代码 #

SELECT 
    AGREEMENTID,
    DUEDATE,
    PRINCOMP,
    INTCOMP,
    -- 需求1：连续零值区间所有记录都填区间最大DUEDATE，用下面这行
    -- CASE WHEN PRINCOMP = 0 THEN MAX(DUEDATE) OVER (PARTITION BY AGREEMENTID, PRINCOMP, rn1 - rn2) ELSE NULL END AS END_DATE
    -- 需求2：仅连续零值区间的最后一条记录填最大DUEDATE（和你给出的样例展示效果一致），用下面这行
    CASE 
        WHEN PRINCOMP = 0 
        AND DUEDATE = MAX(DUEDATE) OVER (PARTITION BY AGREEMENTID, PRINCOMP, rn1 - rn2)
        THEN DUEDATE 
        ELSE NULL 
    END AS END_DATE
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY AGREEMENTID ORDER BY DUEDATE) AS rn1,
        ROW_NUMBER() OVER (PARTITION BY AGREEMENTID, PRINCOMP ORDER BY DUEDATE) AS rn2
    FROM your_table_name
) t
ORDER BY AGREEMENTID, DUEDATE;

注意事项 #

必须先将DUEDATE字段转换为标准日期/时间戳类型再做排序、最大值计算，禁止直接用字符串类型排序，否则会出现类似15JAN2020字符串排序小于16SEP2019的逻辑错误，导致分组和取值完全偏离预期。
针对你提供的样例数据，上述代码运行后2019-09-16至2020-02-15的连续零值区间，仅最后一条2020-02-15的记录会填充END_DATE，和你给出的预期效果完全一致。

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