sql - Redshift : Find preceding rows that satisfy condition to constitute a sequence

Question

一周以来，我一直在努力寻找以下 Redshift 谜语的解决方案(我觉得我已经对它着迷了):

Redshift 中有一个事件表(“event_user_item”)，用户通过输入出现在 event_value 列中的项目代码来触发某些项目的事件。

提交失败由event_type序列PageLoad-ItemCode-ErrorResponse组成，但是这样的事件类型不一定是连续的，意思每个 user_id 之间可以有许多其他事件类型。

我发布了一个基于 3 个不同 user_id 的小摘录，应该说明关注失败提交的相关场景。

ord_num event_type          event_value     user_id     event_datetime
1       PageLoad                            124         03/09/2018 21:48:39
2       ItemCode            LG56731         124         03/09/2018 21:48:53
4       Details1PageLoad                    124         03/09/2018 21:48:56
8       PageLoad                            124         03/09/2018 22:02:23
9       ItemCode            GU07019         124         03/09/2018 22:02:32
10      ErrorResponse       Some message    124         03/09/2018 22:02:32
51      PageLoad                            228         04/09/2018 12:38:30
52      ItemCode            EQ23487         228         04/09/2018 12:38:33
53      ErrorResponse       Some message    228         04/09/2018 12:38:34
54      PageLoad                            304         04/09/2018 15:43:14
55      ItemCode            OB68102         304         04/09/2018 15:43:57
56      ErrorResponse       Some message    304         04/09/2018 15:43:58
57      ItemCode            PB68102         304         04/09/2018 15:44:21
58      ErrorResponse       Some message    304         04/09/2018 15:44:22
59      PageLoad                            304         05/09/2018 11:19:37
60      ItemCode            OB68102         304         05/09/2018 11:20:17
62      Details1PageLoad                    304         05/09/2018 11:20:20

目标:找到每个 user_id 每个 ItemCode 的失败提交数。重要的是不要混淆失败提交和成功提交的项目代码。此外，同一项目代码也可能有多个失败条目。

我不是 Redshift 方面的专家，尤其是它的窗口函数，但我尝试坚持的第一个想法是 LAG 函数。为了做到这一点，我打算确定有资格被计算的 ord_nums 序列，例如

ord_num event_type          event_value     user_id event_datetime           error?     sequence
1       PageLoad                            124     03/09/2018 21:48:39     
2       ItemCode            LG56731         124     03/09/2018 21:48:53     
4       Details1PageLoad                    124     03/09/2018 21:48:56     
8       PageLoad                            124     03/09/2018 22:02:23     
9       ItemCode            GU07019         124     03/09/2018 22:02:32     
10      ErrorResponse       Some message    124     03/09/2018 22:02:32     1       8-9-10
51      PageLoad                            228     04/09/2018 12:38:30     
52      ItemCode            EQ23487         228     04/09/2018 12:38:33     
53      ErrorResponse       Some message    228     04/09/2018 12:38:34     1       51-52-53
54      PageLoad                            304     04/09/2018 15:43:14     
55      ItemCode            OB68102         304     04/09/2018 15:43:57     
56      ErrorResponse       Some message    304     04/09/2018 15:43:58     1       54-55-56
57      ItemCode            PB68102         304     04/09/2018 15:44:21     
58      ErrorResponse       Some message    304     04/09/2018 15:44:22     1       54-57-58
59      PageLoad                            304     05/09/2018 11:19:37     
60      ItemCode            OB68102         304     05/09/2018 11:20:17     
62      Details1PageLoad                    304     05/09/2018 11:20:20     

所以根据 user_id 应该有以下计数:

user_id     nr_failed_submissions   
124         1   
228         1   
304         2

但是，从上面的数据集和预期结果可以看出，无法预测要向后移动多少条记录，我需要一个不能放在 LAG 内的附加条件...

我尝试了很多选择，但没有一个适合。

非常有用和有见地的帖子已经

• lag function to get the last different value(redshift) ,
• Last Non-Null Value in Redshift by Group
• How can I get the Redshift/Postgresql LAG window function to selectively exclude records?
• Assign a Sequence (session ID) to my table based on A value in field

但直到现在，我还没有设法将它们全部融合成可行的解决方案。在 Redshift 中一定有办法做到这一点？

Answer 1

此查询将创建“时间范围”，其中 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 事件。在这两个结果集之间加入应该可以找到我们正在寻找的东西。

redshift 的一个特殊性让我在尝试直接连接这两个数据集时遇到了一些麻烦，所以我不得不创建两个临时表。这个可怕的格式查询给了我预期的结果:

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