Row Numbering and Sub Grouping

Question

I'm hoping someone can help; I'd class myself as a novice at Oracle/SQL, but so far I've managed to get what I need but I've hit a brick wall in how to approach my query.

I have a dataset of activites, each activity has a unique ID that is consistent throughout its lifecycle; each activity has multiple events indicated by time; each event can have a different status. See below for an example set.

What I want to achieve is a list that contains my data ordered by activity id and time with an incremental ID for each activity (1,2,3,4); but I also need a secondary column which starts from 1 and increments when the status differs from the previous row.

Below is an example of my data:

    ACTIVITY_ID | EVENT_TIMESTAMP      | EVENT_STATUS
    --------------------------------------------------------
    A001        | 01/01/2020 09:00:00  | STATUS A
    A001        | 01/01/2020 10:10:00  | STATUS B
    A001        | 01/01/2020 11:20:00  | STATUS C
    A001        | 01/01/2020 12:30:00  | STATUS C
    A002        | 01/01/2020 13:40:00  | STATUS F
    A002        | 01/01/2020 17:50:00  | STATUS F
    A002        | 01/01/2020 17:53:00  | STATUS G

Utilising the ROW_NUMBER and PARTITION BY I have achieved an output that gives me my ordered list like so:

    ACTIVITY_ID | EVENT_TIMESTAMP      | EVENT_STATUS   | EVENT_NUMBER
    --------------------------------------------------------------------
    A001        | 01/01/2020 09:00:00  | STATUS A       | 1
    A001        | 01/01/2020 10:10:00  | STATUS B       | 2  
    A001        | 01/01/2020 11:20:00  | STATUS C       | 3
    A001        | 01/01/2020 12:30:00  | STATUS C       | 4
    A002        | 01/01/2020 13:40:00  | STATUS F       | 1
    A002        | 01/01/2020 17:50:00  | STATUS F       | 2
    A002        | 01/01/2020 17:53:00  | STATUS G       | 3

What I'm struggling with is the sub-grouping result I'm lookig for (below), should this just be the same as the ROW_NUMBER but with a partition against the Event Status? I've tried various attempts but the partition always resets to 1 when the status change as opposed to starting from 1, and then incrementing with each change?

    ACTIVITY_ID | EVENT_TIMESTAMP      | EVENT_STATUS   | EVENT_NUMBER | EVENT_STATUS_GROUP
    ----------------------------------------------------------------------------------------
    A001        | 01/01/2020 09:00:00  | STATUS A       | 1            | 1
    A001        | 01/01/2020 10:10:00  | STATUS B       | 2            | 2
    A001        | 01/01/2020 11:20:00  | STATUS C       | 3            | 3
    A001        | 01/01/2020 12:30:00  | STATUS C       | 4            | 3
    A001        | 01/01/2020 12:30:00  | STATUS A       | 5            | 4

    A002        | 01/01/2020 13:40:00  | STATUS F       | 1            | 1
    A002        | 01/01/2020 17:50:00  | STATUS F       | 2            | 1
    A002        | 01/01/2020 17:53:00  | STATUS G       | 3            | 2

I hope this is clear enough, if not, please do ask any questions.

user_5841306 · Answer

You can use DENSE_RANK() analytic function :

SELECT t.*, 
       ROW_NUMBER() OVER (PARTITION BY ACTIVITY_ID ORDER BY EVENT_STATUS ) 
                                                                         AS EVENT_NUMBER,
       DENSE_RANK() OVER (PARTITION BY ACTIVITY_ID ORDER BY EVENT_STATUS ) 
                                                                   AS EVENT_STATUS_GROUP
  FROM tab t
 ORDER BY ACTIVITY_ID, EVENT_NUMBER

Demo

user_1144035 · Answer

You can use lag() and a cumulative sum to calculate the number of changes:

SELECT t.*, 
       ROW_NUMBER() OVER (PARTITION BY ACTIVITY_ID ORDER BY EVENT_TIMESTAMP) AS EVENT_NUMBER,
       SUM(CASE WHEN PREV_EVENT_STATUS = EVENT_STATUS THEN 0 ELSE 1 END) OVER
           (PARTITION BY ACTIVITY_ID ORDER BY EVENT_TIMESTAMP) AS EVENT_STATUS_GROUP
