ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 号段问题研究

号段问题研究

原创 Linux操作系统 作者:冷月逐浪 时间:2011-08-28 19:52:59 0 删除 编辑
--号段问题研究:
一、相关知识点简介:
1、利用层次查询构造连续的数
SELECT * FROM (SELECT ROWNUM+4 FROM DUAL CONNECT BY ROWNUM<5);
SELECT * FROM (SELECT LEVEL+4 FROM DUAL CONNECT BY LEVEL<5);
2、用LEAD和LAG获得相邻行的字段值
二:号段:
--应用点:检查凭证编号等编号异常情况等
3、求连接的号段区间:
--原始数据情况:
VPD_COMPID,TMONTH,CODEID,SQBH
2601        01     4       1
2601        01     4       2
2601        01     4       3
2601        01     4       5
--加工数据情况:查出连续的记录,也看出断点的地方
VPD_COMPID,TMONTH,CODEID,S_SQBH,E_SQBH   --S_sqbh为连续区间的起始编号,e_sqbh为连续区间的结束编号
2601        01     4       1      3
2601        01     4       5      5
解答:
CALL VPD_PKG.SET_CONTEXT_COMPID('-1');
SELECT *FROM XTBILL2011;
--求已完成凭证的申请编号连续与断点的情况:
SELECT VPD_COMPID,TMONTH,CODEID,--PRE_SQBH,SQBH,MIN_SQBH,MAX_SQBH,
  NVL(LAG(SQBH) OVER(PARTITION BY VPD_COMPID,TMONTH,CODEID ORDER BY PRE_SQBH),MIN_SQBH) S_SQBH,
  NVl(PRE_SQBH,MAX_SQBH) E_SQBH
 FROM(
SELECT VPD_COMPID,TO_CHAR(TTIME,'MM') TMONTH,CODEID,
  LAG(SQBH,1) OVER(PARTITION BY VPD_COMPID,TO_CHAR(TTIME,'MM'),CODEID ORDER BY SQBH) PRE_SQBH,
  SQBH,
  MIN(SQBH) OVER(PARTITION BY VPD_COMPID,TO_CHAR(TTIME,'MM'),CODEID) MIN_SQBH,
  MAX(SQBH) OVER(PARTITION BY VPD_COMPID,TO_CHAR(TTIME,'MM'),CODEID) MAX_SQBH
 FROM XTBILL2011 WHERE BZWC=1 --ORDER BY VPD_COMPID,TMONTH,CODEID,PRE_SQBH
  )  --ORDER BY VPD_COMPID,TMONTH,CODEID,PRE_SQBH
WHERE NVL(SQBH-PRE_SQBH-1,1)<>0;
4、若号段类型不是数字,是字母或日期类型:
1)
CREATE TABLE t(KSHM VARCHAR2(10));
INSERT INTO t(KSHM) VALUES('A');
INSERT INTO t(KSHM) VALUES('B');
INSERT INTO t(KSHM) VALUES('C');
INSERT INTO t(KSHM) VALUES('F');
INSERT INTO t(KSHM) VALUES('I');
INSERT INTO t(KSHM) VALUES('J');
INSERT INTO t(KSHM) VALUES('K');
INSERT INTO t(KSHM) VALUES('U');
INSERT INTO t(KSHM) VALUES('Y');
INSERT INTO t(KSHM) VALUES('Z');
INSERT INTO t(KSHM) VALUES('H');
INSERT INTO t(KSHM) VALUES('G');
COMMIT;
WITH V AS (SELECT KSHM,ASCII(KSHM) KV FROM t)
  SELECT NVL(LAG(e) OVER(ORDER BY s),MINN) ST,NVL(s,MAXn) EN
    FROM (SELECT LAG(KSHM,1) OVER(ORDER BY KV) s,
                 KSHM e,LAG(KV,1) OVER(ORDER BY KV) SV,
                 KV EV,MIN(KSHM) OVER() MINN,
                 MAX(KSHM) OVER() MAXN
      FROM V
  )
 WHERE NVL(EV-SV-1,1)<>0;
2)
CREATE TABLE T_1(TTIME DATE);
INSERT INTO T_1(TTIME) VALUES(SYSDATE-10);
INSERT INTO T_1(TTIME) VALUES(SYSDATE-9);
INSERT INTO T_1(TTIME) VALUES(SYSDATE-8);
INSERT INTO T_1(TTIME) VALUES(SYSDATE-6);
INSERT INTO T_1(TTIME) VALUES(SYSDATE-5);
INSERT INTO T_1(TTIME) VALUES(SYSDATE-3);
INSERT INTO T_1(TTIME) VALUES(SYSDATE-1);
COMMIT;
--时间间隔按天处理:
WITH V AS (SELECT TTIME,TTIME KV FROM T_1)
  SELECT NVL(LAG(e) OVER(ORDER BY s),MINN) ST,NVL(s,MAXn) EN
    FROM (SELECT LAG(TTIME,1) OVER(ORDER BY KV) s,
                 TTIME e,LAG(KV,1) OVER(ORDER BY KV) SV,
                 KV EV,MIN(TTIME) OVER() MINN,
                 MAX(TTIME) OVER() MAXN
      FROM V
  )
 WHERE NVL(TRUNC(EV,'DD')-TRUNC(SV,'DD')-1,1)<>0;
5、常规函数解法:
CREATE TABLE T_2(FPHM NUMBER(10),KSHM NUMBER(10));
INSERT INTO T_2(FPHM,KSHM) VALUES(2014,00000001);
INSERT INTO T_2(FPHM,KSHM) VALUES(2014,00000002);
INSERT INTO T_2(FPHM,KSHM) VALUES(2014,00000003);
INSERT INTO T_2(FPHM,KSHM) VALUES(2014,00000004);
INSERT INTO T_2(FPHM,KSHM) VALUES(2014,00000005);
INSERT INTO T_2(FPHM,KSHM) VALUES(2014,00000007);
INSERT INTO T_2(FPHM,KSHM) VALUES(2014,00000008);
INSERT INTO T_2(FPHM,KSHM) VALUES(2014,00000009);
INSERT INTO T_2(FPHM,KSHM) VALUES(2013,00000120);
INSERT INTO T_2(FPHM,KSHM) VALUES(2013,00000121);
INSERT INTO T_2(FPHM,KSHM) VALUES(2013,00000122);
INSERT INTO T_2(FPHM,KSHM) VALUES(2013,00000124);
INSERT INTO T_2(FPHM,KSHM) VALUES(2013,00000125);
COMMIT;
--利用rownum辅助解决:
1)
SELECT B.FPHM,MIN(B.KSHM),MAX(B.KSHM)
  FROM (
    SELECT a.*,TO_NUMBER(A.KSHM-ROWNUM) CC
      FROM (
        SELECT * FROM T_2 ORDER BY FPHM,KSHM
          ) A
  ) B
 GROUP BY B.FPHM,B.CC;
2)
SELECT MAX(FPHM),MIN(KSHM),MAX(KSHM)
  FROM T_2 
  GROUP BY FPHM||(KSHM-ROWNUM);
3)
SELECT * FROM T;
SELECT KSHM,ASCII(KSHM) FROM T ORDER BY ASCII(KSHM);
SELECT MAX(KSHM),MIN(KSHM),KSHM_ASC-ROWNUM FROM (   --注意rownum的应用
SELECT KSHM,ASCII(KSHM) KSHM_ASC
  FROM T ORDER BY ASCII(KSHM)
  )
  GROUP BY KSHM_ASC-ROWNUM;
4)
SELECT * FROM T_1;
--按天来算是否连续:
SELECT MAX(TTIME),MIN(TTIME) FROM (   --注意rownum的应用
SELECT TTIME
  FROM T_1 ORDER BY TTIME
  )
  GROUP BY TRUNC(TTIME,'DD')-ROWNUM;
6、根据号段求出包含的数:
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;
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处理的结果:
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 --id=2的情况
  WHERE A.ID=B.ID
  CONNECT BY ROWNUM<=GAP;
SELECT CHR(65) FROM DUAL;
SELECT ASCII('A') FROM DUAL;
7、求缺失的号:
CREATE TABLE T21(SERIAL_NO  NUMBER(10));
INSERT INTO T21(SERIAL_NO) VALUES(1);
INSERT INTO T21(SERIAL_NO) VALUES(2);
INSERT INTO T21(SERIAL_NO) VALUES(3);
INSERT INTO T21(SERIAL_NO) VALUES(4);
INSERT INTO T21(SERIAL_NO) VALUES(6);
INSERT INTO T21(SERIAL_NO) VALUES(8);
INSERT INTO T21(SERIAL_NO) VALUES(10);
COMMIT;
--要求,查出缺失的号码:
1)
SELECT /*DISTINCT*/ s+LEVEL-1 RLT ,LEVEL
  FROM (SELECT
               LAG(SERIAL_NO,1) OVER(ORDER BY SERIAL_NO)+1 s,
               LAG(SERIAL_NO,1) OVER(ORDER BY SERIAL_NO) S1,SERIAL_NO,
    SERIAL_NO-1 E FROM T21 
     )
  START WITH SERIAL_NO-S1>1  
  CONNECT BY LEVEL<=SERIAL_NO-S1-1 
  ORDER BY 1;
2)
INSERT INTO T21(SERIAL_NO) VALUES(50);
COMMIT;
SELECT * FROM T21 ORDER BY 1;
--start with
SELECT S+LEVEL-1 RLT
  FROM (SELECT LAG(SERIAL_NO,1) OVER(ORDER BY SERIAL_NO)+1 S,
               SERIAL_NO-1 E FROM T21
        )
  START WITH E-S>=0
  CONNECT BY LEVEL<=E-S+1;
--where
SELECT s+LEVEL-1 RLT
  FROM (SELECT LAG(SERIAL_NO,1) OVER(ORDER BY SERIAL_NO)+1 s,
               SERIAL_NO-1 E FROM T21
        )
  WHERE E-s>=0
  CONNECT BY LEVEL<=e-s+1;
3)
--上面的方法,均会出现重复的记录,且性能极低,改进如下:
WITH TT AS (
  --先构造出不连续的区间:
  SELECT s,E
    FROM (SELECT SERIAL_NO,SERIAL_NO-1 E,
                 LAG(SERIAL_NO,1) OVER(ORDER BY SERIAL_NO)+1 S
            FROM T21
          )
   WHERE e-s>=0
)
  SELECT A.S+B.DIS-1 H
    FROM TT A,
      (SELECT ROWNUM DIS FROM
        (SELECT MAX(e-s+1) GAP FROM TT)
       CONNECT BY ROWNUM<=GAP) B
    WHERE A.S+B.DIS-1<=A.E
    ORDER BY 1;
4)如果是第一个数就缺失的情况:
--有的应用不仅仅只考虑到连续的情况,也要求第一个是从1或是某一固定值。
--如此需要保证第一个数不能丢失。
解决方案,需要保证第一个数在源表里:
可构造源表:
--如第一个数字为-1
SELECT * FROM T21
UNION
SELECT -1 FROM DUAL;
WITH TT AS (
  --先构造出不连续的区间:
  SELECT s,E
    FROM (SELECT SERIAL_NO,SERIAL_NO-1 E,
                 LAG(SERIAL_NO,1) OVER(ORDER BY SERIAL_NO)+1 S
            FROM (
              SELECT * FROM T21
              UNION
              SELECT -1 FROM DUAL
            )
          )
   WHERE e-s>=0
)
  SELECT A.S+B.DIS-1 H
    FROM TT A,
      (SELECT ROWNUM DIS FROM
        (SELECT MAX(e-s+1) GAP FROM TT)
       CONNECT BY ROWNUM<=GAP) B
    WHERE A.S+B.DIS-1<=A.E
    ORDER BY 1;
5)同理,如果要保证最后一个数也不能缺失,即可构造源表:
--若最后一数为:100
SELECT * FROM T21
UNION
SELECT 100 FROM DUAL;
8、求尚未使用的号段:
1)
CREATE TABLE T8(
  A  NUMBER(4),
  B  NUMBER(4),
  C  NUMBER(4),
  Q  VARCHAR2(1 BYTE)
 );
INSERT INTO T8(A,B,C,Q) VALUES(0,999,1,'A');
INSERT INTO T8(A,B,C,Q) VALUES(100,199,2,'A');
INSERT INTO T8(A,B,C,Q) VALUES(300,499,2,'A');
INSERT INTO T8(A,B,C,Q) VALUES(555,666,2,'A');
COMMIT;
SELECT * FROM T8;
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,则是一个容错处理手段,
--可将因出现重叠号段而导致出现的不正确的结果过滤掉:
INSERT INTO T8(A,B,C,Q) VALUES(280,340,2,'A');
COMMIT;
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;
--会出来一记录:341,299,故需加上过滤条件:where s<=e;
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 --AND a>=prIor A AND b<=PRIOR B
  )
WHERE s<=E;
2)如插入一条:990,1099,2,'A'.
INSERT INTO T8(a,b,c,q) VALUES(990,1099,2,'A');
COMMIT;
--超出部分会被过滤掉(过滤条件:where s<=e).
--结果如下:
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 --AND a>=prIor A AND b<=PRIOR B
  )
WHERE s<=E;
--查询出号段表号段记录不在总号段范围内的情况:
SELECT A.* FROM T8 A,T8 B
  WHERE A.C<>1 AND B.C=1
     AND (A.AB.B);
3)号段出现交叉的情况,同样会过滤:
--查询出号段表的交叉情况:
SELECT * FROM (
SELECT A,b,c,q,LAG(B,1) OVER(PARTITION BY Q ORDER BY a) PRE_B
  FROM T8
  WHERE C=2
 )
  WHERE  a-PRE_B<=0;
SELECT * FROM T8;
4)注:OFFICE_LEVEL为1的号段只允许存在一个,即总号段范围只能有一个:
-----------------------------------------------------------------------------

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20709695/viewspace-706057/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 行转列问题研究
下一篇: dump函数应用
请登录后发表评论 登录
全部评论

注册时间:2011-07-10

  • 博文量
    42
  • 访问量
    84836