如何基于TripID与datetime2时间谓词关联两表避免重复或遗漏数据

问题场景复现 #

你需要基于TripID关联表A（行程分段表）和表B（告警表），要求告警的TimeTriggered落在分段的SegmentStart到SegmentEnd区间内，同时避免边界时间点命中两个分段导致重复关联，也不能漏匹配边界点的告警，还要保留索引性能。
表关联示意图：

解决方案 #

方案1：严格连续分段场景（性能最优） #

如果你的行程分段是连续无Gap、无重叠的（即前一个分段的SegmentEnd等于后一个分段的SegmentStart），可以直接用边界归属规则优化关联条件，完全命中索引：

FROM TableA A
INNER JOIN TableB B
ON A.TripID = B.TripID
AND (
    -- 普通分段用左闭右开，避免边界点匹配前一段和后一段
    B.TimeTriggered >= A.SegmentStart 
    AND B.TimeTriggered < A.SegmentEnd
    -- 最后一个分段用左闭右闭，捕获刚好落在行程最终结束时间的告警
    OR (
        A.SegmentEnd = (SELECT MAX(SegmentEnd) FROM TableA WHERE TripID = A.TripID)
        AND B.TimeTriggered = A.SegmentEnd
    )
)

该方案无需额外排序、聚合操作，所有条件都可以命中联合索引，性能最高。

方案2：通用全场景（兼容分段重叠/有Gap的情况） #

如果分段可能存在重叠、Gap，或者不确定分段是否严格连续，可以用关联后去重的方式实现：

SELECT * FROM (
    SELECT 
        A.*,
        B.*,
        -- 同一条告警匹配到多个分段时，仅保留分段起始时间更早的匹配结果（可自行调整排序规则修改归属逻辑，若B表无单独告警ID，PARTITION BY替换为B表主键即可）
        ROW_NUMBER() OVER (PARTITION BY B.TripID, B.AlarmID ORDER BY A.SegmentStart ASC) AS rn
    FROM TableA A
    INNER JOIN TableB B
    ON A.TripID = B.TripID
    AND B.TimeTriggered BETWEEN A.SegmentStart AND A.SegmentEnd
) t
WHERE t.rn = 1

该方案适用性最广，只要给表加对索引，性能也能满足绝大多数业务场景。

索引优化建议 #

两个方案都可以通过建立以下联合索引保证性能，不会出现全表扫描：

• 给TableA建立(TripID, SegmentStart, SegmentEnd)联合索引
• 给TableB建立(TripID, TimeTriggered)联合索引

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