#mysql #sql
#mysql #sql
Вопрос:
У меня есть таблица с именем ‘game_logs’ со следующей структурой:
id: integer
user_id: integer
game_id: integer
game_event_type_id: integer
timestamp: datetime
Я должен вычислить среднюю разницу во времени между событиями типа «3» (game_event_type_id = 3) для записей с одинаковыми идентификаторами пользователя и идентификатора игры.
id, user_id, game_id, game_event_type_id, timestamp
'1', '45', '92', '3', '2014-06-02 15:36:04'
'2', '45', '92', '3', '2014-06-02 15:36:17'
'3', '45', '92', '3', '2014-06-02 15:36:25'
'4', '20', '91', '3', '2014-06-02 15:37:15'
Я пробовал разные вещи, но группировка по game_id и user_id и одновременное вычисление среднего значения кажется сложной задачей. Я остаюсь присоединенным к той же таблице.
Кто-нибудь может мне помочь? Спасибо!
Ответ №1:
Вы можете получить среднюю разницу в секундах, выполнив:
select user_id, game_id,
(case when count(*) > 1
then timestampdiff(second, min(timestamp), max(timestamp)) / (count(*) - 1)
end) as AvgDiffSeconds
from game_logs gl
where game_event_type_id = 3
group by user_id, game_id;
Ключевая идея заключается в том, что средняя разница — это максимум минус минимум, деленный на единицу меньше, чем количество записей.
Комментарии:
1. Ваша идея имеет большой смысл. Хотя я получаю сообщение об ошибке в предложении SQL. Я изменил «end» на «end case», но все еще не работает.
Ответ №2:
Вы не можете сделать это с помощью одного запроса. Либо используйте хранимую процедуру, либо сделайте это на стороне вашего приложения (там это должно быть довольно просто)