Блог

У меня есть таблица с именем ‘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 и одновременное вычисление среднего значения кажется сложной задачей. Я остаюсь присоединенным к той же таблице.

Кто-нибудь может мне помочь? Спасибо!

Вы можете получить среднюю разницу в секундах, выполнив:

 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;
  

Ключевая идея заключается в том, что средняя разница — это максимум минус минимум, деленный на единицу меньше, чем количество записей.

Вы не можете сделать это с помощью одного запроса. Либо используйте хранимую процедуру, либо сделайте это на стороне вашего приложения (там это должно быть довольно просто)