Блог

У нас есть приложение, которое помогает людям находить других баскетболистов для игры. Мне было поручено выяснить среднее время между каждым участником игры, например, Game 2445, к которому присоединился один пользователь, затем другой, затем другой. Я хочу выяснить временные рамки между каждым из этих объединителей.

Таблица game_users имеет следующий формат

 game_id | user_id | active | created_at 

1234    |3455    |   1     | 2020-01-26 05:18:18

1234    |5673    |   1     |2020-02-16 04:16:15

1234    |2344    |   1     |2020-02-22 18:59:55
 

Created_at здесь относится к времени, когда пользователь присоединился к игре

Как я могу узнать среднее время, которое требуется пользователю, чтобы присоединиться к игре?

сначала вам нужно выяснить разницу во времени между двумя соединениями, вы можете использовать функцию window LEAD() и TIMESTAMPDIFF() (в моей версии я показываю разницу в часах, вы можете изменить ее на любую, которая имеет для вас смысл), затем вы можете рассчитать среднее значение с помощью cte :

 ;with cte as (
SELECT  
  * 
  ,TIMESTAMPDIFF(HOUR, created_at ,LEAD(created_at) OVER (Partition by game_id order by created_at)) as TimeDifference
FROM game_users 
)

SELECT 
   game_id 
   , AVG(TimeDifference) as avgTimedifference
FROM cte
GROUP BY game_id