Как использовать оконные функции в SQL для сохранения записи

Question

У меня есть набор данных, где я пытаюсь создать "идентификатор сеанса" на основе отметки времени, когда происходит определенное событие (т.е. загрузка) в моем случае

Мои данные:

userid  event  timestamp
xyz     load   '2016-12-01 08:21:13:000'
xyz     view   '2016-12-01 08:21:14:000'
xyz     view   '2016-12-01 08:21:16:000'
xyz     exit   '2016-12-01 08:21:17:000'
xyz     load   '2016-12-02 08:01:13:000'
xyz     view   '2016-12-02 08:01:16:000'
abc     load   '2016-12-01 08:11:13:000'
abc     view   '2016-12-01 08:11:14:000'

То, чего я пытаюсь добиться, - это создать новый столбец с именем session_start_timestamp, в котором строка помечается относительно последней "загрузки" для каждого пользователя.

Я знаю, как сделать это, создав таблицу подмножеств (взяв минимальную временную метку и само-соединение), но есть ли функция lag/lead/max/partition, которая может сделать это вместо этого?

Окончательный результат должен выглядеть следующим образом:

userid  event  timestamp                  session_start_timestamp
xyz     load   '2016-12-01 08:21:13:000'  '2016-12-01 08:21:13:000'
xyz     view   '2016-12-01 08:21:14:000'  '2016-12-01 08:21:13:000'
xyz     view   '2016-12-01 08:21:16:000'  '2016-12-01 08:21:13:000'
xyz     exit   '2016-12-01 08:21:17:000'  '2016-12-01 08:21:13:000'
xyz     load   '2016-12-02 08:01:13:000'  '2016-12-02 08:01:13:000'
xyz     view   '2016-12-02 08:01:16:000'  '2016-12-02 08:01:13:000'
abc     load   '2016-12-01 08:11:13:000'  '2016-12-01 08:11:13:000'
abc     view   '2016-12-01 08:11:14:000'  '2016-12-01 08:11:13:000'

Answer 1

Это проблема разрыва / острова:

SQL DEMO (postgresql)

1. Вы рассчитываете разрыв или точки разрыва.
2. Затем с использованием накопительного SUM() рассчитать группы
3. Затем выберите MIN() время от каждой группы

-

WITH gap as (
    SELECT *, CASE WHEN "event" = 'load' THEN 1 ELSE 0 END as gap
    FROM Table1
), island as (
    SELECT *, SUM(gap) OVER (PARTITION BY "userid" ORDER BY "timestamp" ) as grp
    FROM gap
)    
SELECT *, MIN("timestamp") OVER (PARTITION BY "userid", "grp") as new_timestamp
FROM island

ВЫХОД

Вы можете объединить первые два запроса:

WITH island as (
    SELECT *, SUM (CASE WHEN "event" = 'load' THEN 1 ELSE 0 END ) 
              OVER (PARTITION BY "userid" ORDER BY "timestamp" ) as grp
    FROM Table1
)    
SELECT *, MIN("timestamp") OVER (PARTITION BY "userid", "grp") as new_timestamp
FROM island