查看: 46270|回复: 61

[精华] Oracle层次查询和分析函数在号段选取中的应用

[复制链接]
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
发表于 2007-2-5 20:14 | 显示全部楼层 |阅读模式
摘要
一组连续的数,去掉中间一些数,如何求出剩下的数的区间(即号段)?知道号段的起止,如何求出该号段内所有的数?知道一个大的号段范围和已经取过的号段,如何求出可用的号段?利用Oracle提供的强大的查询功能以及分析函数,我们可以很轻松的解决上述问题。

关键词:
号段选取、连续数、断点、层次查询、分析函数、connect by、rownum、level、lead、lag

1.        问题的提出
在实际工作中,我们常常会碰到号段选取的问题,例如:
        一组连续的数,去掉中间一些数,要求出剩下的数的区间(即号段)
例如:一串数字为1,2,3,4,7,9,10,则号段为1-4,7-7,9-10
        知道号段的起止,要求出该号段内所有的数
例如:号段为1-3,15-15,则号段内所有的数为1,2,3,15
        一组数,中间可能有断点,要求出缺失的数
例如:一串数字为1,2,3,4,7,9,10,则缺失的数为5,6,8
        已知大号段范围及已用号段范围,求可用号段范围
例如:大号段范围0-999,已用号段范围0-200,399-599,则可用号段范围为201-398,600-999
2.        基础知识
先做下热身运动,回顾一下层次查询和lead/lag函数的运用。
2.1        伪列rownum和level
伪列就是并非在表中真正存在的列。已有很多资料介绍rownum和level这两个伪列。这里只想强调一点,伪列是只针对结果集的。
2.2        利用层次查询构造连续的数
        产生5~8这4个连续的数
[php]
select * from (select rownum+4 from dual connect by rownum<5);
select * from (select level+4 from dual connect by level<5);
........
[/php]
        以8月为界,例如2005年8月1日,之前的在校学生入学年份为2001~2004,之后的为2002~2005。求当前日期下的在校学生入学年份:
[php]
select * from (select to_char(add_months(sysdate, 4), 'yyyy') - rownum from dual connect by rownum<5);
........
[/php]
2.3        用分析函数Lead和Lag获得相邻行的字段值
[php]
select rn, lag(rn)over(order by rn) previos, lead(rn)over(order by rn) next
from (select rownum+4 rn from dual connect by rownum<5);

        RN    PREVIOS       NEXT
---------- ---------- ----------
         5                     6
         6          5          7
         7          6          8
         8          7
........
[/php]
简单的说,在这里,Lag是获得前一行的内容,而Lead是获得后一行的内容。
[php]
select rn, lag(rn,2,-1)over(order by rn) previos, lead(rn,2,-1) over(order by rn) next
from (select rownum+4 rn from dual connect by rownum<5);

        RN    PREVIOS       NEXT
---------- ---------- ----------
         5         -1          7
         6         -1          8
         7          5         -1
         8          6         -1
........
[/php]
这里,通过指定offset参数来获得两行前的内容和两行后的内容,如果offset超出范围并且未设定默认值-1,那么系统会自动将其值设为NULL。
3.        问题的解决
有了基础知识的积累,我们就可以解决前面提到的问题。
3.1        已知号码求号段
3.1.1        题例
我有一个表结构,
fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125

(第二个字段内可能是连续的数据,可能存在断点。)

怎样能查询出来这样的结果,查询出连续的记录来。
就像下面的这样?
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125

3.1.2        解答
思路:利用lag取得前一行的kshm,然后和本行的kshm想比,如果差值为1,说明这一行和上一行是连续的。由于首尾的特殊性,故而需要先用max和min来获得首尾点。
[php]
select fphm, nvl(lag(e)over(partition by fphm order by s),minn) ST, nvl(S,maxn) EN from
(select fphm, lag(kshm,1) over(partition by fphm order by kshm) S, kshm E,
min(kshm)over(partition by fphm) minn, max(kshm) over(partition by fphm) maxn from t)
where nvl(E-S-1,1)<>0;

FPHM       ST         EN
---------- ---------- ----------
2013       00000120   00000122
2013       00000124   00000125
2014       00000001   00000005
2014       00000007   00000009

........
[/php]
3.2        根据号段求出包含的数
3.2.1        题例
有表及测试数据如下:
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;

S为号段起点,E为号段终点,求出起点和终点之间的数(包括起点和终点)
3.2.2        解答
很明显,这需要构造序列来解决问题
[php]
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          S          E        DIS          H
---------- ---------- ---------- ---------- ----------
         1         10         11          1         10
         1         10         11          2         11
         2          1          5          1          1
         2          1          5          2          2
         2          1          5          3          3
         2          1          5          4          4
         2          1          5          5          5
         3         88         92          1         88
         3         88         92          2         89
         3         88         92          3         90
         3         88         92          4         91
         3         88         92          5         92

