求尚未使用的号段（读书笔记四）

求尚未使用的号段
建表语句：

CREATE TABLE t22(q VARCHAR2(8),
                 a NUMBER(4),
                 b NUMBER(4),
                 c NUMBER(4));
INSERT INTO t22 VALUES('A',0,999,1);
INSERT INTO t22 VALUES('A',100,199,2);
INSERT INTO t22 VALUES('A',300,499,2);
INSERT INTO t22 VALUES('A',555,666,2);
COMMIT;

取出包含在等级1里的，还没被录入等级2的号段
思路：将大号段的边界与小号段的边界相比，从大号段中将小号段”挖“掉，这样剩下的就是可用号段了

第一步：首先用lead和lag函数来取值，根据结果再思考第二步

SQL> SELECT t.*,
  2         LAG(a) OVER(ORDER BY a) aa,
  3         LAG(b) OVER(ORDER BY a) bb,
  4         LEAD(a) OVER(ORDER BY a) cc,
  5         LEAD(b) OVER(ORDER BY a) dd
  6  FROM t22 t;
 
Q            A     B     C         AA         BB         CC         DD
-------- ----- ----- ----- ---------- ---------- ---------- ----------
A            0   999     1                              100        199
A          100   199     2          0        999        300        499
A          300   499     2        100        199        555        666
A          555   666     2        300        499            

第二步：根据结果可以看到，cc列减去1可得到结果，而cc列的最后一行空行，可以填入max(b)。即

SQL> SELECT t.*,
  2         LAG(a) OVER(ORDER BY a) aa,
  3         LAG(b) OVER(ORDER BY a) bb,
  4         nvl(LEAD(a) OVER(ORDER BY a)-1,MAX(b) OVER(PARTITION BY q)) cc,
  5         LEAD(b) OVER(ORDER BY a) dd
  6  FROM t22 t;
 
Q            A     B     C         AA         BB         CC         DD
-------- ----- ----- ----- ---------- ---------- ---------- ----------
A            0   999     1                               99        199
A          100   199     2          0        999        299        499
A          300   499     2        100        199        554        666
A          555   666     2        300        499        <span style="color:#ff0000;">999</span> 

第三步：再次观察，可以看到b列加1得到一组数。即

SQL> SELECT t.*,
  2         LAG(a) OVER(ORDER BY a) aa,
  3         LAG(b) OVER(ORDER BY a) bb,
  4         b+1,
  5         nvl(LEAD(a) OVER(ORDER BY a)-1,MAX(b) OVER(PARTITION BY q)) cc,
  6         LEAD(b) OVER(ORDER BY a) dd
  7  FROM t22 t;
 
Q            A     B     C         AA         BB        B+1         CC         DD
-------- ----- ----- ----- ---------- ---------- ---------- ---------- ----------
A            0   999     1                             <span style="color:#3366ff;">1000</span>         99        199
A          100   199     2          0        999        200        299        499
A          300   499     2        100        199        500        554        666
A          555   666     2        300        499        667        999 

第四步：明显看到现在b+1和cc越来越接近答案，但是b+1的第一行应该写入0,那么此时可以想到使用AA列来做辅助，即若AA列与B+1列比较，
若AA不为空则返回b+1，否则返回min(a)。其余不相干的列去除，语句如下

SQL> SELECT nvl2(LAG(a) OVER(ORDER BY a),b+1,MIN(a) OVER(PARTITION BY q)) qian,
  2         nvl(LEAD(a) OVER(ORDER BY a)-1,MAX(b) OVER(PARTITION BY q)) hou
  3  FROM t22 t;
 
      QIAN        HOU
---------- ----------
         0         99
       200        299
       500        554
       667        999

这样做似乎结束了，其实没有结束
如

INSERT INTO t22 VALUES('A',280,340,2);
COMMIT;

若用

SQL> SELECT nvl2(LAG(a) OVER(ORDER BY a),b+1,MIN(a) OVER(PARTITION BY q)) qidian,
  2         nvl(LEAD(a) OVER(ORDER BY a)-1,MAX(b) OVER(PARTITION BY q)) hou
  3  FROM t22 t;
 
    QIDIAN        HOU
---------- ----------
         0         99
       200        279
      <span style="color:#ff0000;"> 341        299</span>
       500        554
       667        999

显然不对

此时的解决办法则是在外面套一层，同时加where过滤条件，即qian<=hou。如下

SQL> SELECT t.qian,t.hou
  2  FROM (SELECT nvl2(LAG(a) OVER(ORDER BY a),b+1,MIN(a) OVER(PARTITION BY q)) qian,
  3         nvl(LEAD(a) OVER(ORDER BY a)-1,MAX(b) OVER(PARTITION BY q)) hou
  4  FROM t22) t
  5  WHERE t.qian<=t.hou;
 
      QIAN        HOU
---------- ----------
         0         99
       200        279
       500        554
       667        999

若涉及到分组，则在lag和lead中加入分组即可