CODEWITHSUNDEEP
sqlsql-server
yet_another_programmer
yet_another_programmer

Reputation: 327

Rank values from table based on temporal sequences of values

I have a table similar to this one, representing which drivers were driving different cars at certain times.

CAR_ID    DRIVER_ID    DT
  10          A      10:00
  10          A      12:00
  10          A      14:00
  10          B      16:00
  10          B      17:00
  10          B      20:00
  10          A      21:00
  10          A      22:00
  20          C      15:00
  20          C      18:00

Where DT is a datetime. I'm trying to have something similar to what I would obtain using a DENSE_RANK() function but generating a new number when there is a change on the column DRIVER_ID between two drivers. This would be my expected output:

CAR_ID    DRIVER_ID    DT    RES
  10          A      10:00    1
  10          A      12:00    1
  10          A      14:00    1
  10          B      16:00    2
  10          B      17:00    2
  10          B      20:00    2
  10          A      21:00    3    # 
  10          A      22:00    3    # 
  20          C      15:00    4
  20          C      18:00    4

Using DENSE_RANK() OVER (PARTITION BY CAR_ID, DRIVER_ID ORDER BY DT) AS RES I get the two elements marked with a # as members of the same group as the first three rows, but I want them to be different, because of the "discontinuity" (the car was driven by another driver from 16:00 to 20:00). I can't seem to find a solution that doesn't include a loop. Is this possible?

Any help would be greatly appreciated.

Upvotes: 2

Views: 46

Answers (2)

Steve
Steve

Reputation: 960

You need to do.a row_number partitioned by car and ordered by dt. Also you need to do a row_number partitioned by car and driver and ordered by dt. Subtracting the second of these from the first gives you a unique "segment" number - which in this case will represent the continuous period of possession that each driver had of each car.

This segment number value has no Intrinsic meaning - it is just guaranteed to be different for each segment within the partition of cars and drivers. Then use this segment number as an additional partition for whatever function you are trying to apply.

As a note, however, I couldn't work out how you got the results you've displayed for RES from the code you quote, and thus I'm not exactly sure what you are trying to achieve overall.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

This can be done with lag and running sum.

select t.*,sum(case when prev_driver = driver then 0 else 1 end) over(partition by id order by dt) as res
from (select t.*,lag(driver_id) over(partition by id order by dt) as prev_driver
      from tbl
     ) t

Upvotes: 2

Related Questions