sql server时间处理问题

Question

现在有一个sql server数据表，有时间列以及多台设备的实时电流大小，电流每两分钟采集一次，现在假设规定设备电流大于100时处于工作状态，大于0小于100为通电但不工作（待机状态），等于0为停机状态，但由于工作时其他设备影响，电流会出现波动，即使在运行状态也可能出现一段时间里电流小于100的情况，但此时设备仍处于运行状态（图中蓝色部分），所以规定电流小于100的时间不超过半小时即为运行状态，超过半小时或者半小时内待机时间超过运行时间则为待机状态，想要计算出设备一天内（或者规定时间内）工作时长、停机时长、待机时长

Answer 1

WITH StateChanges AS (  
    SELECT  
        DeviceID,  
        TimeStamp,  
        Current,  
        CASE   
            WHEN Current > 100 THEN 'Working'  
            WHEN Current > 0 AND Current <= 100 THEN 'Standby'  
            ELSE 'Off'  
        END AS State,  
        LEAD(TimeStamp) OVER (PARTITION BY DeviceID ORDER BY TimeStamp) AS NextTimeStamp,  
        LEAD(Current) OVER (PARTITION BY DeviceID ORDER BY TimeStamp) AS NextCurrent  
    FROM  
        DeviceCurrent  
    WHERE  
        -- 假设我们计算特定日期的一天的数据  
        CAST(TimeStamp AS DATE) = '2024-08-28'  
),  
StateDurations AS (  
    SELECT  
        DeviceID,  
        TimeStamp AS StartTime,  
        CASE   
            WHEN NextTimeStamp IS NULL THEN CAST('23:59:59' AS TIME)  
            ELSE DATEADD(MINUTE, 2, CAST(TimeStamp AS TIME)) -- 假设每次记录间隔为2分钟  
        END AS EndTime,  
        State,  
        CASE   
            WHEN LEAD(State) OVER (PARTITION BY DeviceID ORDER BY TimeStamp) IS NULL  
                OR State <> LEAD(State) OVER (PARTITION BY DeviceID ORDER BY TimeStamp)  
            THEN DATEDIFF(MINUTE, TimeStamp,   
                CASE   
                    WHEN NextTimeStamp IS NULL THEN CAST('2023-04-01 23:59:59' AS DATETIME)  
                    ELSE DATEADD(MINUTE, 2, CAST(TimeStamp AS DATETIME))  
                END)  
            ELSE 0  
        END AS DurationInMinutes  
    FROM  
        StateChanges  
),  
AggregatedStates AS (  
    SELECT  
        DeviceID,  
        State,  
        SUM(DurationInMinutes) AS TotalDurationInMinutes  
    FROM  
        StateDurations  
    GROUP BY  
        DeviceID,  
        State  
),  
FinalResults AS (  
    SELECT  
        DeviceID,  
        SUM(CASE WHEN State = 'Working' THEN TotalDurationInMinutes ELSE 0 END) AS WorkingHours,  
        SUM(CASE WHEN State = 'Off' THEN TotalDurationInMinutes ELSE 0 END) AS OffHours,  
        SUM(CASE WHEN State = 'Standby' THEN TotalDurationInMinutes ELSE 0 END) AS StandbyHours  
    FROM  
        AggregatedStates  
    GROUP BY  
        DeviceID  
)  
SELECT * FROM FinalResults;