基于字段值多日志行统计设备工作时长的技术问询

需求与日志示例 #

核心需求 #

• 忽略重复指令，第一个START触发计时，直到下一个PAUSE或FINISH停止
• 每个Serial可包含多个START-FINISH周期，需累加各周期时长
• PAUSE会暂停计时，直到下一个START恢复

日志示例 #

当前实现代码 #

SELECT
    *,
    CASE 
        WHEN Lines.PreviousCmd = 'START'  and Lines.Command = 'FINISH' THEN DATEDIFF(second, PreviousTS, TS)
        WHEN Lines.PreviousCmd = 'FINISH'  and Lines.Command = 'START' THEN 0
        WHEN Lines.PreviousCmd = 'START'  and Lines.Command = 'PAUSE' THEN DATEDIFF(second, PreviousTS, TS)
        WHEN Lines.PreviousCmd = 'PAUSE'  and Lines.Command = 'START' THEN 0
        WHEN Lines.PreviousCmd = 'PAUSE'  and Lines.Command = 'FINISH' THEN 0
        WHEN Lines.PreviousCmd = 'FINISH'  and Lines.Command = 'PAUSE' THEN 0
    ELSE 999999999999999999999999999999999999 END AS TimeOn
FROM
(
    SELECT
        Serial,
        LAG(Serial, 1) OVER (ORDER BY Serial asc, TS asc ) as PreviousSerial,
        ROW_NUMBER() OVER(ORDER BY Serial asc, TS asc) AS RN,
        LAG(TS, 1) OVER (ORDER BY Serial asc, TS asc ) as PreviousTS,
        TS,
        LAG(Command,1) OVER (ORDER BY Serial asc, TS asc ) AS PreviousCmd,
        Command
    FROM #tAPPLOGTMP
) Lines 
where Command <> PreviousCmd
and PreviousSerial = Serial

优化建议 #

1. 修正窗口函数分区逻辑 #

原代码中LAG使用全局排序，容易出现跨Serial的错误关联。应按Serial分区，让窗口函数只在同一序列号内取上一条记录，同时省去PreviousSerial = Serial的判断：

-- 内层查询修改示例
SELECT
    Serial,
    LAG(TS, 1) OVER (PARTITION BY Serial ORDER BY TS asc ) as PreviousTS,
    TS,
    LAG(Command,1) OVER (PARTITION BY Serial ORDER BY TS asc ) AS PreviousCmd,
    Command
FROM #tAPPLOGTMP

2. 提前过滤重复指令 #

通过分组标记连续相同指令，只保留每组的第一条记录，减少后续计算的数据量：

WITH FilteredLogs AS (
    SELECT 
        Serial, TS, Command
    FROM (
        SELECT 
            Serial, TS, Command,
            -- 为连续相同指令生成分组ID
            SUM(CASE WHEN Command = LAG(Command) OVER(PARTITION BY Serial ORDER BY TS) THEN 0 ELSE 1 END) 
                OVER(PARTITION BY Serial ORDER BY TS) AS Grp
        FROM #tAPPLOGTMP
    ) t
    GROUP BY Serial, Grp, Command, TS
)

3. 简化CASE表达式与直接聚合总时长 #

合并无效分支逻辑，直接按Serial聚合得到总工作时长，一步到位满足需求：

WITH FilteredLogs AS (
    SELECT 
        Serial, TS, Command
    FROM (
        SELECT 
            Serial, TS, Command,
            SUM(CASE WHEN Command = LAG(Command) OVER(PARTITION BY Serial ORDER BY TS) THEN 0 ELSE 1 END) 
                OVER(PARTITION BY Serial ORDER BY TS) AS Grp
        FROM #tAPPLOGTMP
    ) t
    GROUP BY Serial, Grp, Command, TS
),
TimePairs AS (
    SELECT 
        Serial,
        TS,
        Command,
        LAG(TS) OVER(PARTITION BY Serial ORDER BY TS) AS PrevTS,
        LAG(Command) OVER(PARTITION BY Serial ORDER BY TS) AS PrevCmd
    FROM FilteredLogs
)
SELECT 
    Serial,
    SUM(CASE 
        WHEN PrevCmd = 'START' AND Command IN ('PAUSE', 'FINISH') 
        THEN DATEDIFF(second, PrevTS, TS) 
        ELSE 0 
    END) AS TotalWorkSeconds
FROM TimePairs
GROUP BY Serial;

4. 移除魔法值 #

原代码中ELSE返回的超大数值属于无意义魔法值，直接返回0即可，避免后续聚合或数据处理时出现异常。

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