SQL中如何依据状态列过滤行并计算工单排除监控状态的耗时

实现思路 #

要扣除工单处于监控状态的时长，核心是给每一次进入监控的状态记录匹配到对应的退出监控的时间，计算每段监控的持续时长即可：

• 用窗口函数对同一个工单的状态变更记录按时间戳排序
• 用LEAD()/LAG()函数获取相邻的状态变更时间和状态值，完成监控时间段的配对
• 计算每段监控的时长，汇总后就是总需调整的分钟数

可用SQL代码 #

以下是兼容MySQL 8.0+、PostgreSQL、Hive、Spark SQL等支持窗口函数的数据库的实现：

1. 计算每个工单总需调整的分钟数 #

WITH status_ranked AS (
    SELECT
        ticket_id,
        `timestamp` AS curr_ts,
        status AS curr_status,
        -- 取同工单下一条状态变更的时间
        LEAD(`timestamp`) OVER (PARTITION BY ticket_id ORDER BY `timestamp`) AS next_ts
    FROM ticket_status_change -- 替换成你实际的表名
)
SELECT
    ticket_id,
    SUM(
        CASE
            WHEN curr_status = 'Changed to: Monitoring'
            -- 时间差换算为分钟，不同数据库可以替换对应时间差函数
            THEN TIMESTAMPDIFF(MINUTE, curr_ts, next_ts)
            ELSE 0
        END
    ) AS total_adjustment_mins
FROM status_ranked
GROUP BY ticket_id;

时间差函数适配说明：PostgreSQL可替换为EXTRACT(EPOCH FROM (next_ts - curr_ts))/60，Oracle可替换为(next_ts - curr_ts)*24*60

2. 生成原表的Adjustment needed列 #

如果需要和样例一样给每行记录标注调整时长，直接用LAG()函数匹配上一条监控状态即可：

WITH status_ranked AS (
    SELECT
        *,
        LAG(`timestamp`) OVER (PARTITION BY ticket_id ORDER BY `timestamp`) AS prev_ts,
        LAG(status) OVER (PARTITION BY ticket_id ORDER BY `timestamp`) AS prev_status
    FROM ticket_status_change -- 替换成你实际的表名
)
SELECT
    `Entry ID`,
    `Time Stamp`,
    `Status`,
    CASE
        WHEN prev_status = 'Changed to: Monitoring' AND status != 'Changed to: Monitoring'
        THEN TIMESTAMPDIFF(MINUTE, prev_ts, `Time Stamp`)
        ELSE NULL
    END AS `Adjustment needed`
FROM status_ranked
ORDER BY `Time Stamp` DESC;

样例验证 #

你提供的样例数据中有两段监控时长：

1. 2021/8/16 20:57 进入监控 → 2021/8/20 13:40 退出监控，时长约5323~5324分钟
2. 2021/8/20 13:40 进入监控 → 2021/8/20 13:46 退出监控，时长约6~6.3分钟
   两段加总就是约5330分钟，和你给出的总调整时长一致，差异来自时间差的取整规则，可根据业务需求调整是否保留小数、是否向上取整。

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