Databricks SQL LAG()处理重复时间戳的工单前序记录匹配问题

问题核心约束 #

• 源表字段：PK（记录唯一主键）、ticket_number（工单编号）、timestamp（状态变更时间）、previous_state（变更前状态）、current_state（变更后状态）
• 现存问题：同一工单下存在多条timestamp完全相同的变更记录，直接按时间戳排序调用LAG()窗口函数，会匹配到错误的前序记录
• 可用匹配依据：状态流转存在严格承接关系，即任意一条变更记录的previous_state，必然等于逻辑上前一条记录的current_state

实现逻辑 #

1. 先定位每个工单的流转起点：找不到任何同工单记录的current_state与该记录previous_state相等的，就是初始状态记录
2. 用递归CTE从起点开始顺着状态承接关系逐层遍历所有后续记录，为每条记录生成不依赖时间戳的逻辑顺序号
3. 基于逻辑顺序号匹配每条记录的直接前序记录，取前序的主键、时间戳，即可直接计算两个状态节点的时间差，得到对应阶段的停留时长

可复用SQL实现（支持PostgreSQL/MySQL 8.0/Spark SQL等所有兼容递归CTE的查询引擎） #

WITH RECURSIVE
-- 标记所有工单的流转起始节点
start_nodes AS (
    SELECT 
        curr.*,
        1 AS logic_sort
    FROM ticket_status_log curr
    LEFT JOIN ticket_status_log prev
        ON curr.ticket_number = prev.ticket_number
        AND curr.previous_state = prev.current_state
    WHERE prev.PK IS NULL
),
-- 递归遍历全量状态链路，生成正确逻辑顺序
state_chains AS (
    SELECT * FROM start_nodes
    UNION ALL
    SELECT 
        curr.*,
        prev.logic_sort + 1 AS logic_sort
    FROM ticket_status_log curr
    INNER JOIN state_chains prev
        ON curr.ticket_number = prev.ticket_number
        AND curr.previous_state = prev.current_state
    WHERE prev.logic_sort < 200 -- 限制递归深度，避免脏数据导致死循环，可按业务实际调整
)
-- 匹配前序记录，计算停留时长
SELECT
    curr.PK,
    curr.ticket_number,
    curr.timestamp,
    curr.previous_state,
    curr.current_state,
    prev.PK AS prev_record_pk,
    prev.timestamp AS prev_timestamp,
    -- 停留时长单位可按需调整，示例为秒级差值
    TIMESTAMPDIFF(SECOND, prev.timestamp, curr.timestamp) AS stay_duration_sec
FROM state_chains curr
LEFT JOIN state_chains prev
    ON curr.ticket_number = prev.ticket_number
    AND curr.logic_sort = prev.logic_sort + 1
ORDER BY curr.ticket_number, curr.logic_sort;

注意事项 #

• 这套排序逻辑完全不依赖时间戳，只要previous_state和current_state的承接关系准确，同时间戳下存在任意多条连续变更都能正确排序，不会出现匹配错位
• 遇到状态回退场景（比如审批驳回、工单关闭后重开）不需要额外调整逻辑，只要回退记录的previous_state标注为回退前的真实状态，递归链路可自动识别
• 正式跑数前建议先做脏数据校验：统计每个工单递归输出的记录总数，和原表同工单的记录数做比对，数量不一致的说明存在previous_state填写错误的脏数据，先清洗再计算即可避免链路断裂

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