Get the sequence of repeating the same values

Question

This table / data:

WITH T(col1, col2) AS(
    SELECT 1, 'A' FROM DUAL
    UNION ALL
    SELECT 2, 'B' FROM DUAL
    UNION ALL
    SELECT 3, 'B' FROM DUAL
    UNION ALL
    SELECT 4, 'B' FROM DUAL
    UNION ALL
    SELECT 5, 'A' FROM DUAL
    UNION ALL
    SELECT 6, 'B' FROM DUAL
    UNION ALL
    SELECT 7, 'B' FROM DUAL
    UNION ALL
    SELECT 8, 'A' FROM DUAL
)

I need to get the col2number of repeated queue queues, I mean, how many times the same value was repeated in col2when the rows are orderedcol1

The result should be:

col1 | col2 | queue_count
-------------------------
1    |A     |1
2    |B     |3
3    |B     |3
4    |B     |3
5    |A     |1
6    |B     |2
7    |B     |2
8    |A     |1

I tried some analytic functions, but did not achieve the desired result.

Can this be done in pure SQL? without using pl / sql (without loops and every line of step-by-step control, etc.)

Answer 1

This is a problem of scarcity and islands. Here is one way to solve it:

select col1, col2,
       count(*) over (partition by col2, seqnum - seqnum_col2) as queue_count
from (select t.*,
             row_number() over (partition by col2 order by col1) as seqnum_col2,
             row_number() over (order by col1) as seqnum
      from t
     ) t;

, . , , , , "", , col2.

Answer 2

, !

Oracle ( , ); , , Oracle 12 , , MATCH_RECOGNIZE.

WITH T ( col1, col2 ) AS (
       SELECT 1, 'A' FROM DUAL UNION ALL
       SELECT 2, 'B' FROM DUAL UNION ALL
       SELECT 3, 'B' FROM DUAL UNION ALL
       SELECT 4, 'B' FROM DUAL UNION ALL
       SELECT 5, 'A' FROM DUAL UNION ALL
       SELECT 6, 'B' FROM DUAL UNION ALL
       SELECT 7, 'B' FROM DUAL UNION ALL
       SELECT 8, 'A' FROM DUAL
     )
select col1, col2, queue_count
from   t
match_recognize (
  order by col1
  measures final count(*) as queue_count
  all rows per match
  pattern ( A+ | B+ )
  define  A as A.col2 = 'A',
          B as B.col2 = 'B'
)
;

 COL1 COL2  QUEUE_COUNT
----- ---- ------------
    1 A               1
    2 B               3
    3 B               3
    4 B               3
    5 A               1
    6 B               2
    7 B               2
    8 A               1

 8 rows selected