SQL - How to select x number of rows prior to a specific row

user_7373440 0
Viewed 140

I have this table:

   ts  |  user_id  |   event   |  
-------------------------------
 1500        a         eat 
 1501        a         walk 
 1502        a         sleep 
 1500        b         eat 
 1501        b         sleep 
 1502        b         wake
 1500        c         walk 
 1501        c         eat
 1502        c         sit
 1503        c         sleep 
 1504        c         wake

So I want to select x number of rows prior to a certain event, let's say I want to select 2 events before sleep per user_id.

My final table result should look like:

user_id  |   event   |   rank  |
--------------------------------
    a         eat         1
    a         walk        2
    a         sleep       3
    b         NULL        0
    b         eat         1
    b         sleep       2
    c         eat         2
    c         sit         3
    c         sleep       4

How to do this in SQL (specifically Redshift SQl)

amazon-redshift gaps-and-islands window-functions datetime sql
2 Answers

Hmmm . . . You can use lead():

select t.*
from (select t.*,
             lead(event) over (partition by user_id order by ts) as next_event,
             lead(event, 2) over (partition by user_id order by ts) as next_event2
      from t
     ) t
where 'sleep' in (event, next_event, next_event2);

Note: This only returns rows that are in the data. If you need for rows to be generated additional logic is needed.

EDIT:

You can actually generalize this:

select t.*
from (select t.*,
             sum(case when event = 'sleep') over (partition by user_id order by ts rows between current row and 2 following) as cnt_sleep
      from t
     ) t
where cnt_sleep > 0;

This counts the number of "sleep"s in the next n rows (well, n - 1). It returns a row if "sleep" is found in any of them.

user_11440350

This is a gaps-and-islands problem, where you want the first and the last two rows of each island.

Probably the safest approach is a window sum of the sleep events to define the group, and then filtering with row_number():

select *
from (
    select t.*, 
        row_number() over(partition by user_id, grp order by ts) rn_asc,
        row_number() over(partition by user_id, grp order by ts desc) rn_desc
    from (
        select t.*,
            sum(case when event = 'sleep' then 1 else 0 end) 
                over(partition by user_id order by ts desc)  grp
        from mytable t
    ) t
) t
where (rn_asc = 1 or rn_desc <= 2) and grp > 0
order by user_id, ts

We define islands with a window count of "sleep" events in descending order. Then, we just enumerate each island rows in both ascending and descending order, and filter on the records we are interested in.

Demo on DB Fiddle:

  ts | user_id | event | grp | rn_asc | rn_desc
---: | :------ | :---- | --: | -----: | ------:
1500 | a       | eat   |   1 |      1 |       3
1501 | a       | walk  |   1 |      2 |       2
1502 | a       | sleep |   1 |      3 |       1
1500 | b       | eat   |   1 |      1 |       2
1501 | b       | sleep |   1 |      2 |       1
1500 | c       | walk  |   1 |      1 |       4
1502 | c       | sit   |   1 |      3 |       2
1503 | c       | sleep |   1 |      4 |       1

EDIT

Redshift requires a window frame in the order by clause of window functions. So it is a bit longer to type:

select *
from (
    select t.*, 
        row_number() over(
            partition by user_id, grp 
            order by ts rows between unbounded preceding and current row
        ) rn_asc,
        row_number() over(
            partition by user_id, grp 
            order by ts rows between unbounded preceding and current row
        ) rn_desc
    from (
        select t.*,
            sum(case when event = 'sleep' then 1 else 0 end) over(
                partition by user_id 
                order by ts desc
                order by ts rows between unbounded preceding and current row
            )  grp
        from mytable t
    ) t
) t
where (rn_asc = 1 or rn_desc <= 2) and grp > 0
order by user_id, ts
user_106767160
Related