TSQL中已分区数据的非空行二次分组编号实现方案咨询

问题描述 #

现有TSQL脚本通过ROW_NUMBER() OVER(PARTITION BY SUBSTRING(NSS,1,10))按NSS字段前10位分区，生成nTIMES_ID_REPEATED列实现数据分组。当前需求是：基于该分组，仅对REPORT_STATUS字段非空（含排除空白字符串）的行生成新的连续序号列，忽略REPORT_STATUS为空的行。

附测试表脚本：

CREATE TABLE TEST_TABLE ( 
    nTIMES_ID_REPEATED INT,
    STUDY_DATE DATETIME,
    HOSPITAL varchar(255),
    FIRST_LAST_NAME varchar(255),
    SECOND_LAST_NAME varchar(255),
    PATIENT_NAME varchar(255),
    NSS varchar(255),
    CPIM_CODE varchar(255),
    ID_REMAINDER varchar(255),
    STUDY_TYPE varchar(255),
    MODALITY varchar(255),
    REPORT_STATUS varchar(255),
    UID_PARTITION INT
);

INSERT INTO TEST_TABLE VALUES (1,'2022/05/28','HGZ 98','SANCHEZ','GONZALEZ','DANIELA YARELI ','9211929411','80.15.005','1F1992OR','Ultrasonido','US','28/05/2022',1)
INSERT INTO TEST_TABLE VALUES (2,'2022/05/28','HGZ 98','SANCHEZ','GONZALEZ','DANIELA YARELI ','9211929411','80.15.005','1F1992OR','Ultrasonido','US','28/05/2022',1)
INSERT INTO TEST_TABLE VALUES (1,'2022/05/28','HGZ 98','AVILA','ESPINOZA','MA DE JESUS ','9409850742','80.15.005','4F1961OR','Ultrasonido','US','28/05/2022',1)
INSERT INTO TEST_TABLE VALUES (2,'2022/05/28','HGZ 98','AVILA','ESPINOZA','MA DE JESUS ','9409850742','80.15.005','4F1961OR','Ultrasonido','US','28/05/2022',1)
INSERT INTO TEST_TABLE VALUES (1,'2022/05/28','HGZ 98','VELAZQUEZ','CONTRERAS','GRECIA IRLANDA ','9412972424','80.15.005','1F1997OR','Ultrasonido','US','28/05/2022',1)
INSERT INTO TEST_TABLE VALUES (2,'2022/05/28','HGZ 98','VELAZQUEZ','CONTRERAS GRECIA IRLANDA',' ','9412972424','80.15.001','00000000','Radiología Simple','CR',' ',1)
INSERT INTO TEST_TABLE VALUES (1,'2022/05/28','HGZ 98','SANTIAGO','ARREDONDO','HANNA NIDIA ','9496811863','80.15.005','3F2008OR','Ultrasonido','US','28/05/2022',1)
INSERT INTO TEST_TABLE VALUES (2,'2022/05/28','HGZ 98','SANTIAGO','ARREDONDO HANNA NIDIA',' ','9496811863','80.15.001','10000000','Radiología Simple','CR',' ',1)
INSERT INTO TEST_TABLE VALUES (3,'2022/05/28','HGZ 98','SANTIAGO','ARREDONDO HANNA NIDIA',' ','9496811863','80.15.007','13F2008O','Tomografía Computada Simple','CT','28/05/2022',1)
INSERT INTO TEST_TABLE VALUES (1,'2022/05/28','HGZ 98','PACHECO','PINEDA ISABEL',' ','9498790021','80.15.001','20000000','Radiología Simple','CR',' ',1)
INSERT INTO TEST_TABLE VALUES (2,'2022/05/28','HGZ 98','PACHECO','PINEDA ISABEL',' ','9498790021','80.15.001','70000000','Radiología Simple','CR',' ',1)

解决方案 #

完全可以通过窗口函数实现该需求，无需额外复杂方案。核心思路是：

• 保留原有的SUBSTRING(NSS,1,10)分区逻辑
• 使用CASE语句过滤REPORT_STATUS非空的行，仅对这些行生成连续序号
• 对REPORT_STATUS为空的行，序号列返回NULL（或根据需求返回其他标识）

具体TSQL代码如下：

SELECT 
    *,
    CASE 
        -- 排除NULL和空白字符串的情况
        WHEN LTRIM(RTRIM(ISNULL(REPORT_STATUS, ''))) <> ''
        THEN ROW_NUMBER() OVER(
            PARTITION BY SUBSTRING(NSS,1,10)
            ORDER BY nTIMES_ID_REPEATED -- 可根据实际需求调整排序字段，比如STUDY_DATE
        )
        ELSE NULL -- 空值行不生成序号
    END AS NEW_REPORT_SEQUENCE
FROM TEST_TABLE;

代码说明 #

1. 过滤逻辑：LTRIM(RTRIM(ISNULL(REPORT_STATUS, ''))) <> ''同时处理了REPORT_STATUS为NULL、纯空格的情况，确保只有有效内容的行才会生成序号。
2. 分区与排序：按NSS前10位分区，保证同一患者分组内的序号连续；排序字段使用原有的nTIMES_ID_REPEATED，可根据业务需求替换为STUDY_DATE或其他字段。
3. 空值处理：REPORT_STATUS为空的行，NEW_REPORT_SEQUENCE列返回NULL，清晰区分有效行与忽略行。

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