基于起止时间连接层级表与主管表并生成连接历史的技术实现

嘿，这个跨表生成时间维度的连接历史问题我熟，刚好之前帮业务部门处理过类似的员工层级+主管变更的场景，咱们一步步来搞定它，确保覆盖你提到的三种变更情况：仅层级变、仅主管变、两者同时变。

核心思路拆解 #

要生成符合要求的连接历史，关键是把两个表的所有时间变更点合并，生成连续的时间区间，再为每个区间匹配对应的层级和主管信息。这样不管是单一维度变更还是双维度同时变更，都能被精准捕捉到。

示例表结构与测试数据 #

先定义两个业务表的结构和测试数据，方便你理解逻辑（这里用MySQL语法，其他数据库可微调）：

Table A（层级表） #

CREATE TABLE table_a (
    entity_id VARCHAR(20), -- 关联实体ID，比如员工ID
    level VARCHAR(10),     -- 层级，如L1/L2/L3
    starttime DATE,        -- 生效开始日期
    endtime DATE           -- 生效结束日期（闭区间）
);

INSERT INTO table_a VALUES
('EMP001', 'L1', '2023-01-01', '2023-03-31'),
('EMP001', 'L2', '2023-04-01', '2023-09-30'); -- 层级长期不变的场景

Table B（主管表） #

CREATE TABLE table_b (
    entity_id VARCHAR(20),
    manager VARCHAR(20),   -- 主管ID/姓名
    starttime DATE,
    endtime DATE
);

INSERT INTO table_b VALUES
('EMP001', 'MGR001', '2023-01-01', '2023-05-31'),
('EMP001', 'MGR002', '2023-06-01', '2023-08-31'),
('EMP001', 'MGR003', '2023-09-01', '2023-12-31'); -- 主管多次变更的场景

通用SQL实现 #

下面的SQL会自动合并两个表的变更时间点，生成每个时间段对应的层级+主管组合：

WITH all_time_points AS (
    -- 收集所有可能的变更时间点：包括两表的生效开始日、失效结束日的次日
    SELECT entity_id, starttime AS point FROM table_a
    UNION
    SELECT entity_id, DATE_ADD(endtime, INTERVAL 1 DAY) AS point FROM table_a
    UNION
    SELECT entity_id, starttime AS point FROM table_b
    UNION
    SELECT entity_id, DATE_ADD(endtime, INTERVAL 1 DAY) AS point FROM table_b
),
time_intervals AS (
    -- 用窗口函数生成连续的时间区间
    SELECT 
        entity_id,
        point AS interval_start,
        LEAD(point, 1) OVER (PARTITION BY entity_id ORDER BY point) AS interval_end
    FROM all_time_points
)
-- 匹配每个区间对应的层级和主管
SELECT 
    ti.entity_id,
    a.level,
    b.manager,
    ti.interval_start AS starttime,
    -- 处理最后一个区间的结束日：如果没有下一个时间点，用'9999-12-31'表示永久生效
    CASE 
        WHEN ti.interval_end IS NULL THEN '9999-12-31'
        ELSE DATE_SUB(ti.interval_end, INTERVAL 1 DAY)
    END AS endtime
FROM time_intervals ti
-- 匹配当前区间内有效的层级记录
LEFT JOIN table_a a 
    ON ti.entity_id = a.entity_id 
    AND ti.interval_start >= a.starttime 
    AND (ti.interval_end IS NULL OR ti.interval_end <= DATE_ADD(a.endtime, INTERVAL 1 DAY))
-- 匹配当前区间内有效的主管记录
LEFT JOIN table_b b 
    ON ti.entity_id = b.entity_id 
    AND ti.interval_start >= b.starttime 
    AND (ti.interval_end IS NULL OR ti.interval_end <= DATE_ADD(b.endtime, INTERVAL 1 DAY))
-- 过滤无效区间（比如超出两表最大生效时间的空区间）
WHERE ti.interval_end IS NOT NULL 
   OR (ti.interval_start <= (SELECT MAX(endtime) FROM table_a WHERE entity_id = ti.entity_id) 
       OR ti.interval_start <= (SELECT MAX(endtime) FROM table_b WHERE entity_id = ti.entity_id))
ORDER BY ti.entity_id, ti.interval_start;

关键逻辑说明 #

1. 收集时间点：all_time_points把两个表的starttime和endtime+1都收集起来，这样能捕捉到所有可能的变更节点（比如层级在2023-04-01变更，主管在2023-06-01变更，这些点都会被纳入）。
2. 生成连续区间：time_intervals用LEAD窗口函数为每个时间点找到下一个时间点，形成连续的区间，比如2023-01-01到2023-04-01，2023-04-01到2023-06-01等。
3. 匹配维度信息：通过左连接两个表，匹配每个区间内有效的层级和主管，这样三种变更场景都会被覆盖：
   • 仅层级变更：区间内层级不同但主管相同；
   • 仅主管变更：区间内主管不同但层级相同；
   • 同时变更：区间内层级和主管都发生变化。

注意事项 #

• 数据库兼容性：不同数据库的日期函数有差异，比如PostgreSQL用endtime + INTERVAL '1 day'，SQL Server用DATEADD(day, 1, endtime)，需要根据实际使用的数据库调整。
• 空结束日期处理：如果业务中endtime为NULL表示当前生效，建议提前把NULL替换为'9999-12-31'，这样时间点收集会更顺畅。
• 实体关联键：确保entity_id是两个表的唯一关联键，PARTITION BY entity_id会保证每个实体的时间区间独立计算。

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