如何从多份带生效/结束日期的属性表生成实体属性历史表？

要生成所有属性组合的有效历史状态，核心思路是先提取所有属性变更的时间节点，将时间轴分割为连续的区间，再为每个区间匹配对应生效的属性值。以下是具体实现方案：

单实体版本（以entity_id=1为例） #

-- 提取所有属性变更的时间节点
WITH all_time_points AS (
    SELECT effective_date AS point_date FROM attribute_a WHERE entity_id = 1
    UNION
    SELECT end_date AS point_date FROM attribute_a WHERE entity_id = 1 AND end_date IS NOT NULL
    UNION
    SELECT effective_date AS point_date FROM attribute_b WHERE entity_id = 1
    UNION
    SELECT end_date AS point_date FROM attribute_b WHERE entity_id = 1 AND end_date IS NOT NULL
),
-- 生成连续的时间区间
time_intervals AS (
    SELECT
        point_date AS effective_date,
        LEAD(point_date) OVER (ORDER BY point_date) AS end_date
    FROM all_time_points
    ORDER BY point_date
)
-- 匹配每个区间的属性值
SELECT
    1 AS entity_id,
    a.attribute_value AS attribute_a_value,
    b.attribute_value AS attribute_b_value,
    ti.effective_date,
    ti.end_date
FROM time_intervals ti
LEFT JOIN attribute_a a
    ON a.entity_id = 1
    AND a.effective_date <= ti.effective_date
    AND (a.end_date >= ti.end_date OR a.end_date IS NULL)
LEFT JOIN attribute_b b
    ON b.entity_id = 1
    AND b.effective_date <= ti.effective_date
    AND (b.end_date >= ti.end_date OR b.end_date IS NULL)
ORDER BY ti.effective_date;

多实体批量处理版本 #

如果需要同时处理所有实体，只需调整时间节点和区间的生成逻辑，按entity_id分组：

WITH all_time_points AS (
    SELECT entity_id, effective_date AS point_date FROM attribute_a
    UNION
    SELECT entity_id, end_date AS point_date FROM attribute_a WHERE end_date IS NOT NULL
    UNION
    SELECT entity_id, effective_date AS point_date FROM attribute_b
    UNION
    SELECT entity_id, end_date AS point_date FROM attribute_b WHERE end_date IS NOT NULL
),
time_intervals AS (
    SELECT
        entity_id,
        point_date AS effective_date,
        LEAD(point_date) OVER (PARTITION BY entity_id ORDER BY point_date) AS end_date
    FROM all_time_points
)
SELECT
    ti.entity_id,
    a.attribute_value AS attribute_a_value,
    b.attribute_value AS attribute_b_value,
    ti.effective_date,
    ti.end_date
FROM time_intervals ti
LEFT JOIN attribute_a a
    ON a.entity_id = ti.entity_id
    AND a.effective_date <= ti.effective_date
    AND (a.end_date >= ti.end_date OR a.end_date IS NULL)
LEFT JOIN attribute_b b
    ON b.entity_id = ti.entity_id
    AND b.effective_date <= ti.effective_date
    AND (b.end_date >= ti.end_date OR b.end_date IS NULL)
ORDER BY ti.entity_id, ti.effective_date;

兼容低版本MySQL（无LEAD函数） #

如果使用MySQL 8.0以下版本，用自连接替代LEAD函数生成时间区间：

WITH all_time_points AS (
    SELECT entity_id, effective_date AS point_date FROM attribute_a
    UNION
    SELECT entity_id, end_date AS point_date FROM attribute_a WHERE end_date IS NOT NULL
    UNION
    SELECT entity_id, effective_date AS point_date FROM attribute_b
    UNION
    SELECT entity_id, end_date AS point_date FROM attribute_b WHERE end_date IS NOT NULL
),
time_intervals AS (
    SELECT
        t1.entity_id,
        t1.point_date AS effective_date,
        MIN(t2.point_date) AS end_date
    FROM all_time_points t1
    LEFT JOIN all_time_points t2
        ON t1.entity_id = t2.entity_id
        AND t2.point_date > t1.point_date
    GROUP BY t1.entity_id, t1.point_date
)
SELECT
    ti.entity_id,
    a.attribute_value AS attribute_a_value,
    b.attribute_value AS attribute_b_value,
    ti.effective_date,
    ti.end_date
FROM time_intervals ti
LEFT JOIN attribute_a a
    ON a.entity_id = ti.entity_id
    AND a.effective_date <= ti.effective_date
    AND (a.end_date >= ti.end_date OR a.end_date IS NULL)
LEFT JOIN attribute_b b
    ON b.entity_id = ti.entity_id
    AND b.effective_date <= ti.effective_date
    AND (b.end_date >= ti.end_date OR b.end_date IS NULL)
ORDER BY ti.entity_id, ti.effective_date;

方案优势 #

• 精准覆盖所有变更节点：通过收集所有属性的生效、失效时间，确保每个区间内的属性组合唯一
• 逻辑清晰易维护：用CTE分步处理时间节点、区间生成、属性匹配，可读性强
• 兼容性好：提供了不同数据库版本的适配方案
• 性能高效：只要给entity_id、effective_date、end_date建立索引，就能快速完成关联查询

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