Vcno period
Vc001 1
Vc001 2
Vc001 5
Vc001 6
Vc001 7
Vc002 4
Vc004 2
Vc002 6
Vc005 11
Data as above, remove the period of continuous maximum period of time?
The results for the
Vc001 3
Vc002 1
Vc004 1
Vc005 1

CodePudding user response:

Vc001 5
Vc001 6
Vc001 7
This is a 3 times?
The vc002 one is how to calculate?

CodePudding user response:

With t (n, p) as (select 'vc001, 1 from dual union select' vc001, 2 from dual union select 'vc001, 5 from dual union
Select 'vc001, 6 from dual union select' vc001, 7 from dual union select 'vc002, 4 from dual union
Select 'vc004, 2 from dual union select' vc002, 6 from dual union select 'vc005, 11 from dual)

Select v, Max (CNT)
The from (select v, pr, count (*) CNT
The from (select v,
P
P - row_number () over (partition by v order by p) pr
The from t)
Group by v, pr)
Group by v
The order by v

CodePudding user response:

With t as (select 'vc001 t1, 1 p1 from dual union select' vc001, 2 from dual union select 'vc001, 5 from dual union
Select 'vc001, 6 from dual union select' vc001, 7 from dual union select 'vc002, 4 from dual union
Select 'vc004, 2 from dual union select' vc002, 6 from dual union select 'vc005, 11 from dual)
The select TTT. T1, Max (cn) from (select tt. T1, count (*) cn from (select t1, p1 - row_number () over (partition by t1 order by (p1) rn from t) tt
Group by tt. The t1, tt. Rn) TTT group by TTT. T1;