Oracle判断某一ID是否连续

[weixin_38608720]

有如下数据：
ID 顺序 新增列
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 7 1
1812300256 6 1
1812300256 5 1
1812300121 4 1
1812300121 3 1
1812300121 2 1
1812300121 1 1
判断某一ID是否是连续的，如果是连续的，则对应的新增列为同一数值，若出现断层，从新出现该ID，则将新增列+1，如1~4是1812300121 ，新增列为1，到9又出现1812300121，则新增列为2，这样要如何判断？请指教！

[nayi_224]

with tab as (
select 1 id, 1 ord from dual union all
select 1 id, 2 ord from dual union all
select 2 id, 3 ord from dual union all
select 2 id, 4 ord from dual union all
select 1 id, 5 ord from dual union all
select 1 id, 6 ord from dual union all
select 3 id, 7 ord from dual union all
select 1 id, 8 ord from dual union all
select 3 id, 9 ord from dual 
)
,tab2 as (
select t1.*,
       decode(lag(t1.id) over(order by t1.ord), t1.id, 0, 1) lg
  from tab t1
)
select t1.id,
       t1.ord,
       sum(t1.lg) over(partition by t1.id order by t1.ord) new_col
  from tab2 t1 
 order by t1.ord
;