........
[/php]
我们再看下面这种做法:

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
where a.id=b.id
connect by rownum<=gap

[php]
        ID          S          E     ROWNUM          H
---------- ---------- ---------- ---------- ----------
         2          1          5          1          1
         2          1          5          2          2
         2          1          5          3          3
         2          1          5          4          4
         2          1          5          5          5

........
[/php]
嗯,得到的结果也是正确的,若我们把粗斜体字部分去掉后,看看结果是什么样:
[php]
        ID          S          E     ROWNUM          H
---------- ---------- ---------- ---------- ----------
         1         10         11          1         10
         1         10         11          2         11
         2          1          5          3          3
         2          1          5          4          4
         2          1          5          5          5
         2          1          5          6          6
         3         88         92          7         94
........
[/php]
这样的结果,显然不是我们需要的,更何况,这是错误的。由此更能深入理解,伪列是只针对结果集的。
3.3        求缺失的号
3.3.1        题例
table T,列:serial_no
我想能够查询一下serial_no这个字段的不连续的值。
例如:
serial_no
1
2
3
4
6
8
9
10
我想一个sql语句查出来缺失的号码,
显示结果为:
5
7
3.3.2        解答
思路:找出数B和它前面的数A进行比较(数按从大到小进行排序),如果B-A=1,则说明是连续的,中间没有断点。
[php]
select distinct s+level-1 rlt from (select lag(serial_no,1) over(order by serial_no)+1 S,
serial_no-1 E from t) where E-S<>0 connect by level<=e-s
........
[/php]
3.4        求尚未使用的号段
3.4.1        题例
表A结构:
bill_type_id varchar2(1),
bill_start number,
bill_end number,
office_level varchar2(4)
数据如下:
A 0 999 1
A 0 199 2
A 300 499 2
A 700 799 2
sql目的是取出包含在level1级别里的,还没有录入level2级别的号段。
3.4.2        解答
这个好像是3.1和3.3这两个问题的逆问题
创建表及测试数据:
CREATE TABLE T8
(
A NUMBER(4),
B NUMBER(4),
C NUMBER(4),
Q VARCHAR2(1 BYTE)
);

Insert into T8(A, B, C, Q)Values(555, 666, 2, 'A');
Insert into T8(A, B, C, Q)Values(100, 199, 2, 'A');
Insert into T8(A, B, C, Q)Values(0, 999, 1, 'A');
Insert into T8(A, B, C, Q)Values(300, 499, 2, 'A');
COMMIT;

思路:将大号段的边界与小号段的边界相比,从大号段中将小号段“挖”掉,这样剩下的就是可用号段了。
[php]
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
---------- ----------
         0         99
       200        299
       500        554
       667        999       
........
[/php]

参考资料
        参考资料
        Oracle 8i SQL Reference
        http://lastwinner.itpub.net
        http://hmxxyy.itpub.net/
        论坛相关讨论帖子
        请教查询语句的写法? http://www.itpub.net/435578.html
        如何sql查询出连续号码段 http://www.itpub.net/354052.html
        请教关于号段选取的sql写法 http://www.itpub.net/480536.html
        知道号段起止,如何选择该号段内的所有号码? http://www.itpub.net/701508.html

作者简介
lastwinner,主要关注于Oracle开发以及Web开发。欢迎访问我在ITPUB的BLOG: http://lastwinner.itpub.net
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2007-2-5 20:18 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
90
生肖徽章:蛇
日期:2006-09-07 17:09:082011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-02-18 11:43:34现任管理团队成员
日期:2011-05-07 01:45:082012新春纪念徽章
日期:2012-01-04 11:50:442012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09
发表于 2007-2-5 21:55 | 显示全部楼层
总结了不少, 看看^_^

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2007-2-5 23:00 | 显示全部楼层
最初由 jackywood 发布
[B]总结了不少, 看看^_^ [/B]


你还用看啊
谢谢支持

使用道具 举报

回复
论坛徽章:
90
生肖徽章:蛇
日期:2006-09-07 17:09:082011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-02-18 11:43:34现任管理团队成员
日期:2011-05-07 01:45:082012新春纪念徽章
日期:2012-01-04 11:50:442012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:09
发表于 2007-2-5 23:22 | 显示全部楼层
最初由 lastwinner 发布
[B]

你还用看啊
谢谢支持 [/B]


老朋友当然要捧场了

使用道具 举报

回复
论坛徽章:
24
生肖徽章:狗
日期:2006-09-07 10:14:43数据库板块每日发贴之星
日期:2008-07-26 01:02:20生肖徽章2007版:兔
日期:2008-10-13 11:10:11奥运会纪念徽章:铁人三项
日期:2008-10-24 13:27:21开发板块每日发贴之星
日期:2008-12-27 01:01:09生肖徽章2007版:马
日期:2009-11-18 10:45:032010新春纪念徽章
日期:2010-03-01 11:21:02ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51ERP板块每日发贴之星
日期:2011-05-18 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2007-2-6 08:44 | 显示全部楼层
哈哈,学习。

