你可以用 lag() 和一个累计总和来计算变化的次数：

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 ;

这是一把小提琴。

你可以用 DENSE_RANK() 分析函数：

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

演示

您可以使用moder match\u来识别：

--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)
);

结果：

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.

完整示例（我在您的示例中添加了一行）：

-- 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)
);