Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  302 随笔 :: 26 文章 :: 82 评论 :: 0 Trackbacks

1、over函数(基本分析函数):

 

==code example 1:==========================================

    date      sale
     1           20
     2           15
     3           14
     4           18
     5           30

 

使用分析函数逐条汇总sale:

select date , sum (sale)over( order by date ) sum from t1;

   date   sale     sum

    1       20        20  ----------1天           
    2       15        35  ----------1天+2天           
    3       14        49  ----------1天+2天+3天           
    4       18        67      ……
    5       30        97      ……

另:使用count、avg、min、max等均与此类同。

 

==code example 2:==========================================

    A   B   C 
    m   a   2                      
    n   a   3                      
    m   a   2                      
    n   b   2                      
    n   b   1                      
    x   b   3                      
    x   b   2                      
    x   b   4                      
    h   b   3

 

使用分析函数分项汇总C:

select a,c, sum (c)over( partition by a) sum from t2 ;

   A   B   C        SUM
   h   b   3        3                        
   m   a   2        4                        
   m   a   2        4                        
   n   a   3        6                        
   n   b   2        6                        
   n   b   1        6                        
   x   b   3        9                        
   x   b   2        9                        
   x   b   4        9
  

 

若无须分项汇总,则使用partition by null

select a,c, sum (c)over( partition by null) sum from t2 ;

   A   B   C        SUM
   h   b   3        22                        
   m   a   2        22                        
   m   a   2        22                        
   n   a   3        22                        
   n   b   2        22                        
   n   b   1        22                        
   x   b   3        22                        
   x   b   2        22                        
   x   b   4        22                        

 

==code example 3:==========================================

    NAME  CLASS  S                          
    fda    1      80                     
    ffd    1      78                     
    dss    1      95                     
    cfe    2      74                     
    gds    2      92                     
    gf     3      99                     
    ddd    3      99                     
    adf    3      45                     
    asdf   3      55                     
    3dd    3      78
          

 

统计每个班的第一名成绩:

select * from                                                                       

( select name , class ,s,rank()over( partition by class order by s desc ) mm

from t1)                                                                           

where mm= 1

    NAME   CLASS    S        MM                                                                                        
    dss      1       95        1    
    gds      2      92        1                      
    gf       3      99        1                      
    ddd      3      99        1
 

 

==code example 4:=================★★★★====================

    ID     NUM
    1       0
    2       2
    3       2
    4       0
    5       0
    6       2    
    7       2
    8       0
    9       1
    10      1

 

使用开窗函数进行汇总的研究:

