Oracle層次查詢和分析函式在號段選取中的應用(轉)

Oracle層次查詢和分析函式在號段選取中的應用[@more@]摘要
一組連續的數，去掉中間一些數，如何求出剩下的數的區間（即號段）？知道號段的起止，如何求出該號段內所有的數？知道一個大的號段範圍和已經取過的號段，如何求出可用的號段？利用Oracle提供的強大的查詢功能以及分析函式，我們可以很輕鬆的解決上述問題。

關鍵詞：
號段選取、連續數、斷點、層次查詢、分析函式、connect by、rownum、level、lead、lag

1. 問題的提出
在實際工作中，我們常常會碰到號段選取的問題，例如：
 一組連續的數，去掉中間一些數，要求出剩下的數的區間（即號段）
例如：一串數字為1,2,3,4,7,9,10，則號段為1-4,7-7,9-10
 知道號段的起止，要求出該號段內所有的數
例如：號段為1-3,15-15，則號段內所有的數為1,2,3,15
 一組數，中間可能有斷點，要求出缺失的數
例如：一串數字為1,2,3,4,7,9,10，則缺失的數為5,6,8
 已知大號段範圍及已用號段範圍，求可用號段範圍
例如：大號段範圍0-999,已用號段範圍0-200,399-599，則可用號段範圍為201-398,600-999
2. 基礎知識
先做下熱身運動，回顧一下層次查詢和lead／lag函式的運用。
2.1 偽列rownum和level
偽列就是並非在表中真正存在的列。已有很多資料介紹rownum和level這兩個偽列。這裡只想強調一點，偽列是隻針對結果集的。
2.2 利用層次查詢構造連續的數
 產生5～8這4個連續的數

select * from (select rownum+4 from dual connect by rownum<5);
select * from (select level+4 from dual connect by level<5);
........
 以8月為界，例如2005年8月1日，之前的在校學生入學年份為2001～2004，之後的為2002～2005。求當前日期下的在校學生入學年份：

select * from (select to_char(add_months(sysdate, 4), 'yyyy') - rownum from dual connect by rownum<5);
........
2.3 用分析函式Lead和Lag獲得相鄰行的欄位值

select rn, lag(rn)over(order by rn) previos, lead(rn)over(order by rn) next
from (select rownum+4 rn from dual connect by rownum<5);

RN PREVIOS NEXT
---------- ---------- ----------
5 6
6 5 7
7 6 8
8 7
........
簡單的說，在這裡，Lag是獲得前一行的內容，而Lead是獲得後一行的內容。

select rn, lag(rn,2,-1)over(order by rn) previos, lead(rn,2,-1) over(order by rn) next
from (select rownum+4 rn from dual connect by rownum<5);

RN PREVIOS NEXT
---------- ---------- ----------
5 -1 7
6 -1 8
7 5 -1
8 6 -1
........
這裡，透過指定offset引數來獲得兩行前的內容和兩行後的內容，如果offset超出範圍並且未設定預設值-1，那麼系統會自動將其值設為NULL。
3. 問題的解決
有了基礎知識的積累，我們就可以解決前面提到的問題。
3.1 已知號碼求號段
3.1.1 題例
我有一個表結構，
fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125

(第二個欄位內可能是連續的資料，可能存在斷點。)

怎樣能查詢出來這樣的結果,查詢出連續的記錄來。
就像下面的這樣？
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125

3.1.2 解答
思路：利用lag取得前一行的kshm，然後和本行的kshm想比，如果差值為1，說明這一行和上一行是連續的。由於首尾的特殊性，故而需要先用max和min來獲得首尾點。

select fphm, nvl(lag(e)over(partition by fphm order by s),minn) ST, nvl(S,maxn) EN from
(select fphm, lag(kshm,1) over(partition by fphm order by kshm) S, kshm E,
min(kshm)over(partition by fphm) minn, max(kshm) over(partition by fphm) maxn from t)
where nvl(E-S-1,1)<>0;

FPHM ST EN
---------- ---------- ----------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009

........
3.2 根據號段求出包含的數
3.2.1 題例
有表及測試資料如下：
CREATE TABLE T20
(
ID NUMBER(2),
S NUMBER(5),
E NUMBER(5)
);

