OTARIKI OTARIKI - 1 month ago 11
SQL Question

Get queue repeated same values count

Given table/data like this:

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 get
col2
queue repeated counts, I mean, how many times repeated same value in
col2
when rows are ordered by
col1


Result must 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 don't achieve desirable result.

It's possible to do this in "pure" SQL? without using pl/sql (without looping and each row step by step handling, etc.)

Answer

This is a gap and islands problem. 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;

It is a little hard to explain why this works. However, if you run the subquery and stare at the results, you will probably "get" why the difference between the row numbers works to identify adjacent rows with the same value in col2.