系统版本时态表关联变更历史查询方案咨询及相关疑问

我拥有3个系统版本时态表，分别为FOO、BAR和BAZ，它们的关联关系为：

FOO -> 1-N -> BAR -> 1-N -> BAZ

我希望获取指定FOO行的完整变更历史，包括其关联的BAR和BAZ行的所有变更，形成统一时间线。我当前采用FOR SYSTEM_TIME ALL并基于重叠时段关联的方案：

SELECT f.*, b.*, bz.*
FROM FOO FOR SYSTEM_TIME ALL f
JOIN BAR FOR SYSTEM_TIME ALL b
    ON b.FooId = f.FooId
    AND b.StartTime < f.EndTime
    AND b.EndTime   > f.StartTime
JOIN BAZ FOR SYSTEM_TIME ALL bz
    ON bz.BarId = b.BarId
    AND bz.StartTime < b.EndTime
    AND bz.EndTime   > b.StartTime
WHERE f.FooId = 1
ORDER BY f.StartTime, b.StartTime, bz.StartTime;

请问该方案是否合理？我担心三表未同步更新时会出现重复行或遗漏变更的情况。另外，我注意到FOO_History中存在StartTime与子表更新时间匹配，但FOO列未变更的行，这是否正常？

一、当前关联方案的合理性与潜在问题 #

你的方案逻辑方向是对的，通过FOR SYSTEM_TIME ALL拉取全量历史版本，再用时间重叠条件关联，能捕获各表版本在时间上共存的状态，但存在两个明显问题：

• 重复行问题：当父表的一个版本时段内，子表存在多个版本时，会产生笛卡尔积式的重复行。比如FOO的某个有效时段里，BAR有3个版本，每个BAR版本对应2个BAZ版本，就会生成6行冗余数据，不利于时间线的清晰梳理。
• 遗漏变更问题：如果子表的变更时段完全落在父表某个版本时段的边界上，会被过滤。比如FOO的版本时段是[2024-01-01, 2024-01-10)，BAR在2024-01-10更新的版本StartTime等于FOO的EndTime，此时b.StartTime < f.EndTime不成立，这个BAR的变更就会被遗漏。

优化思路 #

要构建准确的统一时间线，建议先收集所有关键时间点，拆分成连续的时间区间，再关联各表在每个区间内的有效版本：

-- 收集所有涉及的时间点
WITH AllTimePoints AS (
    SELECT StartTime AS Point FROM FOO FOR SYSTEM_TIME ALL WHERE FooId = 1
    UNION
    SELECT EndTime AS Point FROM FOO FOR SYSTEM_TIME ALL WHERE FooId = 1
    UNION
    SELECT StartTime AS Point FROM BAR FOR SYSTEM_TIME ALL WHERE FooId = 1
    UNION
    SELECT EndTime AS Point FROM BAR FOR SYSTEM_TIME ALL WHERE FooId = 1
    UNION
    SELECT StartTime AS Point FROM BAZ FOR SYSTEM_TIME ALL 
        WHERE BarId IN (SELECT BarId FROM BAR FOR SYSTEM_TIME ALL WHERE FooId = 1)
    UNION
    SELECT EndTime AS Point FROM BAZ FOR SYSTEM_TIME ALL 
        WHERE BarId IN (SELECT BarId FROM BAR FOR SYSTEM_TIME ALL WHERE FooId = 1)
),
-- 生成连续的时间区间
TimeIntervals AS (
    SELECT 
        Point AS IntervalStart,
        LEAD(Point) OVER (ORDER BY Point) AS IntervalEnd
    FROM AllTimePoints
    WHERE Point IS NOT NULL
)
-- 关联各表在区间内的有效版本
SELECT 
    f.*, b.*, bz.*,
    ti.IntervalStart, ti.IntervalEnd
FROM TimeIntervals ti
LEFT JOIN FOO FOR SYSTEM_TIME ALL f
    ON f.FooId = 1
    AND f.StartTime <= ti.IntervalStart
    AND f.EndTime >= ti.IntervalEnd
LEFT JOIN BAR FOR SYSTEM_TIME ALL b
    ON b.FooId = f.FooId
    AND b.StartTime <= ti.IntervalStart
    AND b.EndTime >= ti.IntervalEnd
LEFT JOIN BAZ FOR SYSTEM_TIME ALL bz
    ON bz.BarId = b.BarId
    AND bz.StartTime <= ti.IntervalStart
    AND bz.EndTime >= ti.IntervalEnd
WHERE ti.IntervalEnd IS NOT NULL
ORDER BY ti.IntervalStart;

这种方式能确保每个时间区间内的各表版本都是当时的有效状态，既不会遗漏变更，也能避免冗余的重复行。

二、FOO_History中无变更但时间匹配子表更新的行是否正常 #

这种情况是正常的，原因通常有两种：

1. 显式无变更更新：如果更新子表时，执行了UPDATE FOO语句（哪怕没有修改任何列），时态表会基于事务执行时间生成新的历史记录——系统版本时态表是按事务触发的，不是按列变更触发的。
2. 框架/ORM自动触发：部分应用框架或ORM工具在更新子表时，会默认更新父表的乐观锁版本号或其他控制字段，即使父表业务列没有变化，也会生成新的FOO版本。

如果不需要这类无变更的历史行，可以在查询时过滤：

SELECT *
FROM FOO_HISTORY h
WHERE NOT EXISTS (
    SELECT 1 FROM FOO_HISTORY h_prev
    WHERE h_prev.FooId = h.FooId
    AND h_prev.EndTime = h.StartTime
    -- 替换为实际业务列，避免比较系统版本字段
    AND h_prev.Col1 = h.Col1 
    AND h_prev.Col2 = h.Col2
    -- ...列出所有业务列
);

注意：多数数据库不支持直接用*比较所有列，需要明确列出业务字段来判断是否有实际变更。

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