Oracle之分析函数应用- 连续值判断

drop table t purge;
create table t (id1 int,id2 int ,id3 int);
insert into t (id1 ,id2,id3) values (1,45,89);
insert into t (id1 ,id2,id3) values (2,45,89);
insert into t (id1 ,id2,id3) values (3,45,89);
insert into t (id1 ,id2,id3) values (8,45,89);
insert into t (id1 ,id2,id3) values (12,45,89);
insert into t (id1 ,id2,id3) values (36,45,89);
insert into t (id1 ,id2,id3) values (22,45,89);
insert into t (id1 ,id2,id3) values (23,45,89);
insert into t (id1 ,id2,id3) values (89,45,89);
insert into t (id1 ,id2,id3) values (92,45,89);
insert into t (id1 ,id2,id3) values (91,45,89);
insert into t (id1 ,id2,id3) values (90,45,89);
commit;


SQL> select * from t;


       ID1        ID2        ID3
---------- ---------- ----------
         1         45         89
         2         45         89
         3         45         89
         8         45         89
        12         45         89
        36         45         89
        22         45         89
        23         45         89
        89         45         89
        92         45         89
        91         45         89
        90         45         89


--需求1：将连续数据查找出来，要达到如下效果


ID1 ID2 ID3
----------- 
1 45 89
2 45 89
3 45 89
22 45 89
23 45 89
89 45 89
90 45 89
91 45 89
92 45 89


--需求22： 要求查出连续数据，并且要写出最小值和最大值及连续的个数，效果如下
 
1   3   3
22  23  2
89  92  4


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


select t.*,
               lag(id1,1,0) over(order by id1) av, ---构造出伪列av
               lead(id1,1,0) over(order by id1) ev ---构造出伪列ev
          from t;
          
          
      ID1        ID2        ID3         AV         EV
--------- ---------- ---------- ---------- ----------
        1         45         89          0          2
        2         45         89          1          3
        3         45         89          2          8
        8         45         89          3         12
       12         45         89          8         22
       22         45         89         12         23
       23         45         89         22         36
       36         45         89         23         89
       89         45         89         36         90
       90         45         89         89         91
       91         45         89         90         92
       92         45         89         91          0
        
select id1, id2, id3
  from (select t.*,
               lag(id1,1,0)  over(order by id1)  av,
               lead(id1,1,0) over(order by id1) ev
          from t) a
 where id1 - 1 = av
    or id1 + 1=  ev;
         
        
--思考中间环节（构造）


SELECT id1,
       id2,
       id3,
       ROW_NUMBER() OVER(ORDER BY id1) - ID1 AS group_id                 
FROM t                 ;


       ID1        ID2        ID3   GROUP_ID
---------- ---------- ---------- ----------
         1         45         89          0
         2         45         89          0
         3         45         89          0
         8         45         89         -4
        12         45         89         -7
        22         45         89        -16
        23         45         89        -16
        36         45         89        -28
        89         45         89        -80
        90         45         89        -80
        91         45         89        -80
        92         45         89        -80

--在构造的中间环节的基础上，实现了需求1


SELECT id1, id2, id3
  FROM (        
        SELECT id1, id2, id3, COUNT(*) OVER(PARTITION BY group_id) CNT        
          FROM (                
                 SELECT id1,
                         id2,
                         id3,
                         ROW_NUMBER() OVER(ORDER BY id1) - ID1 AS group_id                 
                   FROM t                 
                 )        
        )
 WHERE CNT > 1
 ORDER BY id1;

--同样在构造的中间环节的基础上，实现了需求2

SELECT MIN(id1), MAX(id1), COUNT(*)
  FROM (SELECT id1,
               id2,
               id3,
               ROW_NUMBER() OVER(ORDER BY id1) - ID1 AS group_id
          FROM t)
HAVING COUNT(*) > 1
 GROUP BY group_id
 ORDER BY 1;