基于日期范围关联表并拆分跨区间记录的SQL实现需求问询

这个场景我之前处理过不少，本质就是解决细粒度时间区间与粗粒度区间的交叉匹配+拆分问题，用CTE结合区间重叠判断就能搞定。下面我用通用SQL语法来实现，适配大多数主流关系型数据库（SQL Server、PostgreSQL、MySQL等，个别函数可能需要微调）。

核心思路 #

1. 先关联两张表：基于EquipIdent匹配同一设备，同时判断Table A的记录区间与Table B的记录区间是否存在重叠（这是处理跨区间记录的关键）。
2. 对每一对重叠的区间，计算实际的有效重叠段：
   • 重叠段的起始时间 = Table A记录的开始时间和Table B记录的开始时间中较大的那个
   • 重叠段的结束时间 = Table A记录的结束时间和Table B记录的结束时间中较小的那个
3. 过滤掉无效的区间（起始时间 >= 结束时间的情况，确保数据有效性）。
4. 将处理后的所有有效区间插入到Table A中（如果需要替换原跨区间记录，可先删除再插入）。

完整SQL实现 #

第一步：创建示例表（可选，用于测试） #

-- 创建Table A
CREATE TABLE TableA (
    RecordEventStart DATETIME,
    RecordEventFinish DATETIME,
    EquipIdent VARCHAR(50)
);

-- 插入示例数据
INSERT INTO TableA VALUES
('2022-05-08 06:00:00.000', '2022-05-08 06:10:32.000', 'EQ001'),
('2022-05-08 06:10:32.000', '2022-05-08 06:15:26.000', 'EQ001'),
('2022-05-08 06:15:26.000', '2022-05-08 06:15:35.000', 'EQ001'),
('2022-05-08 07:56:45.000', '2022-05-08 08:08:55.000', 'EQ001'),
('2022-05-08 08:08:55.000', '2022-05-08 09:14:10.000', 'EQ001');

-- 创建Table B
CREATE TABLE TableB (
    RecordEventStart DATETIME,
    RecordEventFinish DATETIME,
    EquipIdent VARCHAR(50),
    CostCode VARCHAR(10)
);

-- 插入示例数据
INSERT INTO TableB VALUES
('2022-05-08 06:00:00.000', '2022-05-08 08:44:29.000', 'EQ001', 'A'),
('2022-05-08 08:44:29.000', '2022-05-08 18:00:00.000', 'EQ001', 'B');

第二步：处理并插入记录 #

WITH ProcessedRecords AS (
    SELECT
        -- 取两个区间起始时间的较大值作为新记录的开始
        GREATEST(a.RecordEventStart, b.RecordEventStart) AS NewRecordStart,
        -- 取两个区间结束时间的较小值作为新记录的结束
        LEAST(a.RecordEventFinish, b.RecordEventFinish) AS NewRecordFinish,
        a.EquipIdent,
        b.CostCode
    FROM TableA a
    JOIN TableB b
        ON a.EquipIdent = b.EquipIdent
        -- 关键：判断两个区间是否重叠
        AND a.RecordEventStart < b.RecordEventFinish
        AND a.RecordEventFinish > b.RecordEventStart
    -- 确保生成的区间是有效的（起始时间必须小于结束时间）
    WHERE GREATEST(a.RecordEventStart, b.RecordEventStart) < LEAST(a.RecordEventFinish, b.RecordEventFinish)
)
-- 将处理后的记录插入Table A
INSERT INTO TableA (RecordEventStart, RecordEventFinish, EquipIdent)
SELECT NewRecordStart, NewRecordFinish, EquipIdent
FROM ProcessedRecords;

关键细节说明 #

• 区间重叠判断：a.RecordEventStart < b.RecordEventFinish AND a.RecordEventFinish > b.RecordEventStart这个条件能覆盖所有区间重叠的情况，包括完全包含、部分交叉的场景。
• 跨区间拆分：比如Table A中那条08:08:55到09:14:10的记录，会和Table B的两条记录分别匹配，生成两个有效区间：08:08:55-08:44:29（对应CostCode A）和08:44:29-09:14:10（对应CostCode B）。
• 数据库兼容性：如果是MySQL，没有GREATEST和LEAST函数，可以用CASE语句替代：

  -- 替代GREATEST
  CASE WHEN a.RecordEventStart > b.RecordEventStart THEN a.RecordEventStart ELSE b.RecordEventStart END AS NewRecordStart,
  -- 替代LEAST
  CASE WHEN a.RecordEventFinish < b.RecordEventFinish THEN a.RecordEventFinish ELSE b.RecordEventFinish END AS NewRecordFinish
  

可选优化：替换原跨区间记录 #

如果不想保留原有的跨区间记录，而是直接用拆分后的记录替换，可以先删除原记录再插入：

-- 先删除需要拆分的原记录
DELETE FROM TableA
WHERE EXISTS (
    SELECT 1
    FROM TableB b
    WHERE TableA.EquipIdent = b.EquipIdent
        AND TableA.RecordEventStart < b.RecordEventFinish
        AND TableA.RecordEventFinish > b.RecordEventStart
        -- 判断当前记录是否跨多个B的区间
        AND (TableA.RecordEventStart < b.RecordEventStart OR TableA.RecordEventFinish > b.RecordEventFinish)
);

-- 再插入处理后的记录（CTE部分同上）
WITH ProcessedRecords AS (...)
INSERT INTO TableA (...) SELECT ...;

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