sql - Redshift 中的窗函数

Question

我有一些看起来像这样的数据:

CustID  EventID     TimeStamp
1       17          1/1/15 13:23
1       17          1/1/15 14:32
1       13          1/1/25 14:54
1       13          1/3/15 1:34
1       17          1/5/15 2:54
1       1           1/5/15 3:00
2       17          2/5/15 9:12
2       17          2/5/15 9:18
2       1           2/5/15 10:02
2       13          2/8/15 7:43
2       13          2/8/15 7:50
2       1           2/8/15 8:00

我正在尝试使用 row_number 函数让它看起来像这样:

CustID  EventID     TimeStamp      SeqNum
1       17          1/1/15 13:23    1
1       17          1/1/15 14:32    1
1       13          1/1/25 14:54    2
1       13          1/3/15 1:34     2
1       17          1/5/15 2:54     3
1       1           1/5/15 3:00     4
2       17          2/5/15 9:12     1
2       17          2/5/15 9:18     1
2       1           2/5/15 10:02    2   
2       13          2/8/15 7:43     3
2       13          2/8/15 7:50     3
2       1           2/8/15 8:00     4

我试过这个:

row_number () over 
          (partition by custID, EventID
           order by custID, TimeStamp asc) SeqNum]

但得到了这个:

CustID  EventID     TimeStamp      SeqNum
1       17          1/1/15 13:23    1
1       17          1/1/15 14:32    2
1       13          1/1/25 14:54    3
1       13          1/3/15 1:34     4
1       17          1/5/15 2:54     5
1       1           1/5/15 3:00     6
2       17          2/5/15 9:12     1
2       17          2/5/15 9:18     2
2       1           2/5/15 10:02    3   
2       13          2/8/15 7:43     4
2       13          2/8/15 7:50     5
2       1           2/8/15 8:00     6

我怎样才能让它根据 EventID 的变化排序？

Answer 1

这很棘手。你需要一个多步骤的过程。您需要识别组(row_number() 的区别适用于此)。然后，为每个组分配一个递增常数。然后使用dense_rank():

select sd.*, dense_rank() over (partition by custid order by mints) as seqnum
from (select sd.*,
             min(timestamp) over (partition by custid, eventid, grp) as mints
      from (select sd.*,
                   (row_number() over (partition by custid order by timestamp) -
                    row_number() over (partition by custid, eventid order by timestamp)
                   ) as grp
            from somedata sd
           ) sd
     ) sd;

另一种方法是使用lag() 和累加和:

select sd.*,
       sum(case when prev_eventid is null or prev_eventid <> eventid
                then 1 else 0 end) over (partition by custid order by timestamp
                                        ) as seqnum
from (select sd.*,
             lag(eventid) over (partition by custid order by timestamp) as prev_eventid
      from somedata sd
     ) sd;

编辑:

我上次使用 Amazon Redshift 时它没有 row_number()。你可以这样做:

select sd.*, dense_rank() over (partition by custid order by mints) as seqnum
from (select sd.*,
             min(timestamp) over (partition by custid, eventid, grp) as mints
      from (select sd.*,
                   (row_number() over (partition by custid order by timestamp rows between unbounded preceding and current row) -
                    row_number() over (partition by custid, eventid order by timestamp rows between unbounded preceding and current row)
                   ) as grp
            from somedata sd
           ) sd
     ) sd;