结合递归的PostgreSQL间隙与孤岛SQL问题求解

核心需求梳理 #

• 基于层级结构的locations表（学校→联邦州→国家），递归获取目标地点及其所有父节点的假期条目
• 针对指定目标假期条目，筛选所有重叠或相邻的跨层级条目（含父节点层级）
• 输出关键数据：目标条目自身天数、合并后总无学天数（含相邻周末/假日）、聚合关联entry_ids、合并后的真实起止日期

分步SQL实现 #

1. 递归获取目标地点的全层级节点 #

用递归CTE遍历locations的层级关系，拿到目标地点（以id=3的学校为例）及其所有上级节点ID：

WITH RECURSIVE location_hierarchy AS (
    -- 起始节点：目标学校
    SELECT id, parent_id
    FROM locations
    WHERE id = 3
    UNION ALL
    -- 递归向上遍历父节点
    SELECT l.id, l.parent_id
    FROM locations l
    JOIN location_hierarchy lh ON l.id = lh.parent_id
)
SELECT id FROM location_hierarchy;

2. 筛选目标条目及关联的重叠/相邻条目 #

定位指定目标条目，同时筛选出所有与它重叠或首尾相接的跨层级条目（相邻定义为前一条结束日+1天等于后一条开始日）：

WITH RECURSIVE location_hierarchy AS (
    SELECT id, parent_id
    FROM locations
    WHERE id = 3
    UNION ALL
    SELECT l.id, l.parent_id
    FROM locations l
    JOIN location_hierarchy lh ON l.id = lh.parent_id
),
target_entry AS (
    -- 指定目标条目：学校3的Christmas school vacation
    SELECT id AS target_id, start_date, end_date
    FROM entries
    WHERE location_id = 3
      AND name = 'Christmas school vacation'
),
related_entries AS (
    -- 获取所有层级的关联条目（含目标自身）
    SELECT e.id, e.start_date, e.end_date
    FROM entries e
    JOIN location_hierarchy lh ON e.location_id = lh.id
    -- 筛选重叠/相邻条目
    WHERE (e.start_date <= (SELECT end_date + INTERVAL '1 day' FROM target_entry)
           AND e.end_date >= (SELECT start_date - INTERVAL '1 day' FROM target_entry))
)
SELECT * FROM related_entries;

3. 合并时间段并计算核心数据 #

通过递归合并重叠/相邻时间段，聚合关联entry_ids，同时计算目标条目自身天数与合并后总天数：

WITH RECURSIVE location_hierarchy AS (
    SELECT id, parent_id
    FROM locations
    WHERE id = 3
    UNION ALL
    SELECT l.id, l.parent_id
    FROM locations l
    JOIN location_hierarchy lh ON l.id = lh.parent_id
),
target_entry AS (
    SELECT id AS target_id, start_date, end_date
    FROM entries
    WHERE location_id = 3
      AND name = 'Christmas school vacation'
),
related_entries AS (
    SELECT e.id, e.start_date, e.end_date
    FROM entries e
    JOIN location_hierarchy lh ON e.location_id = lh.id
    WHERE (e.start_date <= (SELECT end_date + INTERVAL '1 day' FROM target_entry)
           AND e.end_date >= (SELECT start_date - INTERVAL '1 day' FROM target_entry))
),
-- 递归合并重叠/相邻时间段
merged_periods AS (
    SELECT start_date, end_date, ARRAY[id] AS entry_ids
    FROM related_entries
    UNION ALL
    SELECT mp.start_date, GREATEST(mp.end_date, re.end_date), mp.entry_ids || re.id
    FROM merged_periods mp
    JOIN related_entries re ON re.start_date <= mp.end_date + INTERVAL '1 day'
                           AND re.id <> ALL(mp.entry_ids)
                           AND re.start_date >= mp.start_date
),
-- 去重取最终合并结果
final_merged AS (
    SELECT start_date, end_date, entry_ids
    FROM merged_periods mp
    WHERE NOT EXISTS (
        SELECT 1 FROM merged_periods mp2
        WHERE mp2.start_date <= mp.start_date
          AND mp2.end_date >= mp.end_date
          AND array_length(mp2.entry_ids, 1) > array_length(mp.entry_ids, 1)
    )
)
SELECT
    -- 目标条目自身天数
    (SELECT (end_date - start_date + INTERVAL '1 day')::INT FROM target_entry) AS target_days,
    -- 合并后总无学天数（含时间段内所有日期）
    (end_date - start_date + INTERVAL '1 day')::INT AS total_days,
    -- 聚合的关联entry_ids
    entry_ids,
    -- 合并后的真实起止日期
    start_date AS actual_start,
    end_date AS actual_end
FROM final_merged;

4. 扩展：纳入相邻未被占用的周末天数 #

如果需要将合并时间段前后的连续未占用周末也计入总天数，可添加如下计算逻辑：

-- 在final_merged基础上扩展
SELECT
    target_days,
    total_days + 
        -- 计算合并开始日前的连续空闲周末天数
        (SELECT COUNT(*) 
         FROM generate_series(
             (start_date - INTERVAL '7 days')::DATE, 
             (start_date - INTERVAL '1 day')::DATE, 
             INTERVAL '1 day'
         ) d
         WHERE EXTRACT(DOW FROM d) IN (0,6)
         AND NOT EXISTS (
             SELECT 1 FROM entries e
             JOIN location_hierarchy lh ON e.location_id = lh.id
             WHERE d BETWEEN e.start_date AND e.end_date
         )) +
        -- 计算合并结束日后的连续空闲周末天数
        (SELECT COUNT(*) 
         FROM generate_series(
             (end_date + INTERVAL '1 day')::DATE, 
             (end_date + INTERVAL '7 days')::DATE, 
             INTERVAL '1 day'
         ) d
         WHERE EXTRACT(DOW FROM d) IN (0,6)
         AND NOT EXISTS (
             SELECT 1 FROM entries e
             JOIN location_hierarchy lh ON e.location_id = lh.id
             WHERE d BETWEEN e.start_date AND e.end_date
         )) AS total_days_with_adjacent_weekends,
    entry_ids,
    actual_start,
    actual_end
FROM final_merged;

关键说明 #

• 递归CTE确保覆盖目标地点的所有上级层级，避免遗漏国家/州级的银行假日等条目
• 重叠/相邻的判断逻辑可根据业务需求调整（如是否允许间隔多天的“相邻”）
• 数组聚合entry_ids可清晰追踪所有关联的假期条目，便于后续溯源
• 若需仅统计非工作日的无学天数，可在天数计算时添加EXTRACT(DOW FROM d) NOT IN (1,2,3,4,5)过滤

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