В SQL (Impala), как рассчитать дельту времени между строками, которая удовлетворяет условию и следующей?

У меня есть таблица транзакционных данных со структурой, подобной этой:

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, но приветствуются и другие решения.

sql 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() .