分析函数运用样例-连续值判定

最新推荐文章于 2023-06-29 11:01:30 发布

randeexiong

最新推荐文章于 2023-06-29 11:01:30 发布

阅读量367

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签：分析函数

本文链接：https://blog.youkuaiyun.com/xiongfei0131/article/details/90243072

oracle 专栏收录该内容

18 篇文章

订阅专栏

本文详细介绍了一种使用SQL查询连续数据的方法，并展示了如何找出连续数据的最小值、最大值及连续的个数。通过构造伪列和使用窗口函数，实现了对连续数据的有效识别和统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;