У меня есть таблица транзакционных данных со структурой, подобной этой:
User Event TimeStamp
u1 listen_music 2017-10-18 13:28:43
u1 click_btn 2017-10-18 13:28:53
u1 logout 2017-10-18 13:29:55
u2 login 2017-10-19 13:30:20
u2 listen_music 2017-10-19 13:33:25
u2 visit_home 2017-10-19 13:34:10
u2 listen_music 2017-10-19 13:36:15
u2 listen_music 2017-10-19 13:37:35
u2 visit_profile 2017-10-19 13:40:35
Я хотел бы знать, как долго пользователь слушал музыку. Таким образом, результат будет примерно таким:
User No Delta
u1 1 10 sec
u2 1 45 sec
u2 2 80 sec
u2 3 180 sec
Я не хочу вычислять все дельты времени и затем фильтровать события 'listen_music', так как есть много других (не относящихся к делу) событий.
Поэтому на самом деле я хочу вычислить разницу времени между строкой, которая удовлетворяет условию, и следующей строкой для всех пользователей.
Я использую Impala, но приветствуются и другие решения.
Всего 1 ответ
Вы можете использовать lead()
:
select
user,
row_number() over(partition by user order by timestamp) no,
delta
from (
select
t.*,
unixtimestamp(lead(timestamp) over(partition by user order by timestamp))
- unixtimestamp(timestamp) delta
from mytable t
) t
where event = 'listen_music'
order by user, no;
Подзапрос вычисляет разницу между текущей и следующей записью того же пользователя. Внешний запрос фильтрует целевое событие и нумерует записи с помощью row_number()
.