SQL间隙岛屿问题：为连续整数分组分配group_id的方案咨询

核心逻辑 #

你之前计算的RN1 - RN2本身就是同一个连续分组（岛屿）的唯一标识，只需基于该标识与时间维度生成递增的group_id即可，以下提供两种常用场景的实现代码：

场景1：group_id 按租户独立递增（推荐，符合租户数据隔离逻辑） #

SELECT     
    tenant_id,
    tenancy_type,
    a_other,
    b_other,
    lease_date,
    lease_month_no,
    DENSE_RANK() OVER (PARTITION BY tenant_id ORDER BY group_start_date) AS group_id
FROM (
    SELECT 
        tenant_id,
        tenancy_type,
        a_other,
        b_other,
        lease_date,
        ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type, RN1-RN2 ORDER BY lease_date) AS lease_month_no,
        MIN(lease_date) OVER (PARTITION BY tenant_id, tenancy_type, RN1-RN2) AS group_start_date
    FROM (
        SELECT 
            tenant_id,
            tenancy_type,
            a_other,
            b_other,
            lease_date,
            ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY lease_date) AS RN1,
            ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type ORDER BY lease_date) AS RN2
        FROM dbo.lease_info
    ) AS x
) AS y
ORDER BY tenant_id, lease_date

场景2：group_id 全局按时间顺序递增（跨租户统一排序） #

SELECT     
    tenant_id,
    tenancy_type,
    a_other,
    b_other,
    lease_date,
    lease_month_no,
    DENSE_RANK() OVER (ORDER BY group_start_date, tenant_id, tenancy_type) AS group_id
FROM (
    SELECT 
        tenant_id,
        tenancy_type,
        a_other,
        b_other,
        lease_date,
        ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type, RN1-RN2 ORDER BY lease_date) AS lease_month_no,
        MIN(lease_date) OVER (PARTITION BY tenant_id, tenancy_type, RN1-RN2) AS group_start_date
    FROM (
        SELECT 
            tenant_id,
            tenancy_type,
            a_other,
            b_other,
            lease_date,
            ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY lease_date) AS RN1,
            ROW_NUMBER() OVER (PARTITION BY tenant_id, tenancy_type ORDER BY lease_date) AS RN2
        FROM dbo.lease_info
    ) AS x
) AS y
ORDER BY lease_date

逻辑说明 #

• 内层计算的RN1-RN2是间隙与岛屿问题的经典分组标识，同一个连续分组的所有行该差值完全相同
• 新增的group_start_date取每个分组的最小租赁日期，保证group_id严格按时间顺序递增
• 使用DENSE_RANK而非RANK可以保证group_id为连续无缺口的整数，完全适配后续按group_id取MIN/MAX、统计分组长度等计算需求
• 验证分组正确性可直接按group_id分组聚合，查看每个分组的时间区间、行计数是否符合预期

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