MS SQL按状态聚合统计时间差及最大值的查询方法问询

首先，明确你的需求：针对每个UnitID，统计其处于Status A和Status B的总时长（格式为分:秒），同时获取该UnitID对应的Value最大值。

原始数据表（示例） #

======================================================
UnitID  Status  DateTime            Value
======================================================
101     A       01/12/2017 00:02:10 10
101     A       01/12/2017 00:02:40 25
101     A       01/12/2017 00:03:20 18
101     B       01/12/2017 00:03:55 30
101     B       01/12/2017 00:04:05 10
101     B       01/12/2017 00:04:30 20
101     B       01/12/2017 00:04:50 10
101     A       01/12/2017 00:05:00 28
101     A       01/12/2017 00:05:50 18
101     A       01/12/2017 00:06:20 18
102     A       01/12/2017 00:02:10 10
102     A       01/12/2017 00:02:40 25
102     A       01/12/2017 00:03:20 18
102     B       01/12/2017 00:03:55 30
102     B       01/12/2017 00:04:05 10
102     B       01/12/2017 00:04:30 20
102     B       01/12/2017 00:04:50 10
102     A       01/12/2017 00:05:00 28
102     A       01/12/2017 00:05:50 18
102     A       01/12/2017 00:06:20 18

期望输出 #

===========================================
UnitID  StatusA  StatusB  MaxValue
===========================================
101     02:30    00:55    30
102     02:30    00:55    30

MS SQL 查询语句 #

WITH StatusGroups AS (
    -- 第一步：给每个连续的同状态记录分组
    SELECT 
        UnitID,
        Status,
        DateTime,
        Value,
        -- 当当前状态与前一条不同时，生成新分组ID
        SUM(CASE WHEN PrevStatus = Status THEN 0 ELSE 1 END) OVER (PARTITION BY UnitID ORDER BY DateTime) AS GroupID
    FROM (
        SELECT 
            UnitID,
            Status,
            DateTime,
            Value,
            -- 获取前一条记录的状态
            LAG(Status) OVER (PARTITION BY UnitID ORDER BY DateTime) AS PrevStatus
        FROM YourTableName -- 替换为你的实际表名
    ) t
),
StatusDuration AS (
    -- 第二步：计算每个连续状态组的时长，汇总各状态总秒数
    SELECT 
        UnitID,
        Status,
        SUM(DATEDIFF(SECOND, MIN(DateTime), MAX(DateTime))) AS TotalSeconds
    FROM StatusGroups
    GROUP BY UnitID, Status, GroupID
),
TotalDuration AS (
    -- 第三步：汇总每个UnitID各状态的总时长（秒）
    SELECT 
        UnitID,
        Status,
        SUM(TotalSeconds) AS TotalSeconds
    FROM StatusDuration
    GROUP BY UnitID, Status
),
PivotedDuration AS (
    -- 第四步：将状态转成列，并格式化为mm:ss
    SELECT 
        UnitID,
        FORMAT(DATEADD(SECOND, ISNULL([A], 0), 0), 'mm:ss') AS StatusA,
        FORMAT(DATEADD(SECOND, ISNULL([B], 0), 0), 'mm:ss') AS StatusB
    FROM TotalDuration
    PIVOT (
        SUM(TotalSeconds)
        FOR Status IN ([A], [B])
    ) p
),
UnitMaxValue AS (
    -- 第五步：计算每个UnitID的Value最大值
    SELECT 
        UnitID,
        MAX(Value) AS MaxValue
    FROM YourTableName -- 替换为你的实际表名
    GROUP BY UnitID
)
-- 合并最终结果
SELECT 
    pd.UnitID,
    pd.StatusA,
    pd.StatusB,
    umv.MaxValue
FROM PivotedDuration pd
JOIN UnitMaxValue umv ON pd.UnitID = umv.UnitID
ORDER BY pd.UnitID;

代码解释 #

1. StatusGroups CTE：用LAG窗口函数获取每条记录的前一条状态，通过累计求和生成连续同状态的分组ID，确保同一UnitID下连续的相同状态被归为一组。
2. StatusDuration CTE：计算每个连续状态组的时长（秒数），因为同一状态可能有多个不连续的段，先算单段时长再汇总。
3. TotalDuration CTE：对每个UnitID的每个状态，汇总所有连续段的总秒数。
4. PivotedDuration CTE：用PIVOT将行转列，把Status A和B的总秒数转换成mm:ss格式，ISNULL处理某个状态不存在的情况（避免返回NULL）。
5. UnitMaxValue CTE：单独计算每个UnitID的Value最大值。
6. 最后通过UnitID关联时长表和最大值表，得到最终结果。

注意事项 #

• 请将代码中的YourTableName替换成你实际的数据表名称。
• 如果总时长超过60分钟，mm:ss格式会显示超过60的分钟数（比如120:30表示2小时30分），若需要hh:mm:ss格式，只需把FORMAT函数的格式改为hh:mm:ss即可。

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