According to the grouping biillno after comparison before and after, according to LV order again after calculate a1 increasing continuously, continuous decline, does not reduce the maximum ~ and in the comparison of values into another table,

CodePudding user response:

@ yaiger
@ nayi_224

CodePudding user response:

You can refer to https://bbs.csdn.net/topics/392420616nayi_224's answer, you try to write it

CodePudding user response:

Best post scripts of table structure and test data, for testing

CodePudding user response:

We have to do is add a partition

 
With the TAB as 
(select 1 id, t1. A1, 1 lv from temp01 t1 union all 
Select 1 id, t1. A2, 2 from temp01 t1 union all 
Select 1 id, t1. A3, 3 from temp01 t1 union all 
Select 1 id, t1. A4, 4 from temp01 t1 union all 
Select 1 id, t1. A5, 5 from temp01 t1 union all 
Select 2 id, t1. A2, 2 from temp01 t1 union all 
Select 2 id, t1. A3, 3 from temp01 t1 union all 
Select 2 id, t1. A4, 4 from temp01 t1 union all 
Select 1 id, t1. A6, 6 from temp01 t1) 
Tab2 as (
Select a t1. *, 
Case 
The when lead (t1. A1) over (partition by id order by lv) & gt; A1 and 
NVL (lag (t1) a1) over (partition by id order by lv), a1 + 1) & gt; A1 then 
1 
The when lag (t1. A1) over (partition by id order by lv) & gt;=a1 then 
1 
The else 
0 
End fin 
The from the TAB t1) 
, tab3 as (
Select a t1. *, sum (t1. Fin) over (partition by id order by lv) su from tab2 t1 
), tab4, as (
Select a t1. *, 
Count (1) over (partition by id, su) part_1_cot 
The from tab3 t1 
) 
Select a t1. *, 
Max (t1) part_1_cot) over (partition by id) part_2_cot 
The from tab4, t1 

;