如何用Oracle SQL计算打印机故障修复的平均耗时

针对你的需求，我们需要先排除被标记为TRASHED的打印机，再计算每台合格打印机从故障发现到修复的耗时，最后求这些耗时的平均值。以下是两种适用于Oracle数据库的实现方案：

方案一：自连接关联故障与修复记录 #

通过自连接printer_history表，匹配同一打印机的ISSUE_FOUND和FIXED记录，同时过滤掉存在TRASHED操作的设备：

SELECT AVG(fixed.action_date - found.action_date) AS avg_repair_time
FROM printer_history found
JOIN printer_history fixed 
  ON found.printer_name = fixed.printer_name
  AND found.action_name = 'ISSUE_FOUND'
  AND fixed.action_name = 'FIXED'
WHERE NOT EXISTS (
  SELECT 1 
  FROM printer_history trash
  WHERE trash.printer_name = found.printer_name
    AND trash.action_name = 'TRASHED'
);

方案二：条件聚合转换记录 #

利用条件聚合将同一打印机的不同操作日期转为列，再计算时间差并求平均：

SELECT AVG(fixed_date - found_date) AS avg_repair_time
FROM (
  SELECT printer_name,
         MAX(CASE WHEN action_name = 'ISSUE_FOUND' THEN action_date END) AS found_date,
         MAX(CASE WHEN action_name = 'FIXED' THEN action_date END) AS fixed_date
  FROM printer_history
  GROUP BY printer_name
  HAVING NOT EXISTS (
    SELECT 1 
    FROM printer_history trash
    WHERE trash.printer_name = printer_history.printer_name
      AND trash.action_name = 'TRASHED'
  )
) t;

关键说明 #

• 两种方案都会自动排除如Xerox3这类存在TRASHED记录的打印机
• Oracle中TIMESTAMP类型相减会得到INTERVAL类型，AVG函数可直接对该类型计算平均值
• 由于你明确每台合格打印机仅有一组ISSUE_FOUND和FIXED记录，因此无需担心多匹配问题

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