我可以在我的blog里加链接指向2位么 ?

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2007-2-6 11:21 | 显示全部楼层
最初由 jackywood 发布
[B]

老朋友当然要捧场了 [/B]



受宠若惊ing……
看看有什么毛病帮我挑挑


ps:楼上的朋友,链接欢迎加

使用道具 举报

回复
招聘 : 数据库管理员
论坛徽章:
38
ITPUB北京九华山庄2008年会纪念徽章
日期:2008-01-21 16:50:24马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14现任管理团队成员
日期:2012-10-18 17:11:21版主4段
日期:2012-05-15 15:24:112012新春纪念徽章
日期:2012-02-13 15:09:232012新春纪念徽章
日期:2012-02-13 15:09:232012新春纪念徽章
日期:2012-02-13 15:09:23
发表于 2007-2-6 14:40 | 显示全部楼层
野花真是强人
而且精力充沛

使用道具 举报

回复
招聘 : 系统分析师
论坛徽章:
483
马上有钱
日期:2014-02-19 11:55:14itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29慢羊羊
日期:2015-02-09 17:04:38沸羊羊
日期:2015-03-04 14:43:432015年新春福章
日期:2015-03-06 11:57:31ITPUB年度最佳版主
日期:2015-03-18 15:48:48
 楼主| 发表于 2007-2-6 15:42 | 显示全部楼层
最初由 Laurence.li 发布
[B]野花真是强人
而且精力充沛 [/B]



整理总结了一下一年多前的一些文章
算是给自己一个交待

使用道具 举报

回复
论坛徽章:
24
生肖徽章:狗
日期:2006-09-07 10:14:43数据库板块每日发贴之星
日期:2008-07-26 01:02:20生肖徽章2007版:兔
日期:2008-10-13 11:10:11奥运会纪念徽章:铁人三项
日期:2008-10-24 13:27:21开发板块每日发贴之星
日期:2008-12-27 01:01:09生肖徽章2007版:马
日期:2009-11-18 10:45:032010新春纪念徽章
日期:2010-03-01 11:21:02ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51ERP板块每日发贴之星
日期:2011-05-18 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15
发表于 2007-2-6 17:04 | 显示全部楼层

Re: Oracle层次查询和分析函数在号段选取中的应用

最初由 lastwinner 发布
[B]        
3.        问题的解决
有了基础知识的积累,我们就可以解决前面提到的问题。
3.1        已知号码求号段
3.1.1        题例
我有一个表结构,
fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125

(第二个字段内可能是连续的数据,可能存在断点。)

怎样能查询出来这样的结果,查询出连续的记录来。
就像下面的这样?
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125

3.1.2        解答
思路:利用lead取得下一行的kshm,然后和本行的kshm想比,如果差值为1,说明这一行和下一行是连续的。由于首尾的特殊性,故而需要先用max和min来获得首尾点。
[php]
select fphm, nvl(lag(e)over(partition by fphm order by s),minn) ST, nvl(S,maxn) EN from
(select fphm, lag(kshm,1) over(partition by fphm order by kshm) S, kshm E,
min(kshm)over(partition by fphm) minn, max(kshm) over(partition by fphm) maxn from t)
where nvl(E-S-1,1)<>0;

FPHM       ST         EN
---------- ---------- ----------
2013       00000120   00000122
2013       00000124   00000125
2014       00000001   00000005
2014       00000007   00000009

........

[/B]


呵呵,发现问题了,你说用lead结果sql中却使用了lag。而且你这样处理我觉得有点复杂了。

create table test_17 ( fphm varchar2(10),kshm varchar2(10));
insert into test_17 values ('2014','00000001');
insert into test_17 values ('2014','00000002');
insert into test_17 values ('2014','00000003');
insert into test_17 values ('2014','00000004');
insert into test_17 values ('2014','00000005');
insert into test_17 values ('2014','00000007');
insert into test_17 values ('2014','00000008');
insert into test_17 values ('2014','00000009');
insert into test_17 values ('2013','00000120');
insert into test_17 values ('2013','00000121');
insert into test_17 values ('2013','00000122');
insert into test_17 values ('2013','00000124');
insert into test_17 values ('2013','00000125');
commit;

SQL> select * from test_17;

FPHM       KSHM
---------- ----------
2014       00000001
2014       00000002
2014       00000003
2014       00000004
2014       00000005
2014       00000007
2014       00000008
2014       00000009
2013       00000120
2013       00000121
2013       00000122
2013       00000124
2013       00000125

13 rows selected

SQL> select fphm,min(kshm) as s,max(kshm) as e from (
  2  select fphm,kshm,kshm - row_number() over (partition by fphm order by kshm) rn from test_17
  3  ) group by fphm,rn;

FPHM       S          E
---------- ---------- ----------
2013       00000120   00000122
2013       00000124   00000125
2014       00000001   00000005
2014       00000007   00000009

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表