多日期时段截断：按PARENT_ID生成CHILD_ID活跃组合时段

针对你需要将TBL_CHILDREN表按同一PARENT_ID下的活跃CHILD_ID组合生成单独时段记录的需求，我们可以通过时间节点拆分+区间关联聚合的方式实现，以下是具体步骤和代码：

1. 先看示例数据 #

首先你的源表数据可以用这段SQL生成：

WITH TBL_CHILDREN AS (
    SELECT 57 PARENT_ID, 1 CHILD_ID, TO_DATE('2017-01-01','YYYY-MM-DD') START_DATE, TO_DATE('9999-12-31','YYYY-MM-DD') END_DATE FROM dual 
    UNION ALL 
    SELECT 57 PARENT_ID, 2 CHILD_ID, TO_DATE('2017-03-01','YYYY-MM-DD') START_DATE, TO_DATE('2017-05-31','YYYY-MM-DD') END_DATE FROM dual 
    UNION ALL 
    SELECT 57 PARENT_ID, 3 CHILD_ID, TO_DATE('2017-04-01','YYYY-MM-DD') START_DATE, TO_DATE('2017-10-31','YYYY-MM-DD') END_DATE FROM dual
)
SELECT * FROM TBL_CHILDREN;

2. 完整解决方案SQL #

WITH TBL_CHILDREN AS (
    SELECT 57 PARENT_ID, 1 CHILD_ID, TO_DATE('2017-01-01','YYYY-MM-DD') START_DATE, TO_DATE('9999-12-31','YYYY-MM-DD') END_DATE FROM dual 
    UNION ALL 
    SELECT 57 PARENT_ID, 2 CHILD_ID, TO_DATE('2017-03-01','YYYY-MM-DD') START_DATE, TO_DATE('2017-05-31','YYYY-MM-DD') END_DATE FROM dual 
    UNION ALL 
    SELECT 57 PARENT_ID, 3 CHILD_ID, TO_DATE('2017-04-01','YYYY-MM-DD') START_DATE, TO_DATE('2017-10-31','YYYY-MM-DD') END_DATE FROM dual
),
-- 提取所有关键时间节点（所有开始日期、结束日期+1，因为结束日期是闭区间，+1后转为开区间边界）
time_nodes AS (
    SELECT PARENT_ID, START_DATE AS node_date FROM TBL_CHILDREN
    UNION
    SELECT PARENT_ID, END_DATE + 1 AS node_date FROM TBL_CHILDREN
),
-- 生成连续的时间区间，每个区间的start是上一个节点，end是当前节点-1
date_ranges AS (
    SELECT 
        PARENT_ID,
        node_date AS range_start,
        LEAD(node_date) OVER (PARTITION BY PARENT_ID ORDER BY node_date) - 1 AS range_end
    FROM time_nodes
)
-- 关联原表，找出每个区间内活跃的CHILD_ID，聚合后输出
SELECT 
    dr.PARENT_ID,
    dr.range_start AS START_DATE,
    dr.range_end AS END_DATE,
    -- 用LISTAGG生成逗号分隔的CHILD_ID列表，Oracle 12c+也可以用JSON_ARRAYAGG生成数组格式
    LISTAGG(tc.CHILD_ID, ',') WITHIN GROUP (ORDER BY tc.CHILD_ID) AS ACTIVE_CHILDREN
FROM date_ranges dr
JOIN TBL_CHILDREN tc 
    ON dr.PARENT_ID = tc.PARENT_ID
    AND dr.range_start <= tc.END_DATE
    AND dr.range_end >= tc.START_DATE
WHERE dr.range_end IS NOT NULL -- 过滤掉最后一个无意义的节点（因为LEAD会生成NULL）
GROUP BY dr.PARENT_ID, dr.range_start, dr.range_end
ORDER BY dr.PARENT_ID, dr.range_start;

3. 输出结果说明 #

执行上述SQL后，会得到符合你需求的分段记录：

关键逻辑解释 #

• 时间节点拆分：把所有的START_DATE和END_DATE+1收集起来，这些节点是活跃组合发生变化的临界点；
• 生成连续区间：用LEAD()函数将相邻的时间节点配对，形成一个个连续的时间区间；
• 关联聚合：在每个区间内找出所有活跃的CHILD_ID，用LISTAGG聚合得到该区间的活跃组合，最终按区间分组输出。

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