Oracle SQL实现Event_hist表按部门统计工单分段处理时长

核心逻辑说明 #

你当前的查询是取整个工单的最大日志ID，只能统计全流程总时长。要按部门拆分，核心是对同一个工单、同一个部门的所有日志分组，分别计算每组的最早进入时间和最晚离开时间的差值。

通用场景解法（工单单次进入同一部门统计） #

如果同一个工单不会重复进入同一个部门，直接用分组聚合即可，性能最优：

SELECT
    eh.case_id,
    eh.dep_id,
    ROUND((MAX(eh.log_ts) - MIN(eh.log_ts)) * 1440, 0) AS time_used_minutes
FROM event_hist eh
INNER JOIN Cases c ON eh.case_id = c.case_id
WHERE eh.creation_ts > DATE '2021-09-01'
  AND eh.case_type = 1 -- 若case_type存储在Cases表，需改为c.case_type
GROUP BY eh.case_id, eh.dep_id
ORDER BY eh.case_id, MIN(eh.log_ts);

逻辑说明：

• 按case_id+dep_id双字段分组，把每个工单在对应部门的所有日志归为同一组
• 每组MIN(log_ts)是工单进入该部门的首条日志时间，MAX(log_ts)是离开该部门的最后一条日志时间，差值转分钟即为该部门驻留时长

特殊场景解法（工单多次进入同一部门拆分统计） #

如果存在同一个工单多次流转回同一个部门，需要单独统计每次的驻留时长，用窗口函数做会话拆分：

WITH dep_change_mark AS (
    SELECT
        eh.case_id,
        eh.dep_id,
        eh.log_ts,
        -- 和上一条日志的部门对比，不一致则标记为新的驻留周期
        CASE WHEN LAG(eh.dep_id) OVER (PARTITION BY eh.case_id ORDER BY eh.log_ts) = eh.dep_id THEN 0 ELSE 1 END AS new_session_flag
    FROM event_hist eh
    INNER JOIN Cases c ON eh.case_id = c.case_id
    WHERE eh.creation_ts > DATE '2021-09-01'
      AND eh.case_type = 1
),
session_id_gen AS (
    SELECT
        *,
        -- 累加标记生成每个驻留周期的唯一ID
        SUM(new_session_flag) OVER (PARTITION BY case_id ORDER BY log_ts) AS session_id
    FROM dep_change_mark
)
SELECT
    case_id,
    dep_id,
    ROUND((MAX(log_ts) - MIN(log_ts)) * 1440, 0) AS time_used_minutes
FROM session_id_gen
GROUP BY case_id, dep_id, session_id
ORDER BY case_id, MIN(log_ts);

性能优化建议 #

百万级数据场景下，建议给event_hist表的case_id、dep_id、log_ts、creation_ts字段添加联合索引，可大幅降低查询耗时。

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