New column ID order
1812300146 20 2
1812300146 19 2
1812300256 18 2
1812300256 17 2
1812300146 16 1
1812300146 15 1
1812300257 14 1
1812300257 13 1
1812300257 12 1
1812300258 11 2
1812300258 10 2
1812300121 9 2
1812300258 8 1
1812300258 July 1
1812300256 June 1
1812300256 5 1
1812300121 April 1
1812300121 3 1
1812300121 2 1
1812300121 1 1
Determine whether a particular ID is continuous, if is continuous, the corresponding new listed the same numerical, if appear fault, anew the ID, will add + 1, such as 1 ~ 4, 1812300121, as a new 1, 1812300121, to 9 appeared the new listed as 2, so how to judge? Give advice or comments please!
CodePudding user response:
with TAB as (
Select one id, one word from dual union all
Select 1 id, 2 word from dual union all
Select 2 id, 3 word from dual union all
Select two id, four word from dual union all
Select id 1, 5 word from dual union all
Select id 1, 6 word from dual union all
Select 3 id, 7 word from dual union all
Select id 1, 8 word from dual union all
Select 3 id, nine word from dual
)
Tab2 as (
Select a t1. *,
Decode (lag (t1. Id) over (order by t1. The word), t1, id, 0, 1) lg
The from the TAB t1
)
Select a t1. Id,
T1. Word,
The sum (t1), lg) over (partition by t1. The id order by t1. The word) new_col
The from tab2 t1
The order by t1. The word
;