selectid,sum(num)over(orderbyidrangebetween3 preceding and3 following) from t1;

    ID     SUM(NUM)OVER(ORDERBYIDRANGEBET
    1             4
    2             4
    3             6
    4             8
    5             8
    6             7
    7             6
    8             6
    9             6
    10            4

说明:range between代表了该id前后数值区间(注意是数值,不是行数)的数字进行汇总。

 

若要通过行开窗,则将range改为rows

selectid,sum(num)over(orderbyid rowsbetween3 preceding and3 following) from t1;

此例中的运行结果与上面相同(因为行号和数值相同)

若需要某一边不设限,则使用关键字unbounded

selectid,sum(num)over(orderbyidrowsbetweenunbounded  preceding and2  following) from t1;

 

 

 

2、排序函数的研究:

 
一般的3中排序函数,注意排序的区别:

select col,value,rank() over([partitionby col] orderbyvalue) from tmp1;

--------------排名相同则重复,之后跳过

select col,value,dense_rank() over([partitionby col] orderbyvalue) from tmp1;

--------------排名相同则重复,之后不跳过

select col,value,row_number() over([partitionby col] orderbyvalue) from tmp1;

--------------排名相同也不重复

select col,value,cume_dist() over([partitionby col] orderbyvalue) from tmp1;

----------------0-1之间比率,最大1,rank形式

select col,value,percent_rank() over([partitionby col] orderbyvalue) from tmp1;

----------------0-1之间比率,不到1,decse_rank形式

 

 

另一种有趣的排序用法,确定某数在序列中的排位:

select col,rank(121) within group(orderbyvaluedesc) from tmp1 groupby col

--------------121在各个col中的value中的排位(排名相同则重复,之后跳过)

select col,dense_rank(121) within group(orderbyvaluedesc) from tmp1 groupby col;

--------------121在各个col中的value中的排位(排名相同则重复,之后不跳过)

select col,cume_dist(121) within group(orderbyvaluedesc) from tmp1 groupby col;

--------------121在各个col中的value中的位置占比(结果均在0-1之间,用rank的规则)

select col,percent_rank(121) within group(orderbyvaluedesc) from tmp1 groupby col;

--------------121在各个col中的value中的位置占比(结果均在0-1之间,用decse_rank的规则)

 

 

 
KEEP(DENSE_RANK FIRST ORDER BY ) 的研究
 

有表test数据如下:

   ID  MC   SL
    1   111   1
    1   222   1
    1   333   2
    1   555   3
    1   666   3
    2   111   1
    2   222   1
    2   333   2
    2   555   2

selectid,mc,sl,

min(mc) keep (DENSE_RANK firstORDERBY sl) over(partitionbyid) A,

max(mc) keep (DENSE_RANK firstORDERBY sl) over(partitionbyid) B,

min(mc) keep (DENSE_RANK last  ORDERBY sl) over(partitionbyid) C,

max(mc) keep (DENSE_RANK last  ORDERBY sl) over(partitionbyid) D

from test;

    ID   MC  SL  A   B   C   D
    1   111  1  111 222 555 666
    1   222  1  111 222 555 666
    1   333  2  111 222 555 666
    1   555  3  111 222 555 666
    1   666  3  111 222 555 666
    2   111  1  111 222 333 555
    2   222  1  111 222 333 555
    2   333  2  111 222 333 555
    2   555  2  111 222 333 555

具体逻辑如下:以min(mc) keep (DENSE_RANK firstORDERBY sl) over(partitionbyid)  为例
1、确认over(partitionbyid),选取ID=1的5行
2、通过(DENSE_RANK ORDERBY sl),对ID=1的5行中的sl进行排序
3、通过keep (DENSE_RANK firstORDERBY sl),选出其中的第一个数据sl=1
4、用min(mc)选取id=1 & sl=1时的mc最小值

 
3、几个层级函数的使用:
 

① 首先是grouping sets的使用方法:

select a,b,c,sum(d) from t

groupbygroupingsets (a,b,c);

上面这个语句等同于一下语句:

select * from

(select a,null,null,sum( d ) from t groupby a

unionall

 selectnull,b,null,sum( d ) from t groupby b

unionall

 selectnull,null,c,sum( d ) from t groupby c);

 

rollup函数:

select a,b,c,sum(d) from t

groupbyrollup(a, b, c);

等同于:

select * from

(select a,b,c,sum(d) from t groupby a, b, c

unionall

 select a,b,null,sum(d) from t groupby a, b

unionall

 select a,null,null,sum(d) from t groupby a

unionall

 selectnull,null,null,sum(d) from t);

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

所以说:

select a,b,c,sum(d) from t

groupbygroupingsets ((a,b,c),(a,b),a,());

等同与

select a,b,c,sum(d) from t

groupbyrollup(a, b, c);

 

cube函数:

select a,b,c,sum(d) from t

groupbycube(a, b, c);

等同于:

select * from

(select a,b,c,sum(d) from t groupby a, b, c

unionall

 select a,b,null,sum(d) from t groupby a, b

unionall

 select a,null,c,sum(d) from t groupby a, c

unionall

 selectnull,b,c,sum(d) from t groupby b, c

unionall

 select a,null,null,sum(d) from t groupby a

unionall

 selectnull,b,null,sum(d) from t groupby b

unionall

 selectnull,null,c,sum(d) from t groupby c

unionall

 selectnull,null,null,sum(d) from t);

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

以所有可以组合的种类汇总!

 

④ Grouping() 函数的用法
以上的grouping sets、rollup、Cube函数在汇总时均会涉及到null值
如何对这些null进行自定义呢?
需要使用Grouping()函数

select

decode(grouping(a),1,'all a',a) a,

decode(grouping(b),1,'all b',b) b,

decode(grouping(c),1,'all c',c) c,

sum(d) from t

groupbycube(a,b,c);

则:所有a列为null处显示all a,其他列类推

 
我的综合应用

select organ_id,decode(grouping(organ_id),1,'合计',min(organ_name)) organ_name,

sum(decode(to_char(signdate,'yyyymm'),'200801',prem,0))/10000一月,

sum(decode(to_char(signdate,'yyyymm'),'200802',prem,0))/10000二月,

sum(decode(to_char(signdate,'yyyymm'),'200803',prem,0))/10000三月,

sum(decode(to_char(signdate,'yyyymm'),'200804',prem,0))/10000四月,

sum(decode(to_char(signdate,'yyyymm'),'200805',prem,0))/10000五月,

sum(decode(to_char(signdate,'yyyymm'),'200806',prem,0))/10000六月,

sum(decode(to_char(signdate,'yyyymm'),'200807',prem,0))/10000七月,

sum(decode(to_char(signdate,'yyyy'),'2008',prem,0))/10000  合计

from t_initial_prem

where branchtype='1'

and appflag in ('1','4')---无必要

and ctflag <> 'N'

groupbyrollup(organ_id)

orderby1;

 

 
4、lag和lead函数的使用技巧:
 
selectrownumfrom t1;
ROWNUM
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

selectrownum,lag(rownum,3,8) over(orderbyrownum) LAG,

lead(rownum,3,8) over(orderbyrownum) LEAD from t1;

ROWNUM   LAG   LEAD
            4
            5
            6
            7
            8
            9
            10
            8
            8
 10           8

LAG ——列出某字段的前N行的值;
LEAD——列出某字段的后N行的值;
LAG(arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量,时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值(必须为数字或空)
 
 
 
=======================================================================================
 
 

Oracle层次查询和分析函数

在号段选取中的应用

作者:lastwinner
BLOG:
http://lastwinner.itpub.net

摘要

一组连续的数,去掉中间一些数,如何求出剩下的数的区间(即号段)?知道号段的起止,如何求出该号段内所有的数?知道一个大的号段范围和已经取过的号段,如何求出可用的号段?利用Oracle提供的强大的查询功能以及分析函数,我们可以很轻松的解决上述问题。

n关键词:

号段选取、连续数、断点、层次查询、分析函数、connect byrownumlevelleadlag


1.问题的提出

在实际工作中,我们常常会碰到号段选取的问题,例如:

n一组连续的数,去掉中间一些数,要求出剩下的数的区间(即号段)例如:一串数字为1,2,3,4,7,9,10,则号段为1-4,7-7,9-10

n知道号段的起止,要求出该号段内所有的数例如:号段为1-3,15-15,则号段内所有的数为1,2,3,15

n一组数,中间可能有断点,要求出缺失的数例如:一串数字为1,2,3,4,7,9,10,则缺失的数为5,6,8

n已知大号段范围及已用号段范围,求可用号段范围例如:大号段范围0-999,已用号段范围0-200,399-599,则可用号段范围为201-398,600-999

2.基础知识

先做下热身运动,回顾一下层次查询和leadlag函数的运用。

2.1伪列rownumlevel

伪列就是并非在表中真正存在的列。已有很多资料介绍rownumlevel这两个伪列。这里只想强调一点,伪列是只针对结果集的

2.2利用层次查询构造连续的数

n产生584个连续的数

select * from (select rownum+4 from dual connect by rownum<5);

select * from (select level+4 from dual connect by level<5);

n8月为界,例如200581日,之前的在校学生入学年份为20012004,之后的为20022005。求当前日期下的在校学生入学年份:

select * from (select to_char(add_months(sysdate, 4), 'yyyy') - rownum from dual connect by rownum<5);

2.3用分析函数LeadLag获得相邻行的字段值

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);

RNPREVIOSNEXT

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

5   6

657

768

87

简单的说,在这里,Lag是获得前一行的内容,而Lead是获得后一行的内容。

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);

