如何编写SQL筛选enrollments表中符合规则的非重叠有效注册记录？

表结构 #

CREATE TABLE enrollments (
    case_id INT,
    enrollment_id VARCHAR(10),
    open_date DATE,
    closed_date DATE,
    status VARCHAR(10)
);

现有数据 #

INSERT INTO enrollments (case_id, enrollment_id, open_date, closed_date, status)
VALUES
(1, 'ab', '2024-10-01', '2024-10-05', 'closed'),
(1, 'bc', '2024-10-03', '2024-10-04', 'closed'),
(1, 'bd', '2024-10-05', '2024-10-15', 'closed'),
(1, 'sx', '2024-10-12', '2024-10-19', 'closed'),
(1, 'za', '2024-10-16', '2024-10-21', 'closed'),
(1, 'ca', '2024-10-25', '2024-10-26', 'closed'),
(1, 'dd', '2024-10-25', '2024-10-27', 'closed'),
(1, 'kh', '2024-10-28', NULL, 'open'),
(1, 'kk', '2024-10-28', NULL, 'open');

筛选规则 #

• 仅保留与之前有效注册记录无重叠的记录：当前注册的open_date不应落在之前有效注册的open_date与closed_date之间；
• 若两条记录open_date相同，保留持续时间更长的记录：即closed_date最晚的，若为open状态则保留；
• 若两条open状态的记录无重叠，需同时保留；
• 仅基于之前的有效注册记录验证当前记录：例如za虽与sx重叠，但sx是无效重叠记录，故za需被保留。

期望输出 #

解决方案 #

可以使用递归CTE实现需求，核心是逐步筛选有效记录，每一步都基于已确认的有效记录判断当前记录是否符合条件：

WITH ranked_enrollments AS (
    -- 对同case_id+open_date的记录排序，优先保留持续时间最长的
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY case_id, open_date 
            ORDER BY 
                CASE WHEN status = 'open' THEN 1 ELSE 0 END DESC,
                closed_date DESC
        ) AS rn
    FROM enrollments
),
valid_candidates AS (
    -- 筛选同日期下的有效候选：非open状态取排名第一的，open状态全部保留
    SELECT 
        case_id, enrollment_id, open_date, closed_date, status
    FROM ranked_enrollments
    WHERE 
        rn = 1 
        OR status = 'open'
),
recursive_valid AS (
    -- 递归初始：取当前case_id下最早open_date的有效候选
    SELECT 
        case_id, enrollment_id, open_date, closed_date, status,
        COALESCE(closed_date, '9999-12-31') AS max_closed_date -- open状态用极大值替代closed_date，方便时间比较
    FROM valid_candidates
    WHERE open_date = (SELECT MIN(open_date) FROM valid_candidates WHERE case_id = 1)
    UNION ALL
    -- 递归步骤：加入后续不与之前有效记录重叠的候选
    SELECT 
        vc.case_id, vc.enrollment_id, vc.open_date, vc.closed_date, vc.status,
        GREATEST(rv.max_closed_date, COALESCE(vc.closed_date, '9999-12-31'))
    FROM valid_candidates vc
    JOIN recursive_valid rv ON vc.case_id = rv.case_id
    WHERE 
        vc.open_date > rv.max_closed_date
        AND vc.open_date > (SELECT MAX(open_date) FROM recursive_valid WHERE case_id = vc.case_id)
)
SELECT 
    case_id,
    enrollment_id,
    TO_CHAR(open_date, 'DD-MM-YYYY') AS open_date,
    closed_date,
    status
FROM recursive_valid
ORDER BY open_date, enrollment_id;

思路说明 #

1. ranked_enrollments：对同一日期的记录排序，确保持续时间最长的记录优先被选中；开放状态的记录因规则要求需全部保留，所以不做排名过滤。
2. valid_candidates：生成待筛选的候选记录集，排除同日期下无效的短时长记录，保留开放状态的所有记录。
3. recursive_valid：从最早的记录开始，逐步加入后续时间不与已保留有效记录重叠的候选，确保每一步都只基于之前的有效记录做判断。
4. 最后将日期格式转换为期望的DD-MM-YYYY格式输出。

如果需要支持多case_id，只需调整递归初始和步骤中的条件，按case_id分组处理即可。

内容的提问来源于stack exchange，提问作者Ashok kumar Kilaru