Average Response times using PHP/MySQL

I have 2 tables in a MySQL Database. one called tickets and the other is ticket_updates

tickets has the following columns:

sequence

ticketnumber

datetime

and ticket_updates

sequence

ticket_seq

datetime

starttime

endtime

the ticket_seq column in the ticket_updates table links with the ticketnumber column in the tickets table. There may be multiple rows in ticket_updates linking to one row in the tickets table.

I want to show an average response time for how long it takes for the tickets to be replied to. the datetime column in both tables is a full timestamp of when the row was added/inserted (Ymd H:i:s)

how can i show average response times for, say the last week?

---

From your chat wit Andy Jones I understand that you define the response time of a ticket as the time elapsed between tickets.datetime and the first ticket_updates.datetime for that ticket. In that case, the following query returns the average response time in seconds for tickets added in the last 7 days.

SELECT avg(response_seconds)
  FROM (
     SELECT time_to_sec(timediff(min(u.datetime), t.datetime)) AS response_seconds
       FROM tickets t
       JOIN ticket_updates u
         ON t.ticketnumber = u.ticket_seq
      WHERE t.datetime > now() - INTERVAL 7 day
      GROUP BY t.ticketnumber ) AS r

---

I've assembled a full example. I assumed some things about your data - if the columns have wrong types, change appropriately. I'm also going to return the average update time in seconds.

CREATE TABLE tickets (
  sequence int(10) not null auto_increment primary key,
  ticket_number int(10) not null default 0,
  date_added timestamp,
  index ticket_number_index (ticket_number)
  );

INSERT INTO tickets (ticket_number, date_added) VALUES
  (1, '2013-12-01 01:00:00'),
  (2, '2013-12-15 02:00:00'),
  (3, '2013-12-10 03:00:00'),
  (4, '2013-12-13 04:00:00'),
  (5, '2013-12-17 05:00:00');


CREATE TABLE ticket_updates (
  sequence int(10) not null auto_increment primary key,
  ticket_number int(10) not null default 0,
  date_added timestamp,
  starttime datetime,
  endtime datetime,
  index ticket_number_index (ticket_number)
  );

INSERT INTO ticket_updates (ticket_number, starttime, endtime, date_added) VALUES
  (1, '2013-12-01 01:01:00', '2013-12-01 01:02:00', '2013-12-01 01:02:00'),
  (1, '2013-12-01 01:01:00', '2013-12-01 01:02:00', '2013-12-01 01:02:00'),
  (2, '2013-12-15 02:01:00', '2013-12-15 02:02:00', '2013-12-15 02:02:00'),
  (2, '2013-12-15 02:01:00', '2013-12-15 02:02:00', '2013-12-15 02:02:00'),
  (2, '2013-12-15 02:01:00', '2013-12-15 02:02:00', '2013-12-15 02:02:00'),
  (2, '2013-12-15 02:02:00', '2013-12-15 02:02:50', '2013-12-15 02:02:50'),
  (4, '2013-12-13 04:01:00', '2013-12-13 04:02:00', '2013-12-13 04:02:00'),
  (4, '2013-12-13 04:01:00', '2013-12-13 04:05:30', '2013-12-13 04:05:30'),
  (5, '2013-12-17 05:01:00', '2013-12-17 05:03:00', '2013-12-17 05:03:00');

You can then run this query...

SELECT SUM(TO_SECONDS(ticket_updates.date_added) - TO_SECONDS(tickets.date_added)) / count(*) as update_time
FROM tickets, ticket_updates
WHERE tickets.ticket_number = ticket_updates.ticket_number AND
  ticket_updates.date_added > now() - INTERVAL 1 WEEK

Not sure if this is quite exactly what you're looking for - but this (and the fiddle) might help move you closer to the answer.

See Fiddle: http://sqlfiddle.com/#!2/4c0acc/1

---

The average is sum of all values (in your case you have to convert datetime to UNIX timestamp) and then divide this sum to the total count. The conversion can be done via MySQL by issuing the command, for example

SELECT unix_timestamp(str_to_date('30/05/2011','%d/%m/%Y'));

or in PHP when displaying the results. It depends on the logic and structure of your project.

链接地址: http://www.djcxy.com/p/17706.html

上一篇: 遍历具有危险指针的列表

下一篇: 使用PHP / MySQL的平均响应时间