RNPREVIOSNEXT

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

5-17

6-18

75-1

86-1

这里,通过指定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来获得首尾点。

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 STEN

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

20130000012000000122

20130000012400000125

20140000000100000005

20140000000700000009

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解答

很明显,这需要构造序列来解决问题

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

运行结果:

IDSEDISH

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

11011110

11011211

21511

21522

21533

21544

21555

38892188

38892289

38892390

38892491

38892592

我们再看下面这种做法:

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

IDSE ROWNUMH

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

21511

21522

21533

21544

21555

嗯,得到的结果也是正确的,若我们把粗斜体字部分去掉后,看看结果是什么样:

IDSE ROWNUMH

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

11011110

11011211

21533

21544

21555

21566

388 92794

这样的结果,显然不是我们需要的,更何况,这是错误的。由此更能深入理解,伪列是只针对结果集的

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,则说明是连续的,中间没有断点。

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

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.13.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;

思路:将大号段的边界与小号段的边界相比,从大号段中将小号段“挖”掉,这样剩下的就是可用号段了。

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

运行结果:

SE

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

099

200299

500554

667999

参考资料

n参考资料

uOracle 8i SQL Reference

uhttp://lastwinner.itpub.net

uhttp://hmxxyy.itpub.net/

n论坛相关讨论帖子

u请教查询语句的写法?http://www.itpub.net/435578.html

u如何sql查询出连续号码段http://www.itpub.net/354052.html

u请教关于号段选取的sql写法http://www.itpub.net/480536.html

u知道号段起止,如何选择该号段内的所有号码?http://www.itpub.net/701508.html

作者简介

lastwinner,主要关注于Oracle开发以及Web开发。欢迎访问我在ITPUBBLOGhttp://lastwinner.itpub.net

*:本文为本人参加ITPUB 2007香山年会的论文。

lastwinner 发表于:2007.02.05 20:13 ::分类: ( Oracle , ) ::阅读:(1519次) :: 评论 (7)
 

 




-The End-

posted on 2008-08-16 06:25 decode360-3 阅读(326) 评论(0)  编辑  收藏 所属分类: SQL Dev

只有注册用户登录后才能发表评论。


网站导航: