oracle序列的间隔属性,用分析函数取序列的间隔

假如我们有一个序列，是从400100到499999，但是其中有序列中有缺失，我们需要找在序列中有哪些间隔，我们应该怎么去找呢？

我们来看看这个例子：

SQL> select * from test_table order by menuid;

……

MENUID NAME

---------- ----------------------------------------

408090 aa

408091 aa

408092 aa

408093 aa

408094 aa

408095 aa

408096 aa

408097 aa

408098 aa

408099 aa

408200 aa

408201 aa

408202 aa

408203 aa

408204 aa

408205 aa

408206 aa

408207 aa

408208 aa

408209 aa

……

439379 aa

439380 aa

439382 aa

439383 aa

439384 aa

439385 aa

439386 aa

439387 aa

439388 aa

……

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

SQL>select *fromtest_tableorderbymenuid;

……

MENUIDNAME

--------------------------------------------------

408090aa

408091aa

408092aa

408093aa

408094aa

408095aa

408096aa

408097aa

408098aa

408099aa

408200aa

408201aa

408202aa

408203aa

408204aa

408205aa

408206aa

408207aa

408208aa

408209aa

……

439379aa

439380aa

439382aa

439383aa

439384aa

439385aa

439386aa

439387aa

439388aa

……

现有一个表myuser.test_table，其中有一列menuid，这个menuid是用序列insert的，但是由于某些情况，menuid本应该从400100到499999，却发生了一些缺失，如400100到408099，本来下一个应该是408100，现在却变成408200；439380的下一个本应该是439381，现在却变成439382……总之，在menuid中发生很多缺失。我们需要找出其中哪些缺失，以便于重新添加序列：

(1)建临时表400101～499999，这个表中包含完整的从400100到499999的数字：

CREATE TABLE xxx AS

SELECT ROWNUM aa FROM dba_source WHERE ROWNUM<=499999;

CREATE TABLE yyy AS

SELECT ROWNUM aa FROM dba_source WHERE ROWNUM<=400100;

CREATE TABLE zzz AS

SELECT * FROM xxx

MINUS

SELECT * FROM yyy;

DROP TABLE xxx;

DROP TABLE yyy;

1

2

3

4

5

6

7

8

9

10

11

12

13

CREATETABLExxxAS

SELECTROWNUMaaFROMdba_sourceWHEREROWNUM<=499999;

CREATETABLEyyyAS

SELECTROWNUMaaFROMdba_sourceWHEREROWNUM<=400100;

CREATETABLEzzzAS

SELECT *FROMxxx

MINUS

SELECT *FROMyyy;

DROPTABLExxx;

DROPTABLEyyy;

(2)将myuser.test_table表的序列取出

CREATE TABLE kk AS

SELECT menuid FROM myuser.test_table

1

2

CREATETABLEkkAS

SELECTmenuidFROMmyuser.test_table

(3)做参照临时表，该表中有是将完整的序列的数据和test_table中不完整的数据合并在一起：

CREATE TABLE sssk AS

SELECT zzz.aa,kk.menuid FROM zzz,kk

WHERE zzz.aa=kk.menuid(+)

--我们可以先看看这个临时参照表是个什么样子：

select * from sssk where rownum<200;

……

AA MENUID

---------- ----------

400277 400277

400278 400278

400279 400279

400280 400280

400281

400282 400282

400283 400283

400284

400285

400286

400287 400287

……

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

CREATETABLEssskAS

SELECTzzz.aa,kk.menuidFROMzzz,kk

WHEREzzz.aa=kk.menuid(+)

--我们可以先看看这个临时参照表是个什么样子：

select *fromssskwhererownum<200;

……

AAMENUID

--------------------

400277400277

400278400278

400279400279

400280400280

400281

400282400282

400283400283

400284

400285

400286

400287400287

……

我们看到，临时参照表中，如果test_table中的序列缺失，就会体现在该表中，有些缺失1个序列，有些缺失3个序列。

(4)做间隔分布表

CREATE TABLE mygod AS

SELECT * FROM (

SELECT

lag(b.menuid) over(PARTITION BY NULL ORDER BY aa) kka

FROM sssk b )

WHERE kka IS NOT NULL

UNION

SELECT 400100 FROM dual

UNION

SELECT 499999 FROM dual;

1

2

3

4

5

6

7

8

9

10

CREATETABLEmygodAS

SELECT *FROM(

SELECT

lag(b.menuid)over(PARTITIONBYNULLORDERBYaa)kka

FROMssskb)

WHEREkkaISNOTNULL

UNION

SELECT400100FROMdual

UNION

SELECT499999FROMdual;

(5)出结果

SELECT *

FROM (SELECT *

FROM (SELECT kka menuid,

lag(kka) over(ORDER BY kka) last_num,

kka - lag(kka) over(ORDER BY kka) minus_num

FROM mygod

ORDER BY kka)

ORDER BY minus_num DESC NULLS LAST)

WHERE rownum < 6;

--我们取的是序列缺失前五位：

MENUID LAST_NUM MINUS_NUM

---------- ---------- ----------

482000 480999 1001

408200 408099 101

418200 418099 101

438200 438099 101

428200 428099 101

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

SELECT *

FROM(SELECT *

FROM(SELECTkkamenuid,

lag(kka)over(ORDERBYkka)last_num,

kka-lag(kka)over(ORDERBYkka)minus_num

FROMmygod

ORDERBYkka)

ORDERBYminus_numDESCNULLSLAST)

WHERErownum<6;

--我们取的是序列缺失前五位：

MENUIDLAST_NUMMINUS_NUM

------------------------------

4820004809991001

408200408099101

418200418099101

438200438099101

428200428099101

MENUID 表示test_table中的字段MENUID，它的值是一个序列；LAST_NUM是根据大小排列，menuid的上一位；MINUS_NUM表示期间缺失的序列数。我们看到，序列从480999到482000缺失最多，期间少了1001个，这一段的序列缺失最多的部分。

SQL> select * from test_table where menuid>=480990 and menuid<=482009 order by menuid;

MENUID NAME

---------- ----------------------------------------

480990 aa

480991 aa

480992 aa

480993 aa

480994 aa

480995 aa

480996 aa

480997 aa

480998 aa

480999 aa

482000 aa

482002 aa

482003 aa

482004 aa

482005 aa

482006 aa

482007 aa

482008 aa

482009 aa

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

SQL>select *fromtest_tablewheremenuid>=480990andmenuid<=482009orderbymenuid;

MENUIDNAME

--------------------------------------------------

480990aa

480991aa

480992aa

480993aa

480994aa

480995aa

480996aa

480997aa

480998aa

480999aa

482000aa

482002aa

482003aa

482004aa

482005aa

482006aa

482007aa

482008aa

482009aa

(6)最后，我们已经找到了序列的间隔，清理战场：

DROP TABLE zzz;

DROP TABLE kk;

DROP TABLE sssk;

DROP TABLE mygod;

1

2

3

4

DROPTABLEzzz;

DROPTABLEkk;

DROPTABLEsssk;

DROPTABLEmygod;