如何基于变更历史表识别工单关闭后至重开间的字段变更？

数据表说明 #

工单变更历史表（History Table） #

+---------+--------+---------+-----------+-----------+
| WORK_ID |  TIME  |  FIELD  | OLD_VALUE | NEW_VALUE |
+---------+--------+---------+-----------+-----------+
| A2      | 09.02  | SALES   | 150       | 250       |
| A1      | 09.00  | STATUS  | CLOSED    | OPEN      |
| A1      | 08.55  | OWNER   | LISA      | DEBBY     |
| A2      | 08.54  | STATUS  | CLOSED    | OPEN      |
| A2      | 08.50  | STATUS  | OPEN      | CLOSED    |
| A1      | 08.45  | SALES   | 300       | 500       |
| A1      | 08.45  | STATUS  | OPEN      | CLOSED    |
| A2      | 08.40  | OWNER   | ROB       | ANDY      |
+---------+--------+---------+-----------+-----------+

工单全量数据表（Work Order Table） #

+---------+---------+--------+-------+
| WORK_ID |  STATUS | OWNER  | SALES | 
+---------+---------+--------+-------+
| A1      | OPEN    | DEBBY  | 500   |
| A2      | OPEN    | ANDY   | 250   |
+---------+---------+--------+-------+

需求 #

识别工单状态变为CLOSED后、变为OPEN前是否有任何非STATUS字段发生变更，期望输出如下：

+---------+---------+--------+-------+-------------------+
| WORK_ID |  STATUS | OWNER  | SALES | CHANGED_ON_CLOSED |
+---------+---------+--------+-------+-------------------+
| A1      | OPEN    | DEBBY  | 500   | TRUE              |
| A2      | OPEN    | ANDY   | 250   | FALSE             |
+---------+---------+--------+-------+-------------------+

实现SQL #

WITH status_transitions AS (
    -- 提取所有状态切换记录，标记关闭/打开事件
    SELECT 
        WORK_ID,
        TIME,
        CASE WHEN OLD_VALUE = 'OPEN' AND NEW_VALUE = 'CLOSED' THEN 1 ELSE 0 END AS closed_event,
        CASE WHEN OLD_VALUE = 'CLOSED' AND NEW_VALUE = 'OPEN' THEN 1 ELSE 0 END AS open_event
    FROM History
    WHERE FIELD = 'STATUS'
),
work_order_windows AS (
    -- 获取每个工单最新的关闭-打开时间窗口
    SELECT 
        st1.WORK_ID,
        st1.TIME AS closed_time,
        MIN(st2.TIME) AS open_time
    FROM status_transitions st1
    LEFT JOIN status_transitions st2 
        ON st1.WORK_ID = st2.WORK_ID 
        AND st2.TIME > st1.TIME 
        AND st2.open_event = 1
    WHERE st1.closed_event = 1
    GROUP BY st1.WORK_ID, st1.TIME
    QUALIFY ROW_NUMBER() OVER (PARTITION BY WORK_ID ORDER BY st1.TIME DESC) = 1
),
change_check AS (
    -- 判断窗口内是否存在非状态字段变更
    SELECT 
        wow.WORK_ID,
        CASE WHEN COUNT(h.WORK_ID) > 0 THEN TRUE ELSE FALSE END AS CHANGED_ON_CLOSED
    FROM work_order_windows wow
    LEFT JOIN History h 
        ON wow.WORK_ID = h.WORK_ID 
        AND h.TIME > wow.closed_time 
        AND h.TIME < wow.open_time 
        AND h.FIELD != 'STATUS'
    GROUP BY wow.WORK_ID
)
-- 关联工单全量表输出最终结果
SELECT 
    wo.WORK_ID,
    wo.STATUS,
    wo.OWNER,
    wo.SALES,
    COALESCE(cc.CHANGED_ON_CLOSED, FALSE) AS CHANGED_ON_CLOSED
FROM Work_Order wo
LEFT JOIN change_check cc 
    ON wo.WORK_ID = cc.WORK_ID;

逻辑说明 #

1. 提取状态事件：从历史表筛选STATUS字段的变更记录，标记OPEN→CLOSED（关闭事件）和CLOSED→OPEN（打开事件）。
2. 锁定时间窗口：为每个工单匹配最新一次关闭事件对应的下一次打开事件，确定需要检查的时间区间。
3. 检查字段变更：统计该时间区间内是否存在非STATUS字段的变更，生成布尔判断标记。
4. 关联输出：将判断结果与工单全量表关联，输出符合要求的结果格式。

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