SQL中重叠区间连接的最优实现方案咨询

我正在寻找连接多个变更历史表并返回合并后重叠区间变更记录的最佳模式。当前使用的脚本虽能得到正确结果，但存在两个明显不足：

• 扩展性差：新增表时需两两比较日期区间，查询复杂度随表数量上升呈指数级增长
• 冗余分组：必须手动指定所有目标属性列进行分组，才能过滤无变更的重复记录

原实现代码 #

CREATE OR REPLACE TEMP TABLE emp_history (
    emp_id INT,
    mgr_id INT, 
    dept_id INT,
    emp_title VARCHAR,
    start_date DATE,
    end_date DATE
);

CREATE OR REPLACE TEMP TABLE dept_history (
    dept_id INT,
    dept_cost_center varchar,
    start_date DATE,
    end_date DATE
);


INSERT INTO emp_history VALUES 
(1, 100, 1, 'Developer', '2023-01-01', '2023-06-30'),
(1, 100, 1, 'Senior Developer', '2023-07-01', '9999-12-31'),
(100, NULL, 1, 'Manager', '2023-01-01','2023-09-30'),
(100, NULL, 1, 'Senior Manager', '2023-10-01', '9999-12-31');

INSERT INTO dept_history VALUES
(1, 'C1', '2023-01-01', '2023-02-28'),
(1, 'C2', '2023-03-01', '9999-12-31');

SELECT * FROM emp_history ORDER BY emp_id,start_date;
SELECT * FROM dept_history ORDER BY dept_id,start_date;

SELECT 
    e.emp_id,
    e.dept_id,   
    e.mgr_id,
    e.emp_title, 
    m.emp_title AS mgr_title, 
    d.dept_cost_center,
    MAX(GREATEST(e.start_date, m.start_date, d.start_date)) AS start_date,
    MIN(LEAST(e.end_date, m.end_date, d.end_date)) AS end_date
FROM emp_history e
JOIN emp_history m 
    ON e.mgr_id = m.emp_id
    AND e.start_date <= m.end_date
    AND e.end_date >= m.start_date
JOIN dept_history d
    ON e.dept_id = d.dept_id
    AND e.start_date <= d.end_date
    AND e.end_date >=d.start_date
    AND m.start_date <= d.end_date
    AND m.end_date >=d.start_date
WHERE GREATEST(e.start_date, m.start_date, d.start_date) <= LEAST(e.end_date, m.end_date, d.end_date)
GROUP BY 1,2,3,4,5,6
ORDER BY 1,7;

期望输出 #

我使用的是Snowflake，但方案需适配各类现代数据库，恳请提供改进建议！

核心思路 #

将所有历史表的关键时间点（所有start_date和end_date）提取出来，构建一个连续的时间轴；然后针对每个时间区间，查询各关联表在该区间内的有效状态，最后按状态聚合得到无重复的变更记录。这种方法完美解决原方案的扩展性和分组问题。

具体实现（Snowflake兼容，适配现代数据库） #

-- 步骤1：收集所有关键时间点，去重后排序
WITH all_time_points AS (
    SELECT start_date AS point_date FROM emp_history
    UNION ALL SELECT end_date FROM emp_history
    UNION ALL SELECT start_date FROM dept_history
    UNION ALL SELECT end_date FROM dept_history
),
-- 步骤2：构建连续的时间区间（每个区间的start和end为相邻时间点）
time_intervals AS (
    SELECT 
        point_date AS interval_start,
        LEAD(point_date) OVER (ORDER BY point_date) AS interval_end
    FROM all_time_points
    WHERE point_date != '9999-12-31' -- 排除永久生效的结束日期
),
-- 步骤3：过滤有效区间（start < end）
valid_intervals AS (
    SELECT interval_start, interval_end
    FROM time_intervals
    WHERE interval_end IS NOT NULL
),
-- 步骤4：获取每个区间内员工、经理、部门的有效状态
interval_states AS (
    SELECT
        e.emp_id,
        e.dept_id,
        e.mgr_id,
        e.emp_title,
        m.emp_title AS mgr_title,
        d.dept_cost_center,
        vi.interval_start AS start_date,
        -- 处理永久生效的情况，用9999-12-31替代NULL
        CASE WHEN vi.interval_end = '9999-12-31' THEN vi.interval_end ELSE vi.interval_end END AS end_date
    FROM valid_intervals vi
    -- 关联员工表，取区间内有效的记录
    JOIN emp_history e 
        ON vi.interval_start < e.end_date 
        AND vi.interval_end > e.start_date
    -- 关联经理表，取区间内有效的记录
    LEFT JOIN emp_history m 
        ON e.mgr_id = m.emp_id
        AND vi.interval_start < m.end_date 
        AND vi.interval_end > m.start_date
    -- 关联部门表，取区间内有效的记录
    LEFT JOIN dept_history d 
        ON e.dept_id = d.dept_id
        AND vi.interval_start < d.end_date 
        AND vi.interval_end > d.start_date
),
-- 步骤5：合并连续的相同状态区间（解决无变更的重复记录问题）
merged_states AS (
    SELECT
        emp_id,
        dept_id,
        mgr_id,
        emp_title,
        mgr_title,
        dept_cost_center,
        MIN(start_date) AS start_date,
        MAX(end_date) AS end_date
    FROM (
        SELECT
            *,
            -- 用窗口函数标记连续相同状态的分组
            SUM(flag) OVER (PARTITION BY emp_id ORDER BY start_date) AS state_group
        FROM (
            SELECT
                *,
                -- 比较当前状态与前一行是否相同，不同则标记1
                CASE WHEN LAG(emp_title) OVER (PARTITION BY emp_id ORDER BY start_date) = emp_title
                          AND LAG(mgr_title) OVER (PARTITION BY emp_id ORDER BY start_date) = mgr_title
                          AND LAG(dept_cost_center) OVER (PARTITION BY emp_id ORDER BY start_date) = dept_cost_center
                     THEN 0 ELSE 1 END AS flag
            FROM interval_states
        ) t1
    ) t2
    GROUP BY emp_id, dept_id, mgr_id, emp_title, mgr_title, dept_cost_center, state_group
    ORDER BY emp_id, start_date
)
SELECT * FROM merged_states;

方案优势 #

1. 高扩展性：新增历史表时，只需在all_time_points中添加该表的start/end_date，再在interval_states中增加对应的关联逻辑即可，无需修改复杂的多表连接条件
2. 自动去重：通过窗口函数标记连续相同状态的区间，避免手动指定所有分组列；即使新增属性列，只需在状态比较的CASE语句中添加对应字段即可
3. 兼容性强：使用标准SQL语法（窗口函数、CTE），适配Snowflake、PostgreSQL、BigQuery等所有现代数据库

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