播面

WITH user_abort_events AS (
    -- 1. 步骤一：找出“用户开门后未取走、柜门重新锁定”的异常事件
    -- 使用窗口函数 LEAD 获取紧邻的下一个锁定事件，并确保货物状态为 'NOT_TAKEN'
    SELECT 
        express_id,
        event_time AS open_time,
        next_event_time AS lock_time
    FROM (
        SELECT 
            express_id,
            event_type,
            event_time,
            LEAD(event_type) OVER (PARTITION BY express_id ORDER BY event_time) AS next_event_type,
            LEAD(lock_status) OVER (PARTITION BY express_id ORDER BY event_time) AS next_lock_status,
            LEAD(cargo_status) OVER (PARTITION BY express_id ORDER BY event_time) AS next_cargo_status,
            LEAD(event_time) OVER (PARTITION BY express_id ORDER BY event_time) AS next_event_time
        FROM cabinet_event_log
    ) t
    WHERE t.event_type = 'USER_OPEN' 
      AND t.next_event_type = 'AUTO_CLOSE'
      AND t.next_lock_status = 'LOCKED'
      AND t.next_cargo_status = 'NOT_TAKEN'
),

courier_retakes AS (
    -- 2. 步骤二：筛选出快递员执行的“异常取出”记录
    SELECT 
        express_id,
        action_time AS courier_take_time
    FROM delivery_record
    WHERE action_type = 'RE_TAKE' 
      AND operator_role = 'COURIER'
)

-- 3. 步骤三：双表关联，限制快递员取出时间在用户锁定格口后的 2 小时内
SELECT 
    a.express_id,
    a.open_time AS user_open_time,
    a.lock_time AS cabinet_relock_time,
    c.courier_take_time,
    -- 计算快递员响应耗时（分钟）
    ROUND((CAST(c.courier_take_time AS LONG) - CAST(a.lock_time AS LONG)) / 60, 2) AS courier_response_minutes
FROM user_abort_events a
JOIN courier_retakes c 
  ON a.express_id = c.express_id
WHERE c.courier_take_time > a.lock_time 
  -- 限制在 2 小时（7200秒）内
  AND CAST(c.courier_take_time AS LONG) - CAST(a.lock_time AS LONG) <= 7200;