How to apply windows for each specific column in SQL?

Question

I want to get last event done per each user for each specific unit_of_measure:

I have this table:

person_id   event_time       event_derscription   unit_of_measure 
-----------------------------------------------------------------
1           20200801120101  "some description"     "unit1"
1           20200801120501  "some description 2"   "unit1"
1           20200801120501  "some description 2"   "unit9"
2           20200801120301  "some description 3"   "unit1"
2           20200801120501  "some description 4"   "unit1"

Expected output is:

person_id   event_time       event_derscription   unit_of_measure 
-----------------------------------------------------------------
1           20200801120101  "some description"     "unit1"
2           20200801120301  "some description 2"   "unit1"
1           20200801120501  "some description 2"   "unit9"

What I tried:

select * 
from 
    (select 
         person_id, event_time, event_derscription, unit_of_measure, 
         rank() over (partition by unit_of_measure order by event_time desc) as RN 
     from 
         test.person_events 
     where 
         partition_name = 20200801 
     group by 
         person_id, event_time, event_description, unit_of_measure) 
where 
    RN = 1;  // I try to use group by person_id to get the result for each person_id but it did not work

The output of my above code is:

person_id   event_time       event_derscription   unit_of_measure 
-----------------------------------------------------------------
2           20200801120301  "some description 2"   "unit1"
1           20200801120501  "some description 2"   "unit9"

is there are any mistake I did ?

score 0 · Accepted Answer · answered Mar 16 '21 at 12:19

I think the query you want is:

select person_id, event_time, event_derscription, unit_of_measure
from (select pe,
             row_number() over (partition BY unit_of_measure, person_id order by event_time desc) as seqnum
      from test.person_events pe
      where partition_name = 20200801 
     ) pe
where seqnum = 1;

Notes:

The main fix to your problem is to include person_id in the partition by.
I see no need for group by. Nothing in your question mentions why it would be desirable.
To fetch one row, use row_number() rather than rank(). Even if thee are no duplicates, it conveys the intention that you want one row.

@code . . . I realize now that you were using `rank()` on purpose to get the different `person_id`s and then using `group by` to remove it. Clever attempt at putting the pieces together -- but this is the way to go. — Gordon Linoff, Mar 16 '21 at 13:05

How to apply windows for each specific column in SQL?

1 Answers1