Oracle SQL实现分段累计计数：遇空值重置计数器

问题描述 #

现有表结构及数据如下：

CP          ROK DOPIS_C
-----------------------
6059150790  2014    C
6059150790  2015    C
6059150790  2016    C
6059150790  2017     
6059150790  2018    C
6059150790  2019     
6059150790  2020    C
6059150790  2021    C
6059150790  2022    C
6059150790  2023     
6059150790  2024     

需要生成RN_C1列，规则为：对DOPIS_C为'C'的记录按连续分段计数，若前一条记录的DOPIS_C为空，则计数器重置。期望输出如下：

CP          ROK DOPIS_C     RN_C1
---------------------------------
6059150790  2014    C         1
6059150790  2015    C         2
6059150790  2016    C         3
6059150790  2017                 
6059150790  2018    C         1
6059150790  2019                 
6059150790  2020    C         1
6059150790  2021    C         2
6059150790  2022    C         3
6059150790  2023              
6059150790  2024                 

现有尝试的问题 #

用户尝试的SQL语句未正确实现计数器重置，且出现错误数值：

SELECT 
    CP,
    ROK,
    DOPIS_C,
    CASE 
        WHEN DOPIS_C = 'C' THEN 
            CASE 
                WHEN lag(DOPIS_C) OVER (ORDER BY ROK) IS NULL THEN 
                    ROW_NUMBER() OVER (ORDER BY ROK)
                WHEN lag(DOPIS_C) OVER (ORDER BY ROK) = 'C' THEN 
                    COUNT(CASE WHEN DOPIS_C = 'C' THEN 1 END) OVER (ORDER BY ROK) 
            END
        ELSE 0 
    END AS POSILANI
FROM 
    table;

错误结果：

6059150790  2014    C   1
6059150790  2015    C   2
6059150790  2016    C   3
6059150790  2017        0
6059150790  2018    C   5
6059150790  2019        0
6059150790  2020    C   7
6059150790  2021    C   6
6059150790  2022    C   7
6059150790  2023        0
6059150790  2024        0

正确的Oracle SQL实现 #

SELECT 
    CP,
    ROK,
    DOPIS_C,
    CASE 
        WHEN DOPIS_C = 'C' THEN
            ROW_NUMBER() OVER (
                PARTITION BY CP, 
                             CASE WHEN DOPIS_C = 'C' THEN 
                                  SUM(CASE WHEN DOPIS_C IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY ROK ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                             END
                ORDER BY ROK
            )
        ELSE NULL
    END AS RN_C1
FROM 
    your_table_name
ORDER BY ROK;

逻辑解释 #

1. 生成分段标识：通过SUM(CASE WHEN DOPIS_C IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY ROK ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)统计当前行及之前的空值记录数，每遇到一个空值，后续'C'记录的分段标识就会递增，实现连续段的划分。
2. 分区计数：以CP（确保同一编号内独立处理）和分段标识作为分区条件，在每个分区内用ROW_NUMBER()按ROK排序计数，确保每个连续'C'段从1开始重新计数。
3. 空值处理：当DOPIS_C不为'C'时，RN_C1设为NULL，与期望输出格式一致。

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