Запрос Redshift для объединения результатов, если данные непрерывны в таблице

У меня есть требование в redshift, где мне нужно объединить результат, если данные непрерывны. У меня есть следующая таблица, где user_id, product_id-varchar и login_time, log_out_time-метка времени.

user_id    product_id   login_time                log_out_time
----------------------------------------------------------------------
ashok      facebook     1/1/2017 1:00:00 AM       1/1/2017 2:00:00 AM
ashok      facebook     1/1/2017 2:00:00 AM       1/1/2017 3:00:00 AM
ashok      facebook     1/1/2017 3:00:00 AM       1/1/2017 4:00:00 AM
ashok      linked_in    1/1/2017 5:00:00 AM       1/1/2017 6:00:00 AM
ashok      linked_in    1/1/2017 6:00:00 AM       1/1/2017 7:00:00 AM
ashok      facebook     1/1/2017 8:00:00 AM       1/1/2017 9:00:00 AM
ram        facebook     1/1/2017 9:00:00 AM       1/1/2017 10:00:00 AM
ashok      linked_in    1/1/2017 7:00:00 AM       1/1/2017 8:00:00 AM

Мне нужно объединить результат, если данные непрерывны для данного идентификатора пользователя для каждого продукта. Поэтому мой вывод должен выглядеть следующим образом: 

user_id    product_id   login_time                log_out_time
----------------------------------------------------------------------
ashok      facebook     1/1/2017 1:00:00 AM       1/1/2017 4:00:00 AM
ashok      facebook     1/1/2017 8:00:00 AM       1/1/2017 9:00:00 AM
ashok      linked_in    1/1/2017 5:00:00 AM       1/1/2017 8:00:00 AM
ram        facebook     1/1/2017 9:00:00 AM       1/1/2017 10:00:00 AM

Я попытался со следующим запросом, но это не помогло мне, 

SELECT user_id, product_id, MIN(login_time), MAX(log_out_time) FROM TABLE_NAME GROUP BY user_id, product_id

Выше запрос не может дать мой требуемый результат поскольку у него нет логики, чтобы проверить данные находятся в непрерывном времени. Для этого мне нужен запрос без использования какой-либо пользовательской функции, но я могу использовать любую встроенную функцию redshift.

1 4
sql database gaps-and-islands

1 ответ:

Вы можете использовать lag() для определения того, где начинаются группы, затем кумулятивную сумму для идентификации групп, а затем group by для агрегирования результатов: 

select user_id, product_id, min(login_time), max(log_out_time)
from (select t.*,
             sum(case when prev_lt = login_time then 0 else 1 end) over
                 (partition by user_id, product_id
                  order by login_time
                  rows between unbounded preceding and current row
                 ) as grp
      from (select t.*,
                   lag(log_out_time) over (partition by user_id, product_id order by login_time) as prev_lt
            from t
           ) t
     ) t
group by user_id, product_id, grp;
5