INSERT INTO T20 ( ID, S, E ) VALUES ( 1, 10, 11);
INSERT INTO T20 ( ID, S, E ) VALUES ( 2, 1, 5);
INSERT INTO T20 ( ID, S, E ) VALUES ( 3, 88, 92);
COMMIT;

S為號段起點，E為號段終點，求出起點和終點之間的數（包括起點和終點）
3.2.2 解答
很明顯，這需要構造序列來解決問題

select a.id, a.s, a.e,b.dis, a.S+b.dis-1 h from
t20 a,
(select rownum dis from
(select max(e-s)+1 gap from t20)
connect by rownum<=gap) b
where a.e>=a.s+b.dis-1
order by a.id, 4

執行結果：
ID S E DIS H
---------- ---------- ---------- ---------- ----------
1 10 11 1 10
1 10 11 2 11
2 1 5 1 1
2 1 5 2 2
2 1 5 3 3
2 1 5 4 4
2 1 5 5 5
3 88 92 1 88
3 88 92 2 89
3 88 92 3 90
3 88 92 4 91
3 88 92 5 92

........
我們再看下面這種做法：

select a.id, a.s, a.e,rownum, a.S+rownum-1 h from
t20 a ,
(select id, e-s+1 gap from t20 where id=2) b
where a.id=b.id
connect by rownum<=gap


ID S E ROWNUM H
---------- ---------- ---------- ---------- ----------
2 1 5 1 1
2 1 5 2 2
2 1 5 3 3
2 1 5 4 4
2 1 5 5 5

........
嗯，得到的結果也是正確的，若我們把粗斜體字部分去掉後，看看結果是什麼樣：

ID S E ROWNUM H
---------- ---------- ---------- ---------- ----------
1 10 11 1 10
1 10 11 2 11
2 1 5 3 3
2 1 5 4 4
2 1 5 5 5
2 1 5 6 6
3 88 92 7 94
........
這樣的結果，顯然不是我們需要的，更何況，這是錯誤的。由此更能深入理解，偽列是隻針對結果集的。
3.3 求缺失的號
3.3.1 題例
table T，列：serial_no
我想能夠查詢一下serial_no這個欄位的不連續的值。
例如：
serial_no
1
2
3
4
6
8
9
10
我想一個sql語句查出來缺失的號碼，
顯示結果為：
5
7
3.3.2 解答
思路：找出數B和它前面的數A進行比較（數按從大到小進行排序），如果B-A=1，則說明是連續的，中間沒有斷點。

select distinct s+level-1 rlt from (select lag(serial_no,1) over(order by serial_no)+1 S,
serial_no-1 E from t) where E-S<>0 connect by level<=e-s
........
3.4 求尚未使用的號段
3.4.1 題例
表A結構:
bill_type_id varchar2(1),
bill_start number,
bill_end number,
office_level varchar2(4)
資料如下:
A 0 999 1
A 0 199 2
A 300 499 2
A 700 799 2
sql目的是取出包含在level1級別裡的,還沒有錄入level2級別的號段。
3.4.2 解答
這個好像是3.1和3.3這兩個問題的逆問題
建立表及測試資料：
CREATE TABLE T8
(
A NUMBER(4),
B NUMBER(4),
C NUMBER(4),
Q VARCHAR2(1 BYTE)
);

Insert into T8(A, B, C, Q)Values(555, 666, 2, 'A');
Insert into T8(A, B, C, Q)Values(100, 199, 2, 'A');
Insert into T8(A, B, C, Q)Values(0, 999, 1, 'A');
Insert into T8(A, B, C, Q)Values(300, 499, 2, 'A');
COMMIT;

思路：將大號段的邊界與小號段的邊界相比，從大號段中將小號段“挖”掉，這樣剩下的就是可用號段了。

select S,E from
(
SELECT NVL2(LAG(A)OVER(PARTITION BY Q ORDER BY A), B+1, MIN(A)OVER(PARTITION BY Q)) S,
NVL(LEAD(A)OVER(PARTITION BY Q ORDER BY A)-1, MAX(B)OVER(PARTITION BY Q)) E
from t8 START WITH C=1 CONNECT BY C-1 = PRIOR C AND Q= PRIOR Q
)
where s<=e
執行結果：
S E
---------- ----------
0 99
200 299
500 554
667 999
........