Eason [資料科學//Python學習/資料庫] & [拍片&剪片]

將有連續的整數分組，規則如下:

id group1

1   null
2   null
4   null
5   null
6   null
8   null
9   null
10 null
12 null

create table test_your (id number,group1 number);

insert into test_your values(1,null);

insert into test_your values(2,null);

insert into test_your values(4,null);

insert into test_your values(5,null);

.

.

.

最後呈現結果如下:

id group1

1   1
2   1
4   2
5   2
6   2
8   3
9   3
10 3
12 4

思路:

step1如何判斷表格內的數字是否連續?

數字=前一個數字+1

制定一個規則  用兩個表格呈現以下規則 (透過rownum方法)     省略...看程式碼

1  0    

2  1

4  2

5  4

6  5

.   6

.  .

設計一個輔助表格 來達成

select * from(
select m.*,rownum from test_your m);   --主要表格

select * from(

select n.*,rownum from test_my n);       --輔助表格(內容同主要表格)

這樣的 join方法 可以呈現以上規則    (方便游標一次讀取2個欄位...)

select abc.id,nvl(cba.id,0) id1 from (select id,rn from(
select m.id,rownum rn from test_your m)) abc left outer join (select id,rn1 from(
select n.id,rownum rn1 from test_my n)) cba
on abc.rn=cba.rn1+1 order by abc.id;

   ** 輔助表方法2(透過with table as  方法)

先將 test_your 增加一個rn欄位(將rownum更新進去)

alter table  test_your add (rn number);
update  test_your set rn=(select m.rn from (select mv.id,rownum rn from  test_your) m where m.id=mv.id);

 然後透過with table as ( )   來建立臨時表(輔助表) 

with t as (select * from test_your)
select test_your.id,nvl(t.id,0) from test_your left outer join t on test_your.rn=t.rn+1 order by test_your.id;

這用個方法 就不用多建立一個真實表，透過臨時表就可完成

再把這段語法放進cursor 即可

輔助表格方法3

用lag lead函數(最方便的方法)

select id ,lag(id,1,0) over (order by id) from test_your ;         

select id ,lead(id,1,0) over (order by id) from test_your ;

step2如何遞增組別   設定變數(v_count)初始值為1

store procedure如下:

********************************

create or replace procedure proc2 is
v_n1 char(2);  --儲存ID
v_n2 char(2);  --儲存ID1
v_count number:=1;  --控制組的編號
cursor c_1 is              --游標

select abc.id,nvl(cba.id,0) id1 from (select id,rn from(

select m.id,rownum rn from test_your m)) abc left outer join (select id,rn1 from(

select n.id,rownum rn1 from test_my n)) cba

on abc.rn=cba.rn1+1 order by abc.id;      --join方法

begin

open c_1;
loop
fetch c_1 into v_n1,v_n2;
EXIT when c_1%NOTFOUND;

if (v_n1=v_n2+1) then     --判斷連續是否
update test_your set group1=v_count where id=v_n1;
dbms_output.put_line(v_n1||' '||v_count);
else
v_count:=v_count+1;
update test_your set group1=v_count where id=v_n1;
dbms_output.put_line(v_n1||' '||v_count);
end if;
end loop;
CLOSE c_1;

end;
/

show error;

結果:

set serveroutput on;
exec proc2;

*****************

匿名區塊已完成
1   1
2   1
4   2
5   2
6   2
8   3
9   3
10 3
12 4