Skip to Main Content
  • Questions
  • How to count pairs in a consecutive number of rows

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Pathryck Vicentin.

Asked: March 15, 2019 - 2:35 pm UTC

Last updated: March 18, 2019 - 2:31 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

I have the following example:

COLUMN
19
20
26
28
29
32
33
34

I need to count the rows based on pairs, 19-20, 28-29, 32-33. I'm having difficulty to check if a pair is already counted or not, any sugestions ? The result should be something like this:

CNT
2
1
2
2
1

and Chris said...

If you were on 12c, pattern matching (match_recognize) is the way to go here:

with rws as (
  select 19 n from dual union all
  select 20 n from dual union all
  select 26 n from dual union all
  select 28 n from dual union all
  select 29 n from dual union all
  select 32 n from dual union all
  select 33 n from dual union all
  select 34 n from dual
)
  select * 
  from rws match_recognize (
    order by n
    measures
      count(*) num#,
      first ( n ) st,
      last ( n ) en
    pattern ( strt cons{0,1} )
    define
      cons as n = prev ( n ) + 1
  )

NUM#   ST   EN   
     2   19   20 
     1   26   26 
     2   28   29 
     2   32   33 
     1   34   34 


As you're not, you'll need something a little more convoluted...

You can use the Tabititosan method to split the rows into groups of consecutive values.

To then further subdivide these into pairs, one approach is:

For each set of consecutive values, calculate a new row number. Then divide this by 2. And take the ceiling of this value.

Add this to the original group to convert them to pairs:

with rws as (
  select 19 n from dual union all
  select 20 n from dual union all
  select 26 n from dual union all
  select 28 n from dual union all
  select 29 n from dual union all
  select 32 n from dual union all
  select 33 n from dual union all
  select 34 n from dual
), grps as (
  select n, 
         n - row_number () 
           over ( order by n ) grp, 
         n - row_number () 
           over ( order by n ) 
           + ceil ( row_number () 
           over ( order by n ) / 2 ) 
           grp2
  from   rws
), pairs as (
  select grp, n, 
         grp + ceil ( row_number () 
           over ( partition by grp order by n ) / 2 
         ) grp_2
  from   grps
)
  select count (*) num#, min ( n ), max ( n )  
  from   pairs
  group  by grp, grp_2
  order  by grp, grp_2;

NUM#   MIN(N)   MAX(N)   
     2       19       20 
     1       26       26 
     2       28       29 
     2       32       33 
     1       34       34 


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.