mysql - 计算平均工单响应时间

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

平均响应时间是最长时间减去最短时间除以响应总数减一。

以秒为单位获取值:

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 时间戳来做同样的事情。