Oracle SQL按MBR_ID统计重叠日期区间去重总月份数

实现逻辑 #

针对区间重叠、重复、不连续的场景，分四步处理：

• 先裁剪区间范围：仅保留和2021年有交集的记录，把跨年度的区间统一截断到202101-202112区间内，排除无效数据
• 标记连续区间组：按成员ID分组、起始月份升序排序，通过窗口函数判断当前区间是否和之前的累计区间重叠，给每个独立不重叠的连续区间分配组ID
• 合并重叠区间：按成员ID+组ID聚合，取每组的最小起始月、最大结束月，得到去重后的连续无重叠区间
• 汇总计算：对每个合并后的区间计算包含的月份数，按成员ID求和得到最终结果

可直接运行的SQL代码 #

WITH valid_data AS (
    -- 第一步：裁剪出2021年范围内的有效区间
    SELECT
        MBR_ID,
        GREATEST(MIN_SPANFROM, 202101) AS start_mon,
        LEAST(MAX_SPANFROM, 202112) AS end_mon
    FROM your_table -- 替换为实际业务表名
    WHERE MIN_SPANFROM <= 202112
      AND MAX_SPANFROM >= 202101
),
group_mark AS (
    -- 第二步：给连续重叠的区间打统一组标记
    SELECT
        MBR_ID,
        start_mon,
        end_mon,
        SUM(CASE WHEN start_mon > prev_max_end THEN 1 ELSE 0 END) OVER(
            PARTITION BY MBR_ID ORDER BY start_mon, end_mon
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS grp_id
    FROM (
        SELECT
            MBR_ID,
            start_mon,
            end_mon,
            NVL(MAX(end_mon) OVER(
                PARTITION BY MBR_ID ORDER BY start_mon, end_mon
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0) AS prev_max_end
        FROM valid_data
    )
),
merged_interval AS (
    -- 第三步：合并同组的重叠区间
    SELECT
        MBR_ID,
        grp_id,
        MIN(start_mon) AS merged_start,
        MAX(end_mon) AS merged_end
    FROM group_mark
    GROUP BY MBR_ID, grp_id
)
-- 第四步：按成员汇总总月份数
SELECT
    MBR_ID,
    SUM(
        -- 直接通过yyyymm数值计算月份差，不受大小月、闰年影响
        (TRUNC(merged_end / 100) - TRUNC(merged_start / 100)) * 12
        + MOD(merged_end, 100) - MOD(merged_start, 100)
        + 1
    ) AS TOTAL_MONTHS_2021
FROM merged_interval
GROUP BY MBR_ID
ORDER BY MBR_ID;

说明 #

• 代码中your_table替换为实际存储成员月份区间的业务表名即可，运行结果和给出的样例期望完全一致
• 月份计算采用数值运算逻辑，避免了日期函数因不同月份天数、闰年2月导致的计算误差
• 自动兼容区间完全重复、部分重叠、间隔不连续、跨2021年等所有边界场景

内容的提问来源于stack exchange，提问作者Vinay Kumar Hiremath