Assigning rank based on consecutive order

Database
Sei
Fan

Assigning rank based on consecutive order

Hi,

I have following table:

IDTime
A1/11/2019  00:02:30
A1/11/2019  01:12:45
B1/11/2019  01:23:35
B

1/12/2019  02:44:39

C1/11/2019  02:55:10
A1/11/2019  03:00:01

Based on the order of IDs, I am trying to create a rank column. 

 

IDTimeRank
A1/11/2019  00:02:301
A1/11/2019  01:12:451
B1/11/2019  01:23:352
B1/11/2019  02:44:392
C1/11/2019  02:55:103
A1/11/2019  03:00:014

-> The rows are ordered by time asc

-> The requirement here is, I want assign same rank to IDs in consectuive rows.

-> If i use rank() function, then all the ID 'A' would be clubbed together having same rank.

-> But, i want to assign a different rank to the same ID if it is not in consecutive order.

-> In the above table, Row 5 was given rank 4 even though there are 2 'A's in row 1,2. It is considered as a new group.

 

How to create that break in grouping when the IDs are not in consecutive rows?

Thanks


Accepted Solutions
Highlighted
Ambassador

Re: Assigning rank based on consecutive order

You want to increase the rank whenever the ids in the current and the previous row are not the same:

WITH cte AS
 (
   SELECT id, ts,
      -- same id as previous row?
      CASE WHEN Lag(id)
                Over (ORDER BY ts) = id 
           THEN 0 
           ELSE 1
      END AS flag
      /** -- before TD16.10
      CASE WHEN Min(id)
                Over (ORDER BY ts
                      ROWS BETWEEN 1 Preceding AND 1 Preceding) = id
           THEN 0 
           ELSE 1
      END AS flag
      **/
   FROM mvt_datas
)
SELECT id, ts,
     Sum(flag) Over(ORDER BY ts ROWS Unbounded Preceding) AS rk
FROM cte

Thanks @Waldar for the DDL :-)

 

1 ACCEPTED SOLUTION
3 REPLIES 3
Teradata Employee

Re: Assigning rank based on consecutive order

Hi Sei,

 

Here is a solution, but I'm not so fan of it because there are 4 stats steps which are expensive.

Pretty sure it could be done in less.

create multiset volatile table mvt_datas, no log
( mvt_pi      byteint
, id          char(1)
, ts          timestamp(0)
)
primary index (mvt_pi)
on commit preserve rows;

insert into mvt_datas values (0, 'A', timestamp '2019-01-11 00:02:30');
insert into mvt_datas values (0, 'A', timestamp '2019-01-11 01:12:45');
insert into mvt_datas values (0, 'B', timestamp '2019-01-11 01:23:35');
insert into mvt_datas values (0, 'B', timestamp '2019-01-11 02:44:39');
insert into mvt_datas values (0, 'C', timestamp '2019-01-11 02:55:10');
insert into mvt_datas values (0, 'A', timestamp '2019-01-11 03:00:01');

with cte_tmp2 as
(
select id, ts
     , first_value(ts) over(partition by grp) as fv
  from cte_tmp1
)
  ,  cte_tmp1 as
(
select id, ts
     , row_number() over(                order by ts asc)
     - row_number() over(partition by id order by ts asc) as grp
  from mvt_datas
 where mvt_pi = 0
)
  select id, ts
       , dense_rank() over(order by fv) as rk
    from cte_tmp2
order by ts asc;

id  ts                   rk
--  -------------------  --
A   2019-01-11 12:02:30   1
A   2019-01-11 01:12:45   1
B   2019-01-11 01:23:35   2
B   2019-01-11 02:44:39   2
C   2019-01-11 02:55:10   3
A   2019-01-11 03:00:01   4

 

Highlighted
Ambassador

Re: Assigning rank based on consecutive order

You want to increase the rank whenever the ids in the current and the previous row are not the same:

WITH cte AS
 (
   SELECT id, ts,
      -- same id as previous row?
      CASE WHEN Lag(id)
                Over (ORDER BY ts) = id 
           THEN 0 
           ELSE 1
      END AS flag
      /** -- before TD16.10
      CASE WHEN Min(id)
                Over (ORDER BY ts
                      ROWS BETWEEN 1 Preceding AND 1 Preceding) = id
           THEN 0 
           ELSE 1
      END AS flag
      **/
   FROM mvt_datas
)
SELECT id, ts,
     Sum(flag) Over(ORDER BY ts ROWS Unbounded Preceding) AS rk
FROM cte

Thanks @Waldar for the DDL :-)

 

Sei
Fan

Re: Assigning rank based on consecutive order

Hi Waldar. Thank you for your answer.