FROM (SELECT t.*,
             LAG(EVENT_STATUS) OVER (PARTITION BY ACTIVITY_ID ORDER BY EVENT_TIMESTAMP) as PREV_EVENT_STATUS
      FROM t
     ) t
ORDER BY ACTIVITY_ID, EVENT_NUMBER ;

Here is a db<>fiddle.

user_429100 · Answer

You can use moder MATCH_RECOGNIZE:

--main query:
select 
    ACTIVITY_ID, EVENT_TIMESTAMP, EVENT_STATUS
   ,EVENT_NUMBER
   ,EVENT_STATUS_GROUP
   ,CLS
from (select t.*
            ,row_number()over(partition by ACTIVITY_ID order by EVENT_TIMESTAMP) EVENT_NUMBER 
      from your_tab t
      )
match_recognize(
    partition by ACTIVITY_ID
    order by EVENT_TIMESTAMP
    measures
         MATCH_NUMBER() AS EVENT_STATUS_GROUP,
         case when classifier()='B' then 'DUP' end as cls
    all rows per match
    pattern(A B*)
    define
         b AS b.EVENT_STATUS =PREV(b.EVENT_STATUS)
);

Result:

ACTIVITY_ID   EVENT_TIMESTAMP     EVENT_STATUS  EVENT_NUMBER EVENT_STATUS_GROUP CLS
------------- ------------------- ------------- ------------ ------------------ ---
A001          2020-01-01 09:00:00 STATUS A                 1                  1
A001          2020-01-01 10:10:00 STATUS B                 2                  2
A001          2020-01-01 11:20:00 STATUS C                 3                  3
A001          2020-01-01 12:30:00 STATUS C                 4                  3 DUP
A001          2020-01-01 13:10:00 STATUS D                 5                  4
A002          2020-01-01 13:40:00 STATUS F                 1                  1
A002          2020-01-01 17:50:00 STATUS F                 2                  1 DUP
A002          2020-01-01 17:53:00 STATUS G                 3                  2

8 rows selected.

Full example (I've added one row to your example):

-- your sample data:
with your_tab(ACTIVITY_ID, EVENT_TIMESTAMP, EVENT_STATUS) as (
   select 'A001', to_date('01/01/2020 09:00:00','dd/mm/yyyy hh24:mi:ss'),'STATUS A' from dual union all
   select 'A001', to_date('01/01/2020 10:10:00','dd/mm/yyyy hh24:mi:ss'),'STATUS B' from dual union all
   select 'A001', to_date('01/01/2020 11:20:00','dd/mm/yyyy hh24:mi:ss'),'STATUS C' from dual union all
   select 'A001', to_date('01/01/2020 12:30:00','dd/mm/yyyy hh24:mi:ss'),'STATUS C' from dual union all
   select 'A001', to_date('01/01/2020 13:10:00','dd/mm/yyyy hh24:mi:ss'),'STATUS D' from dual union all
   select 'A002', to_date('01/01/2020 13:40:00','dd/mm/yyyy hh24:mi:ss'),'STATUS F' from dual union all
   select 'A002', to_date('01/01/2020 17:50:00','dd/mm/yyyy hh24:mi:ss'),'STATUS F' from dual union all
   select 'A002', to_date('01/01/2020 17:53:00','dd/mm/yyyy hh24:mi:ss'),'STATUS G' from dual
)
--main query:
select 
    ACTIVITY_ID, EVENT_TIMESTAMP, EVENT_STATUS
   ,EVENT_NUMBER
   ,EVENT_STATUS_GROUP
   ,CLS
from (select t.*
            ,row_number()over(partition by ACTIVITY_ID order by EVENT_TIMESTAMP) EVENT_NUMBER 
      from your_tab t
      )
match_recognize(
    partition by ACTIVITY_ID
    order by EVENT_TIMESTAMP
    measures
         MATCH_NUMBER() AS EVENT_STATUS_GROUP,
         case when classifier()='B' then 'DUP' end as cls
    all rows per match
    pattern(A B*)
    define
         b AS b.EVENT_STATUS =PREV(b.EVENT_STATUS)
);

Row Numbering and Sub Grouping

3 Answers