sql server中有关时间的数据处理问题

Question

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

Answer 1

为了实现这个需求，我们可以采用SQL Server中的窗口函数和自连接技术来解决这个问题。下面是一个逐步指南来完成这个任务。

步骤 1: 数据表结构
首先，假设你的数据表结构如下：

DeviceID: 设备ID
Timestamp: 时间戳
Current: 当前电流值
步骤 2: 计算状态
我们需要先定义一个状态字段，用以区分设备的工作状态。可以使用CASE语句来根据电流值设置不同的状态。

步骤 3: 处理短暂的异常状态
对于那些短暂的电流低于100的情况，我们需要通过检查连续记录来确定设备是否仍然处于工作状态。这可以通过窗口函数和自连接来实现。

步骤 4: 统计时长
最后，我们需要统计每个状态的总持续时间。

示例 SQL 查询
这里给出一个示例查询，假设你的表名为 DeviceCurrents。

Step 1: 创建临时表或CTE (Common Table Expression) 用于状态计算

WITH DeviceStatus AS (
    SELECT 
        DeviceID,
        Timestamp,
        Current,
        CASE 
            WHEN Current = 0 THEN 'Off'
            WHEN Current > 100 THEN 'Working'
            ELSE 'Standby'
        END AS InitialState
    FROM DeviceCurrents
)

Step 2: 连续状态处理

, ContinuousStatus AS (
    SELECT 
        d1.DeviceID,
        d1.Timestamp,
        d1.Current,
        d1.InitialState,
        -- 使用LAG()获取上一条记录的状态
        d2.InitialState AS PrevState,
        -- 如果当前状态是Working并且上一条记录也是Working或者上一条记录是Standby但是两者的Timestamp差不超过半小时，则认为当前状态是Working
        CASE 
            WHEN d1.InitialState = 'Working' AND (d2.InitialState = 'Working' OR (d2.InitialState = 'Standby' AND d1.Timestamp - d2.Timestamp <= INTERVAL '00:30:00')) THEN 'Working'
            ELSE d1.InitialState
        END AS FinalState
    FROM DeviceStatus d1
    LEFT JOIN DeviceStatus d2 ON d1.DeviceID = d2.DeviceID AND d1.Timestamp = (SELECT MIN(Timestamp) FROM DeviceStatus WHERE DeviceID = d1.DeviceID AND Timestamp < d1.Timestamp)
)

Step 3: 计算状态时长

SELECT 
    DeviceID,
    FinalState,
    SUM(DATEDIFF(minute, LAG(Timestamp) OVER (PARTITION BY DeviceID, FinalState ORDER BY Timestamp), Timestamp)) / 60.0 AS DurationHours
FROM ContinuousStatus
GROUP BY DeviceID, FinalState
ORDER BY DeviceID, FinalState;

解释
DeviceStatus: 这个CTE计算了每个记录的初始状态。
ContinuousStatus: 这个CTE考虑了连续记录的状态，并修正了短暂的异常情况。
Duration Calculation: 最后一个查询统计了每个状态的总时长。
请注意，以上查询需要根据您的具体数据库版本和日期时间类型进行适当的调整。此外，由于SQL Server 不直接支持 INTERVAL 类型，我们使用 DATEDIFF 函数来计算时间差。