Oracle SQL：按ID内国家时段为结果集生成分组ID

需求说明 #

需为按ID、REGISTRATION_TS排序的数据集新增GROUP列，规则如下：

• 同一ID下，连续相同国家的行归为同一组
• 切换国家时组号加1
• 新ID起始时组号重置为1

原始数据集 #

ID    CO REGISTRATION_TS    
----- -- -------------------
56053 CH 05/07/2022 20:57:47
56053 CH 05/07/2022 23:26:05
56053 CH 06/07/2022 03:40:18
56053 CH 06/07/2022 03:42:58
56053 DE 06/07/2022 07:50:21
56053 DE 12/07/2022 05:05:14
56053 DE 13/07/2022 12:43:06
56053 CH 26/07/2022 22:52:20
56053 CH 27/07/2022 04:05:14
56053 DE 27/07/2022 08:47:55
56053 DE 27/07/2022 15:34:32
86EBD SI 29/07/2022 18:05:11
86EBD SI 29/07/2022 18:13:21
86EBD AT 30/07/2022 07:35:15
86EBD DE 30/07/2022 07:35:15
86EBD AT 30/07/2022 07:38:06
86EBD AT 30/07/2022 07:38:06
86EBD AT 30/07/2022 07:46:16
86EBD AT 30/07/2022 07:46:16
86EBD SK 30/07/2022 13:14:45

期望结果集 #

ID    CO REGISTRATION_TS     GROUP
----- -- ------------------- -----
56053 CH 05/07/2022 20:57:47 1
56053 CH 05/07/2022 23:26:05 1
56053 CH 06/07/2022 03:40:18 1
56053 CH 06/07/2022 03:42:58 1
56053 DE 06/07/2022 07:50:21 2
56053 DE 12/07/2022 05:05:14 2
56053 DE 13/07/2022 12:43:06 2
56053 CH 26/07/2022 22:52:20 3
56053 CH 27/07/2022 04:05:14 3
56053 DE 27/07/2022 08:47:55 4
56053 DE 27/07/2022 15:34:32 4
86EBD SI 29/07/2022 18:05:11 1
86EBD SI 29/07/2022 18:13:21 1
86EBD AT 30/07/2022 07:35:15 2
86EBD DE 30/07/2022 07:35:15 3
86EBD AT 30/07/2022 07:38:06 4
86EBD AT 30/07/2022 07:38:06 4
86EBD AT 30/07/2022 07:46:16 4
86EBD AT 30/07/2022 07:46:16 4
86EBD SK 30/07/2022 13:14:45 5

解决方案 #

通过Oracle分析函数组合即可实现，核心是先标记国家切换的位置，再累计计数生成组号：

实现代码 #

SELECT 
    ID,
    CO,
    REGISTRATION_TS,
    SUM(flag) OVER (PARTITION BY ID ORDER BY REGISTRATION_TS) AS "GROUP"
FROM (
    SELECT 
        ID,
        CO,
        REGISTRATION_TS,
        CASE 
            WHEN CO = LAG(CO) OVER (PARTITION BY ID ORDER BY REGISTRATION_TS) THEN 0
            ELSE 1
        END AS flag
    FROM your_table
) t
ORDER BY ID, REGISTRATION_TS;

原理说明 #

1. 内层查询：使用LAG()函数按ID分区、REGISTRATION_TS排序，获取当前行的上一行国家值。对比当前行与上一行国家，若不同则标记为1（表示组切换），相同则标记为0。
2. 外层查询：使用SUM() OVER()按ID分区、REGISTRATION_TS排序，对标记列flag累计求和。每次遇到1时累计值加1，生成递增组号；新ID分区时累计重置，第一个行的flag为1，组号从1开始。

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