计算平均工单响应时间

Question

我想要得到一个部门的平均响应时间。

下面是我的数据库模式。门票：

| ID   | Subject   | Department  | AdminId  | ClientId  | created_at          |
| 1    | Test1     | 1           | 0        | 1         | 2015-01-01 10:10:10 |
| 2    | Test2     | 1           | 0        | 2         | 2015-01-01 20:20:20 |

TicketReplies:
  --AuthorType:
    ----1 => Admin
    ----2 => Client



| ID   | TicketId   | Body        | AuthorId  | AuthorType | created_at          |
| 1    | 1          | Test Body?  | 1         | 2          | 2015-01-01 10:10:10 |
| 2    | 1          | Yes!        | 1         | 1          | 2015-01-01 10:48:16 |
| 3    | 2          | Are you Ok? | 2         | 2          | 2015-01-01 20:20:20 |
| 4    | 2          | Yes!        | 1         | 1          | 2015-01-02 08:15:18 |

Answer 1

平均响应时间是最大时间减去最小时间除以响应总数减1。

要获取以秒为单位的值，请执行以下操作：

select t.department,
       ( (to_seconds(max(created_at)) - to_seconds(min(created_at)) ) /
         nullif(count(*) - 1, 0)
       )
from ticketreplies tr join
     tickets t
     on tr.ticketid = t.id
group by t.department;

这在MySQL 5.5+中有效。在早期版本中，您可以通过转换为Unix时间戳来执行相同的操作。

Answer 2

SELECT Department, AVG(response_time) as avg_response  -- in seconds
FROM 
    (
    SELECT T.Department, 
           TR.TicketId,
           TIMESTAMPDIFF(SECOND, MIN(TR.created_at), MAX(TR.created_at)) as response_time
    FROM Tickets T
    JOIN TicketReplies TR
      ON T.id = TR.TicketId   
    GROUP BY T.Department, TR.TicketId
    ) as T
GROUP BY Department

输出

| Department | avg_response |
|------------|--------------|
|          1 |        22592 |