SQL补全时间戳间隙并拆分行以监控完整日期需求实现

假设你的状态记录表结构如下（请根据实际表结构调整字段名）：

CREATE TABLE StatusRecords (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    StartDateTime DATETIME2(0),
    EndDateTime DATETIME2(0),
    StatusID INT
);

以下是针对四个需求的分步实现方案：

1. 拆分跨天记录 #

使用递归CTE将跨天的时间段拆分为每日分段，当日结束时间设为23:59:59，次日生成从00:00:00开始的新记录：

WITH SplitRecords AS (
    -- 初始记录：处理非跨天/首次跨天分段
    SELECT
        StartDateTime,
        CASE 
            WHEN CAST(StartDateTime AS DATE) = CAST(EndDateTime AS DATE) THEN EndDateTime
            ELSE DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(StartDateTime AS DATE)))
        END AS EndDateTime,
        StatusID,
        CASE 
            WHEN CAST(StartDateTime AS DATE) != CAST(EndDateTime AS DATE) THEN EndDateTime
            ELSE NULL
        END AS RemainingEnd
    FROM StatusRecords

    UNION ALL

    -- 递归生成后续日期的分段记录
    SELECT
        DATEADD(DAY, 1, CAST(Previous.StartDateTime AS DATE)) AS StartDateTime,
        CASE 
            WHEN CAST(DATEADD(DAY, 1, CAST(Previous.StartDateTime AS DATE)) AS DATE) = CAST(Previous.RemainingEnd AS DATE) THEN Previous.RemainingEnd
            ELSE DATEADD(SECOND, -1, DATEADD(DAY, 2, CAST(Previous.StartDateTime AS DATE)))
        END AS EndDateTime,
        Previous.StatusID,
        CASE 
            WHEN CAST(DATEADD(DAY, 1, CAST(Previous.StartDateTime AS DATE)) AS DATE) != CAST(Previous.RemainingEnd AS DATE) THEN Previous.RemainingEnd
            ELSE NULL
        END AS RemainingEnd
    FROM SplitRecords Previous
    WHERE Previous.RemainingEnd IS NOT NULL
)
SELECT StartDateTime, EndDateTime, StatusID
INTO #SplitTemp
FROM SplitRecords
OPTION (MAXRECURSION 0); -- 跨天超过100天需开启此选项

2-4. 填充间隙、补全当日结束、补全无数据日期 #

通过生成完整日期范围和每日时间轴，关联拆分后的记录，完成剩余三个需求：

-- 生成需要覆盖的所有日期范围
WITH DateRange AS (
    SELECT MIN(CAST(StartDateTime AS DATE)) AS DateVal
    FROM #SplitTemp
    UNION ALL
    SELECT DATEADD(DAY, 1, DateVal)
    FROM DateRange
    WHERE DateVal <= (SELECT MAX(CAST(EndDateTime AS DATE)) FROM #SplitTemp)
),
-- 生成每日完整时间区间（00:00:00 至 23:59:59）
DailyFullRange AS (
    SELECT
        CAST(DateVal AS DATETIME2(0)) AS StartOfDay,
        DATEADD(SECOND, -1, DATEADD(DAY, 1, DateVal)) AS EndOfDay
    FROM DateRange
),
-- 整合所有分段：已存在状态、间隙、补全日结束、无数据日期
DailySegments AS (
    -- 保留拆分后的有效状态段
    SELECT
        s.StartDateTime,
        s.EndDateTime,
        s.StatusID
    FROM DailyFullRange d
    JOIN #SplitTemp s ON CAST(s.StartDateTime AS DATE) = d.DateVal

    UNION ALL

    -- 填充同一日内状态间的间隙（需求2）
    SELECT
        DATEADD(SECOND, 1, prev.EndDateTime) AS StartDateTime,
        curr.StartDateTime AS EndDateTime,
        0 AS StatusID
    FROM DailyFullRange d
    JOIN #SplitTemp prev ON CAST(prev.StartDateTime AS DATE) = d.DateVal
    JOIN #SplitTemp curr ON CAST(curr.StartDateTime AS DATE) = d.DateVal
        AND curr.StartDateTime > prev.EndDateTime
    WHERE NOT EXISTS (
        SELECT 1 FROM #SplitTemp s
        WHERE CAST(s.StartDateTime AS DATE) = d.DateVal
            AND s.StartDateTime > prev.EndDateTime
            AND s.StartDateTime < curr.StartDateTime
    )

    UNION ALL

    -- 补全日开始到第一个状态的间隙（需求2）
    SELECT
        d.StartOfDay AS StartDateTime,
        MIN(s.StartDateTime) AS EndDateTime,
        0 AS StatusID
    FROM DailyFullRange d
    JOIN #SplitTemp s ON CAST(s.StartDateTime AS DATE) = d.DateVal
    GROUP BY d.StartOfDay
    HAVING MIN(s.StartDateTime) > d.StartOfDay

    UNION ALL

    -- 补全最后一个状态到当日结束（需求3）
    SELECT
        DATEADD(SECOND, 1, MAX(s.EndDateTime)) AS StartDateTime,
        d.EndOfDay AS EndDateTime,
        0 AS StatusID
    FROM DailyFullRange d
    JOIN #SplitTemp s ON CAST(s.StartDateTime AS DATE) = d.DateVal
    GROUP BY d.StartOfDay, d.EndOfDay
    HAVING MAX(s.EndDateTime) < d.EndOfDay

    UNION ALL

    -- 补全无数据的日期（需求4）
    SELECT
        d.StartOfDay AS StartDateTime,
        d.EndOfDay AS EndDateTime,
        0 AS StatusID
    FROM DailyFullRange d
    WHERE NOT EXISTS (
        SELECT 1 FROM #SplitTemp s WHERE CAST(s.StartDateTime AS DATE) = d.DateVal
    )
)
-- 去重并输出最终结果
SELECT DISTINCT
    StartDateTime,
    EndDateTime,
    StatusID
FROM DailySegments
WHERE StartDateTime <= EndDateTime
ORDER BY StartDateTime;

-- 清理临时表
DROP TABLE #SplitTemp;

关键说明 #

• 递归CTESplitRecords彻底拆分所有跨天记录，确保每条记录仅包含单日时间段。
• DailyFullRange生成完整的日期覆盖范围，确保无遗漏日期。
• DailySegments通过多组UNION逻辑，一次性完成间隙填充、当日补全、无数据日期补全三个需求。

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