#mysql #database #time #average
#mysql #База данных #время #среднее
Вопрос:
Возможно ли рассчитать среднее время ответа на сообщение только со следующими столбцами:
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
Комментарии:
1. Сложно выбрать, какое принять, поэтому выбрал первое — если бы только можно было выбрать 2!
2. Вы всегда можете проголосовать за второе, если это вам тоже помогло …
Ответ №1:
Излишне говорить, что я ненавижу работать с 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;
Это возвращает среднее время (в секундах), которое потребовалось администратору для ответа на сообщение (созданное кем угодно, не обязательно клиентом, но это можно легко изменить).
Комментарии:
1. Что, если бы администратор написал сообщение от имени клиента, где, скажем, в базе данных у клиента и администратора был бы номер, могли бы вы проигнорировать номер клиента в этом случае?
2. @MrJ : Вам просто нужно изменить условие
admin <> 0
на0 NOT IN (admin, client)
3. Хммм… Теперь я получаю null в качестве среднего значения
4. @MrJ: Мой плохой, на самом деле условие должно быть
admin <> 0 AND client = 0
, я отредактировал свой пост.
Ответ №2:
Ваш вопрос хорошо сформулирован, но оставляет место для интерпретации. Это одна из интерпретаций:
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
в обоих случаях). Просто обозначающий ярлык.
Комментарии:
1. Не уверен, к чему относится
GROUP BY 1
часть выше. Как насчет каждого сообщения? Скажем, клиент 1 решает снова ответить на первое сообщение, а администратор отвечает чуть позже?2. Я вижу, вы сделали несколько модификаций — попробуем это!
3. Должны ли мы использовать
UNIX_TIMESTAMP
где-нибудь для преобразования во временную метку? Поскольку я получаю число с большим количеством знаков после запятой 04. @MrJ: Я исправил свой ответ информацией о
GROUP BY 1
.5. @Erwin Brandstetter : Это не сработает из-за способа MySQL обрабатывать операции с датами. MySQL просто тупо преобразует даты в целые числа (2011-11-08 12:00:00 станет 20111108120000) и вычтет эти целые числа.