如何实现设备状态跨时段小时拆分记录？是否需插入数据？

不用插入新记录，通过CTE（公共表表达式）结合时间序列生成就能实现需求。核心思路是先提取每个状态的完整起止时段，再用小时序列拆分这些时段，最后生成每个小时对应的记录。

实现步骤及SQL代码 #

WITH StatusSegments AS (
    -- 提取每个状态的起止时间，保留原始Flag逻辑
    SELECT
        EventTime AS StartTime,
        -- 用LEAD获取下一个状态的时间作为当前状态的结束时间，最后一条状态用当前时间兜底
        LEAD(EventTime, 1, GETDATE()) OVER (PARTITION BY DeviceName ORDER BY EventTime) AS EndTime,
        DeviceStatus,
        DeviceName,
        CASE 
            WHEN DeviceStatus = LAG(DeviceStatus) OVER (PARTITION BY DeviceName ORDER BY EventTime)     
                THEN 0 
                ELSE 1 
        END AS OriginalFlag
    FROM [EventSourcing].[Fact].[Operations]
    WHERE DeviceName LIKE 'Device%' 
      AND DateID IN (20241129, 20241130)
),
HourlySequence AS (
    -- 生成覆盖所有状态时段的小时序列
    SELECT 
        DATEADD(HOUR, n, DATEADD(DAY, DATEDIFF(DAY, 0, MinStart.MinStartTime), 0)) AS HourStart,
        DATEADD(HOUR, n+1, DATEADD(DAY, DATEDIFF(DAY, 0, MinStart.MinStartTime), 0)) AS HourEnd
    FROM (
        -- 生成足够覆盖时间范围的数字序列
        SELECT TOP (DATEDIFF(HOUR, (SELECT MIN(StartTime) FROM StatusSegments), (SELECT MAX(EndTime) FROM StatusSegments)) + 1)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.all_columns
    ) Numbers
    CROSS JOIN (SELECT MIN(StartTime) AS MinStartTime FROM StatusSegments) MinStart
)
-- 关联状态段与小时序列，拆分出每个小时的记录
SELECT
    -- 该小时内状态的实际起始时间
    CASE WHEN ss.StartTime > hs.HourStart THEN ss.StartTime ELSE hs.HourStart END AS EventTime,
    ss.DeviceStatus,
    ss.DeviceName,
    -- 仅状态段的第一个小时记录保留OriginalFlag，其余拆分记录Flag设为0
    CASE WHEN CASE WHEN ss.StartTime > hs.HourStart THEN ss.StartTime ELSE hs.HourStart END = ss.StartTime THEN ss.OriginalFlag ELSE 0 END AS Flag,
    -- 可选：计算该小时内状态持续的秒数
    DATEDIFF(SECOND, 
        CASE WHEN ss.StartTime > hs.HourStart THEN ss.StartTime ELSE hs.HourStart END,
        CASE WHEN ss.EndTime < hs.HourEnd THEN ss.EndTime ELSE hs.HourEnd END
    ) AS DurationSeconds
FROM StatusSegments ss
JOIN HourlySequence hs 
    -- 筛选出状态段与小时有重叠的情况
    ON ss.StartTime < hs.HourEnd 
    AND ss.EndTime > hs.HourStart
ORDER BY ss.DeviceName, EventTime;

代码说明 #

1. StatusSegments：通过LEAD函数获取每个状态的结束时间（即下一条状态记录的时间），同时保留你原来的Flag判断逻辑。
2. HourlySequence：生成覆盖所有状态时段的每小时起止时间，利用系统表sys.all_columns生成数字序列，无需手动创建数字表。
3. 最终关联查询：找出状态段与小时的重叠区间，计算每个小时内状态的实际起止时间，仅状态段的第一条（即原始状态变化的起始小时）保留Flag=1，其余拆分出的小时记录Flag设为0，还可额外计算该小时内的持续时长。

以你提到的18:15:07到次日04:44:02的状态为例，该查询会生成18:15:07-19:00:00、19:00:00-20:00:00……04:00:00-04:44:02的多条记录，其中只有18:15:07对应的记录Flag=1，其余均为0。

内容的提问来源于stack exchange，提问作者Andrea Da Como