用分析函数取序列的间隔

· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/working-case/to-find-out-lag-sequence/

假如我们有一个序列，是从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
……

现有一个表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;

（2）将myuser.test_table表的序列取出

CREATE TABLE kk AS
SELECT menuid FROM myuser.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
……

我们看到，临时参照表中，如果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;

（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

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<-------------失是最多的，上一个为480999，下一个为482000。
    482002 aa
    482003 aa
    482004 aa
    482005 aa
    482006 aa
    482007 aa
    482008 aa
    482009 aa

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

DROP TABLE zzz;
DROP TABLE kk;
DROP TABLE sssk;
DROP TABLE mygod;

· 【文章发布信息】发表于： 2007-12-31 @ 15:09:51 · ||分类： ..experience, Working case

留条评论