MySQL计算buyout optr与reject/pass间垂直周转时间(TAT)实现方法

前提说明：默认你的表中存在唯一业务标识字段（如申请ID、工单ID，用于关联同一条业务的所有状态流转记录）、状态操作时间字段（如create_time/operate_time，记录对应状态生成的时间）。

1. 数据库SQL层直接计算 #

数据量不大的场景优先选择该方案，逻辑更简洁，无需额外开发业务代码：

• 按业务唯一标识分区，对同业务下的所有状态按操作时间正序排序
• 用窗口函数LEAD() 取当前起始状态（optr/buyout optr）的下一条状态记录和对应时间
• 过滤下一条状态为reject或pass的记录，用下一条状态的时间减去起始状态的时间，得到的差值即为对应TAT

示例SQL伪代码（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、Hive等）：

SELECT 
    业务唯一标识,
    operate_time AS start_operate_time,
    next_status,
    next_operate_time,
    -- 时间单位可按需替换为MINUTE/HOUR/DAY
    TIMESTAMPDIFF(SECOND, operate_time, next_operate_time) AS tat_second
FROM (
    SELECT 
        业务唯一标识,
        status,
        operate_time,
        LEAD(status,1) OVER(PARTITION BY 业务唯一标识 ORDER BY operate_time ASC) AS next_status,
        LEAD(operate_time,1) OVER(PARTITION BY 业务唯一标识 ORDER BY operate_time ASC) AS next_operate_time
    FROM 你的表名
) t
-- 如果需要包含buyout optr作为起始状态，调整为WHERE status IN ('optr', 'buyout optr')即可
WHERE status = 'optr' 
AND next_status IN ('reject','pass')

如果使用不支持窗口函数的低版本数据库，可改用自关联实现：

SELECT 
    a.业务唯一标识,
    a.operate_time AS start_operate_time,
    b.status AS next_status,
    b.operate_time AS next_operate_time,
    TIMESTAMPDIFF(SECOND, a.operate_time, b.operate_time) AS tat_second
FROM 你的表名 a
LEFT JOIN 你的表名 b ON a.业务唯一标识 = b.业务唯一标识 AND b.operate_time > a.operate_time
WHERE a.status = 'optr'
AND b.status IN ('reject','pass')
-- 存在起始状态和目标状态之间有其他状态的场景，加以下过滤取最近的目标状态
AND NOT EXISTS (
    SELECT 1 FROM 你的表名 c 
    WHERE c.业务唯一标识 = a.业务唯一标识 
    AND c.operate_time BETWEEN a.operate_time AND b.operate_time
    AND c.id != a.id AND c.id != b.id
)

2. 业务代码层计算 #

适合数据量极大、或者需要处理复杂自定义流转规则的场景：

• 批量拉取需要计算的全量状态记录，按业务唯一标识做分组
• 每个分组内的记录按操作时间升序排序
• 遍历排序后的状态列表，定位到起始状态（optr/buyout optr）的位置，往后找第一个匹配reject/pass的状态记录
• 计算两个状态的时间差即为对应TAT，无匹配后续状态可标记为流转未完成

注意事项 #

• 异常流转兼容：如果同一条业务存在多个相同起始状态，需要根据业务规则选择取第一个还是最后一个起始状态计算
• 时区统一：确保所有状态的操作时间字段时区一致，避免时间差计算错误
• 工作日TAT适配：如果需要计算工作时间内的TAT，要额外扣除非工作时段、节假日区间

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