Блог

Возможно ли рассчитать среднее время ответа на сообщение только со следующими столбцами:

 id | ref | client | admin | date | message
  

• id это уникальный номер сообщения
• ref это номер ссылки на сообщение, который не является уникальным (поиск ссылки, упорядочивание по дате покажет диалог)
• client является идентификатором клиента, если это клиентское сообщение, иначе 0, если не клиент
• admin является идентификатором администратора, если это сообщение администратора, иначе 0, если не клиент
• date настраивается с использованием datetime времени сообщения
• message будучи отправленным сообщением

Пример данных:

 1  | 1   | 1      | 0     | 2011-11-07 01:00:00 | ABC
2  | 1   | 1      | 0     | 2011-11-07 01:01:00 | DEF
3  | 1   | 0      | 1     | 2011-11-07 01:05:00 | abc
4  | 2   | 2      | 0     | 2011-11-07 01:10:00 | 123
5  | 3   | 1      | 0     | 2011-11-07 01:11:00 | abc
6  | 2   | 0      | 1     | 2011-11-07 01:20:00 | a
7  | 3   | 0      | 2     | 2011-11-07 02:11:00 | b
  

В идеале ищется средний период времени между сообщением клиента и сообщением администратора, хотя, если есть 2 клиентских сообщения от одного и того же клиента (т. Е. администратор не ответил на первое сообщение до того, как клиент добавил свое второе сообщение) с той же ссылкой.

Например, время для (1) = 5 минут, (2) = 10 минут, (3) = 60 минут … среднее = 25 минут (1500 секунд — рад работать с секундами)

Я даже не уверен, как начать работать над этим…. Я надеюсь, что кто-нибудь сможет помочь: S

Излишне говорить, что я ненавижу работать с MySQL :

 SELECT AVG(delay_answer)
FROM (SELECT MIN(delay_answer) AS delay_answer
      FROM (SELECT M1.ref, client, admin, TIMESTAMPDIFF(SECOND, date_original, date) AS delay_answer
            FROM messages M1
            INNER JOIN (SELECT ref, MIN(date) AS date_original
                        FROM messages
                        GROUP BY ref) M2
            ON M1.ref = M2.ref AND date > date_original
            WHERE admin <> 0 AND client = 0) x
      GROUP BY ref) y;
  

Это возвращает среднее время (в секундах), которое потребовалось администратору для ответа на сообщение (созданное кем угодно, не обязательно клиентом, но это можно легко изменить).

Ваш вопрос хорошо сформулирован, но оставляет место для интерпретации. Это одна из интерпретаций:

 SELECT avg(TIMESTAMPDIFF(SECOND, c.c_date, a.a_date) AS avg_time_to_response
FROM   (
    SELECT ref, min(date) AS c_date
    FROM   tbl
    WHERE  client > 0
    GROUP  BY 1
    ) c
JOIN  (
    SELECT ref, min(date) AS a_date
    FROM   tbl
    WHERE  admin > 0
    GROUP  BY 1
    ) a USING (ref)
WHERE a.a_date > c.c_date;
  

Показывает среднее время, которое проходит между первой публикацией клиента и первой публикацией администратора в потоке (ссылочный номер сообщения).

Неотвеченные сообщения игнорируются. Потоки, запущенные администраторами, перепутали бы результат с отрицательной длительностью, поэтому я исключил их. В результат включается только время первого ответа для каждого потока. Дополнительные публикации в том же потоке здесь игнорируются.

Прочитайте руководство здесь о TIMESTAMPDIFF() .
_{Спасибо @MrJ и @Vincent за указание на ошибку с вычитанием временных меток!}

Относительно GROUP BY 1

Я цитирую руководство здесь:

На столбцы, выбранные для вывода, можно ссылаться в предложениях ORDER BY и GROUP BY, используя имена столбцов, псевдонимы столбцов или позиции столбцов. Позиции столбцов являются целыми числами и начинаются с 1:

Курсив мой. Итак, я группирую по первому выбранному столбцу ( ref в обоих случаях). Просто обозначающий ярлык.