Oracle SQL assign consecutive numbers to a subset based on column values

Question

I have the following data on table

ID_1    ID_2    SEQ GROUP
212648  601327  1   
212648  1805    2   
212648  500886  3   
212648  3405    4   
212648  501174  5   
212648  201245  6   
212648  500449  7   
212648  3804    8   
212648  501533  9   
212648  3989    10  
212648  500280  11  START
212648  175     12  BETWEEN
212648  500395  13  END
212648  1817    14  
212648  500945  15  START
212648  183     16  BETWEEN
212648  500543  17  BETWEEN
212648  181     18  BETWEEN
212648  500009  19  END
212648  5576    20  
212648  500960  21  
212648  5562    22  
212648  603659  23  

I would like to add a column that will apply a group name for the rows between 'START" and 'END'. For example:

ID_1    ID_2    SEQ GROUP   GROUP_SEQ
212648  601327  1       
212648  1805    2       
212648  500886  3       
212648  3405    4       
212648  501174  5       
212648  201245  6       
212648  500449  7       
212648  3804    8       
212648  501533  9       
212648  3989    10      
212648  500280  11  START    1
212648  175     12  BETWEEN  1
212648  500395  13  END      1
212648  1817    14      
212648  500945  15  START    2
212648  183     16  BETWEEN  2
212648  500543  17  BETWEEN  2
212648  181     18  BETWEEN  2
212648  500009  19  END      2
212648  5576    20      
212648  500960  21      
212648  5562    22      
212648  603659  23      

I searched in the analytical functions of Oracle (RANK(), FIRST, LAST() etc.) but I could not find a solution. Thanks in advance for any responses.

Answer 1

Off the top, this query got the result. Probably a cleaner way if a bit more time is spent on it.

SELECT id_1, id_2, seq, the_group

      ,CASE WHEN (start_count - end_count) > 0 OR (start_count = end_count AND the_group = 'END')
            THEN start_count
            ELSE NULL
       END AS group_seq

  FROM ( SELECT id_1, id_2, seq, the_group

               ,SUM( CASE WHEN the_group = 'START' THEN 1 ELSE 0 END )
                  OVER( PARTITION BY ID_1 ORDER BY id_1, SEQ ) AS start_count

               ,SUM( CASE WHEN the_group = 'END' THEN 1 ELSE 0 END )
                  OVER( PARTITION BY ID_1 ORDER BY id_1, SEQ ) AS end_count

           FROM myTable )

  ORDER BY id_1, seq