Есть таблица time_user с полями:
user_id
event
time
В неё пишутся события, которые генерирует пользователь.
Как можно вычислить среднюю длину сессии по всем сессиям всех пользователей, если под сессией подразумевается последовательность событий одного пользователя, промежуток между которыми(событиями) не превышает 5-ти минут?
Я набросал запросец, но он считает неверно.
SELECT COUNT(t.user_id) cnt_session, t.user_id
FROM time_user t
WHERE ROUND(
(
(
SELECT t1.`time`
FROM time_user t1
WHERE t1.user_id = t.user_id AND t1.`time` > t.`time` LIMIT 1
)
- t.`time`
) / 60
) > 5
GROUP BY t.user_id
ORDER BY t.`time` ASC
Запрос создания таблицы:
CREATE TABLE `time_user` (
`user_id` INT(11) NULL DEFAULT NULL,
`time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`event` ENUM('Y','N') NULL DEFAULT NULL,
INDEX `user_id` (`user_id`),
INDEX `time` (`time`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
Помогите, пожалуйста
Ответ
select avg(length)
from (
select user_id, snum, TIMESTAMPDIFF(SECOND,min(time),max(time)) length
from(
select user_id,time,
@snum:=if(@user=user_id and TIMESTAMPDIFF(SECOND,@ptime,time) <= 60*5,
@snum,@snum+1) snum,
@user:=user_id, @ptime:=time
from time_user,
(select @ptime:=0, @user:=0, @snum:=0) A
order by user_id, time
) B
group by snum
) C
Подзапрос B нумерует сессии подряд, ориентируясь на id пользователя и время события из предыдущей записи (при сортировке по пользователю и времени), запомненное в переменных. Подзапрос C группирует по полученным номерам сессий и вычисляет длину сессии как разность между минимальным и максимальным временем в ней. Внешний запрос берет среднюю длину сессии (в секундах). Если надо сессии в разрезе пользователей добавьте соответствующий group by
Комментариев нет:
Отправить комментарий