#sql #amazon-redshift
#sql #амазонка-красное смещение
Вопрос:
У меня есть набор данных чата с действиями пользователей, которые присоединяются и покидают комнату. Пользователь может присоединиться и покинуть комнату несколько раз. Мне нужно узнать время, потраченное каждым пользователем на каждую комнату.
room_id user_id event time 1 1 join 2021-12-10 09:00:00 1 2 join 2021-12-10 09:10:00 1 3 join 2021-12-10 09:20:00 1 2 leave 2021-12-10 09:30:00 1 1 leave 2021-12-10 09:40:00 1 1 join 2021-12-10 09:50:00 1 3 leave 2021-12-10 10:00:00 1 1 leave 2021-12-10 10:10:00 Что мне нужно, так это:
room_id user_id time_spend 1 1 60 1 2 20 1 1 30 Я новичок в красном смещении. Я попытался использовать функцию задержки, но не смог добавить условие, например, присоединиться и уйти. Может ли кто-нибудь помочь мне решить эту проблему?
Ответ №1:
Вот начало, основанное на ваших образцах данных:
create table test (room_id int, user_id int, event varchar(16), event_time timestamp); insert into test values (1, 1, 'join', '2021-12-10 09:00:00'), (1, 2, 'join', '2021-12-10 09:10:00'), (1, 3, 'join', '2021-12-10 09:20:00'), (1, 2, 'leave', '2021-12-10 09:30:00'), (1, 1, 'leave', '2021-12-10 09:40:00'), (1, 1, 'join', '2021-12-10 09:50:00'), (1, 3, 'leave', '2021-12-10 10:00:00'), (1, 1, 'leave', '2021-12-10 10:10:00') ; select * from test order by user_id, event_time; select room_id, user_id, sum(time_spent) as time_spent from ( select room_id, user_id, event_time, decode(event,'join',event_time) as join_time, lead(decode(event,'leave',event_time)) ignore NULLS OVER (partition by room_id, user_id order by event_time) as leave_time, datediff(min, join_time, leave_time) as time_spent from test) group by room_id, user_id order by room_id, user_id;