Oracle中合并会员重叠/连续订阅日期的SQL查询求助

问题场景 #

需要编写Oracle查询，将会员的重叠或连续订阅日期范围合并为单一的连续区间，但现有逻辑无法处理重叠场景，需修正。

测试数据表 #

期望输出 #

当前使用的错误SQL #

SELECT ID, 
    START_DATE, 
    END_DATE 
FROM (
    SELECT ID,
        CONNECT_BY_ROOT START_DATE START_DATE,
        DAYS_DIFF, 
        END_DATE,
        PREV_END,
        CONNECT_BY_ISLEAF ISLEAF
    FROM ( 
        SELECT ID,
            DAYS_DIFF,PREV_END, START_DATE,END_DATE   
        FROM (   
            SELECT ID,
                ROUND(START_DATE-PREV_END) DAYS_DIFF,
                CASE 
                    WHEN START_DATE<=PREV_END THEN PREV_END 
                END PREV_END, 
                START_DATE,END_DATE
            FROM ( 
                SELECT ID,
                    LAG(END_DATE) OVER (PARTITION BY ID ORDER BY START_DATE) PREV_END, 
                    START_DATE,
                    END_DATE  
                FROM TEST_TABLE A 
            )                  
        )     
    ) 
    CONNECT BY ID= PRIOR ID AND   PREV_END=  PRIOR END_DATE    START WITH PREV_END IS NULL  
) WHERE ISLEAF=1; 

解决方案 #

核心逻辑 #

通过窗口函数标记每个连续/重叠的日期组：

1. 按会员ID分组，按订阅开始日期排序。
2. 用LAG函数获取上一条记录的结束日期，判断当前记录的开始日期是否大于上一条的结束日期——如果是，说明是新的独立组；否则属于同一组。
3. 累加组ID（同一组ID相同），最后按会员ID和组ID分组，取每组的最小开始日期和最大结束日期。

正确的Oracle查询语句 #

WITH grouped_subscriptions AS (
    SELECT 
        ID,
        Start_date,
        End_date,
        -- 标记新组：当前开始日期 > 上一条的结束日期时，组ID+1，否则继承上一组ID
        SUM(CASE WHEN Start_date > LAG(End_date) OVER (PARTITION BY ID ORDER BY Start_date) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY ID ORDER BY Start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM TEST_TABLE
)
SELECT 
    ID,
    MIN(Start_date) AS Start_date,
    MAX(End_date) AS End_date
FROM grouped_subscriptions
GROUP BY ID, group_id
ORDER BY ID, Start_date;

逻辑说明 #

• LAG(End_date) OVER (PARTITION BY ID ORDER BY Start_date)：获取当前会员上一条订阅的结束日期。
• CASE WHEN Start_date > LAG(...) THEN 1 ELSE 0 END：判断当前订阅是否是新组的起点。
• SUM(...) OVER (PARTITION BY ID ORDER BY Start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)：累加组ID，同一连续/重叠区间的所有记录会得到相同的group_id。
• 最后按ID和group_id分组，取最小开始日期和最大结束日期，得到合并后的连续订阅区间。

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