Пронумеровать сессии в MySQL

Question

Есть исходная таблица:

user_id - пользователь, project_id - проект, event_time - время события.

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

В итоге должна получиться такая таблица:

У меня уже есть наброски в виде:

Был написан такой код:

    with user_events_id as (SELECT  user_id,
        project_id,
        event_time,
        concat(user_id, "_", project_id) as id
from user_events), 

session_marker as (select *,
timediff(event_time, previous_time) as diff_time,
case when timediff(event_time, previous_time) is null then 'old' 
when  timediff(event_time, previous_time)<'00:45:00' then 'old' 
else 'new' end as new_old_session
from (select *, LAG (event_time) over ( partition by id order by id, event_time ) as previous_time
from user_events_id) as a
)

select *,
CASE
        WHEN diff_time > '00:45:00' THEN @session_num := @session_num + 1
        ELSE @session_num
    END 
      AS session_number
FROM
    session_marker,
    (SELECT @session_num := 1) AS session_counter
ORDER BY
    id, event_time

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

Необходимо чтобы для каждого нового идентификатора (user+project) нумерация начиналась снова с 1.

Результат, который должен выйти, находится на "Итоговой таблице".

Спасибо!

Answer 1

Идея достаточно простая и часто применяется. Посчитать количество разрывов (>45 мин) нарастающим итогом - это и будет номер сессии.
Пример:

select * 
  ,sum(case when dif>45 then 1 else 0 end)
     over(partition by user_id order by eventtime)+1 session_number 
from(
   select *
     ,timestampdiff(minute,lag(eventtime,1,eventtime)
                           over(partition by user_id order by eventtime)
               ,eventtime) dif
   from test
  ) t

Получится так

user_id	project_id	eventtime	dif	session_number
1	1	2019-12-01 00:00:00.000	0	1
1	2	2019-12-01 00:01:00.000	1	1
1	1	2019-12-01 00:10:41.000	9	1
1	1	2019-12-01 00:15:33.000	5	1
1	2	2019-12-01 00:46:00.000	31	1
1	1	2019-12-01 10:05:00.000	559	2
1	1	2019-12-01 10:35:00.000	30	2
1	1	2019-12-01 15:00:00.000	265	3
2	1	2019-12-01 15:00:00.000	0	1

БЛОГ НА HUSL

Пронумеровать сессии в MySQL

Ответы (1 шт):