Redshift: найти предыдущие строки, которые удовлетворяют условию для создания последовательности

Этот запрос создаст «временные диапазоны», где time1 представляет собой временную метку события PageLoad, а time2 представляет собой временную метку следующего события PageLoad для этого пользователя:

WITH timeranges AS
(
  SELECT A.user_id,
         A.event_datetime AS time1,
         nvl(MAX(B.event_datetime),'2099-01-01') AS time2
  FROM foo AS A
    LEFT JOIN foo AS B
           ON A.user_id = B.user_id
          AND A.event_datetime < B.event_datetime
          AND A.event_type = B.event_type
  WHERE A.event_type = 'PageLoad'
  GROUP BY A.user_id,
           A.event_datetime
)

Этот запрос основан на этом, чтобы связать каждое событие 'ItemCode' с меткой времени соответствующего ему 'PageLoad':

SELECT timeranges.time1 AS pageloadtime,
       foo.*
FROM foo
  LEFT JOIN timeranges
         ON foo.event_datetime >= timeranges.time1
        AND foo.event_datetime < timeranges.time2
WHERE foo.event_type = 'ItemCode'

Этот запрос определяет, попали ли какие-либо события ErrorResponse в каждый из этих диапазонов:

SELECT timeranges.time1 AS pageloadtime,
       timeranges.user_id,
       BOOL_OR(foo.event_type = 'ErrorResponse') AS has_error
FROM timeranges
  LEFT JOIN foo
         ON event_datetime > time1
        AND event_datetime < time2
GROUP BY timeranges.time1,
         timeranges.user_id
HAVING has_error;

И это должно дать нам все, что нам нужно - для каждого события загрузки страницы мы знаем (1) есть ли ошибка в этой загрузке страницы и (2) мы знаем все события ItemCode, связанные с этой полезной нагрузкой. Объединение этих двух наборов результатов должно дать нам то, что мы ищем.

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

create temporary table items_per_pageload as 
with timeranges as (select A.user_id, A.event_datetime as time1, nvl(max(B.event_datetime), '2099-01-01') as time2 from event_user_item as A left join event_user_item as B on A.user_id=B.user_id and A.event_datetime < B.event_datetime and A.event_type=B.event_type
where A.event_type='PageLoad' group by A.user_id, A.event_datetime)
select timeranges.time1 as pageloadtime, event_user_item.* from event_user_item left join timeranges on event_user_item.event_datetime>=timeranges.time1 and event_user_item.event_datetime<timeranges.time2 where event_user_item.event_type='ItemCode'

create temporary table pageloads_with_errors as 
with timeranges as (select A.user_id, A.event_datetime as time1, nvl(max(B.event_datetime), '2099-01-01') as time2 from event_user_item as A left join event_user_item as B on A.user_id=B.user_id and A.event_datetime < B.event_datetime and A.event_type=B.event_type
where A.event_type='PageLoad' group by A.user_id, A.event_datetime)
select timeranges.time1 as pageloadtime, timeranges.user_id, bool_or(event_user_item.event_type='ErrorResponse') as has_error from timeranges left join event_user_item on event_datetime > time1 and event_datetime < time2
group by timeranges.time1, timeranges.user_id having has_error;

select count(1), user_id, event_value from (
select items_per_pageload.* from items_per_pageload join pageloads_with_errors on items_per_pageload.user_id = pageloads_with_errors.user_id and items_per_pageload.pageloadtime = pageloads_with_errors.pageloadtime 
) group by user_id, event_value

Следующие подходы и запросы, основанные на ответе 1 Джейсона Розендейла, работают для меня так, как должны:

create temporary table items_per_pageload as 
with timeranges as (
  select A.user_id
    ,A.event_datetime as time1
    ,nvl(max(B.event_datetime), '2099-01-01') as time2
    ,LEAD(A.event_datetime,1) over (partition by A.user_id order by A.event_datetime) as next_load_time 
  from event_user_item as A 
  left join event_user_item as B on A.user_id=B.user_id and A.event_datetime < B.event_datetime and A.event_type=B.event_type
  where A.event_type='PageLoad' 
  group by A.user_id, A.event_datetime
  )
select timeranges.time1 as pageloadtime, event_user_item.* 
from event_user_item left join timeranges on event_user_item.event_datetime>=timeranges.time1 and event_user_item.event_datetime<nvl(timeranges.next_load_time,timeranges.time2) 
where event_user_item.event_type='ItemCode';

create temporary table pageloads_with_errors as 
with timeranges as (
  select A.user_id
    ,A.event_datetime as time1
    ,nvl(max(B.event_datetime), '2099-01-01') as time2
    ,LEAD(A.event_datetime,1) over (partition by A.user_id order by A.event_datetime) as next_load_time 
  from event_user_item as A left join event_user_item as B on A.user_id=B.user_id and A.event_datetime < B.event_datetime and A.event_type=B.event_type
  where A.event_type='PageLoad' 
  group by A.user_id, A.event_datetime
  )
select timeranges.time1 as pageloadtime,timeranges.user_id,bool_or(event_user_item.event_type='ErrorResponse') as has_error 
from timeranges 
left join event_user_item on event_datetime > time1 and event_datetime < nvl(next_load_time,time2)
group by timeranges.time1,timeranges.user_id 
having has_error;

/* final counts */
select count(1), user_id, event_value from (
    select items_per_pageload.* 
    from items_per_pageload 
    join pageloads_with_errors on items_per_pageload.user_id = pageloads_with_errors.user_id and items_per_pageload.pageloadtime = pageloads_with_errors.pageloadtime 
) 
group by user_id, event_value;