将Python工单状态配对逻辑转换为Snowflake SQL的技术求助

原Python实现逻辑 #

以下是用于配对工单状态的Python代码：

req_id_mem = ""
req_workflow_mem = ""

collect_state_main = []
collect_state_temp = []

for req_id, req_datetime, req_workflow in zip(df["TICKET_ID"], df["DATETIMESTANDARD"], df["STATUS"]):
    if req_id_mem == "" or req_id_mem != req_id:
        req_id_mem = req_id
        req_workflow_mem = ""
        collect_state_temp = []
    
    if req_workflow_mem == "" and req_workflow == "Open" and req_id_mem == req_id:
        req_workflow_mem = req_workflow
        collect_state_temp.append(req_id)
        collect_state_temp.append(req_workflow)
        collect_state_temp.append(req_datetime)
        
    if req_workflow_mem == "Open" and req_workflow == "Closed" and req_id_mem == req_id:
        req_workflow_mem = req_workflow
        collect_state_temp.append(req_workflow)
        collect_state_temp.append(req_datetime)
        collect_state_main.append(collect_state_temp)
        collect_state_temp = []

示例数据 #

预期配对规则 #

• 识别每个TICKET_ID的首个未配对Open状态，匹配其最近的后续Closed状态
• 循环处理工单的多次Open-Closed配对（仅保留首次Open与对应首个Closed的组合，忽略重复的同状态记录）

现有SQL的问题 #

之前尝试的SQL仅能获取首次Open-Closed配对，无法处理工单后续的多次状态配对，因为它只聚合了每个工单的最早Open时间，没有区分多轮状态周期。

解决方案：Snowflake SQL查询 #

WITH ordered_events AS (
    -- 按工单ID和时间排序，过滤出有效状态并标记行号
    SELECT
        TICKET_ID,
        DATETIMESTANDARD,
        STATUS,
        ROW_NUMBER() OVER (PARTITION BY TICKET_ID ORDER BY DATETIMESTANDARD) AS row_num
    FROM DB.TABLE
    WHERE STATUS IN ('Open', 'Closed')
),
open_events AS (
    -- 筛选出所有需要配对的Open事件：即当前是Open，且上一个状态不是Open（避免重复Open）
    SELECT
        TICKET_ID,
        DATETIMESTANDARD AS open_time,
        row_num AS open_row
    FROM ordered_events oe
    WHERE oe.STATUS = 'Open'
    AND (
        row_num = 1 
        OR (SELECT STATUS FROM ordered_events WHERE TICKET_ID = oe.TICKET_ID AND row_num = oe.row_num - 1) != 'Open'
    )
),
closed_events AS (
    -- 筛选出所有有效Closed事件：当前是Closed，且上一个状态不是Closed（避免重复Closed）
    SELECT
        TICKET_ID,
        DATETIMESTANDARD AS close_time,
        row_num AS close_row
    FROM ordered_events oe
    WHERE oe.STATUS = 'Closed'
    AND (
        row_num = 1 
        OR (SELECT STATUS FROM ordered_events WHERE TICKET_ID = oe.TICKET_ID AND row_num = oe.row_num - 1) != 'Closed'
    )
)
-- 配对每个Open事件与后续最早的Closed事件
SELECT
    oe.TICKET_ID,
    oe.open_time,
    MIN(ce.close_time) AS close_time
FROM open_events oe
LEFT JOIN closed_events ce 
    ON oe.TICKET_ID = ce.TICKET_ID 
    AND ce.close_row > oe.open_row
GROUP BY oe.TICKET_ID, oe.open_time
ORDER BY oe.TICKET_ID, oe.open_time;

逻辑说明 #

1. ordered_events：先按工单ID分组、时间排序，给每个事件标记行号，只保留Open和Closed状态。
2. open_events：过滤出真正的"开启"事件——即当前是Open，且上一个状态不是Open（跳过重复的Open记录，只取每轮的第一个Open）。
3. closed_events：同理过滤出真正的"关闭"事件——当前是Closed，且上一个状态不是Closed（跳过重复的Closed记录）。
4. 最后将Open事件与后续的Closed事件关联，取每个Open对应的最早Closed时间，实现多轮配对。

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