ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过例子理解分析函数

通过例子理解分析函数

原创 Linux操作系统 作者:prt 时间:2008-10-31 17:13:26 0 删除 编辑

http://space.itpub.net/batch.viewlink.phpitemid=83808
http://zhouwf0726.itpub.net/post/9689/247171
http://zhouwf0726.itpub.net/post/9689/247175


create table ta(aid int ,aname varchar2(32),amonth varchar2(8),avalue int);
insert into ta values(1,'a','200801',10);
insert into ta values(2,'a','200802',31);
insert into ta values(3,'a','200803',12);
insert into ta values(4,'b','200801',33);
insert into ta values(5,'b','200802',21);
insert into ta values(6,'b','200803',15);
insert into ta values(7,'c','200801',30);
insert into ta values(8,'c','200802',31);
insert into ta values(9,'c','200803',15);
commit;

 ---rollup---提取汇总或最大最小值
 select amonth ,sum(avalue) fee
  from ta group by rollup( amonth );
 
AMONTH          FEE
-------- ----------
200801           73
200802           83
200803           42
                        198

 select aname,amonth ,sum(avalue) fee
  from ta group by rollup( aname,amonth );
 
ANAME                            AMONTH          FEE
-------------------------------- -------- ----------
a                                200801           10
a                                200802           31
a                                200803           12
a                                                         53
b                                200801           33
b                                200802           21
b                                200803           15
b                                                         69
c                                200801           30
c                                200802           31
c                                200803           15
c                                                         76
                                                          198
 ---cube---总的汇总--
 /*
   nulls last 表示把汇总显示到最后,first的放到最前面
   asc时, nulls last为默认
   desc时, nulls first为默认
 */
 select aname,amonth ,sum(avalue) fee
  from ta
  group by cube(aname,amonth)
  order by aname,amonth nulls first;
 
 ANAME                            AMONTH          FEE
-------------------------------- -------- ----------
a                                                         53
a                                200801           10
a                                200802           31
a                                200803           12
b                                                          69
b                                200801           33
b                                200802           21
b                                200803           15
c                                                         76
c                                200801           30
c                                200802           31
c                                200803           15
                                                         198
                                 200801           73
                                 200802           83
                                 200803           42

 ---grouping---null返回1,其他返回0
 select decode(grouping(aname),1,'所有人',aname) aname,
     decode(grouping(amonth),1,'所有月',amonth)  amonth,
      sum(avalue) avalue
   from ta group by cube(aname,amonth)
 order by aname,amonth nulls first;

ANAME                            AMONTH       AVALUE
-------------------------------- -------- ----------
a                                200801           10
a                                200802           31
a                                200803           12
a                                所有月           53
b                                200801           33
b                                200802           21
b                                200803           15
b                                所有月           69
c                                200801           30
c                                200802           31
c                                200803           15
c                                所有月           76
所有人                           200801           73
所有人                           200802           83
所有人                           200803           42
所有人                           所有月          198

 ---rank,dense_rank,row_number--排名--
 /*
   区别rank跳跃排序如1,2,2,4,而dense_rank()连续排序1,2,2,3,row_number无重复排序,为1,2,3,4
 */
 
   select aname,amonth,
        sum(avalue) avalue,
        rank() over (order by sum(avalue) desc) asort
   from ta
  group by aname,amonth;

ANAME                      AMONTH       AVALUE      ASORT
-------------------------------- -------- ---------- ----------
b                                200801           33          1
a                                200802           31          2
c                                200802           31          2
c                                200801           30          4
b                                200802           21          5
c                                200803           15          6
b                                200803           15          6
a                                200803           12          8
a                                200801           10          9

  select aname,amonth,
        sum(avalue) avalue,
        dense_rank() over (order by sum(avalue) desc) asort
   from ta
  group by aname,amonth;

ANAME                      AMONTH       AVALUE      ASORT
-------------------------------- -------- ---------- ----------
b                                200801           33          1
a                                200802           31          2
c                                200802           31          2
c                                200801           30          3
b                                200802           21          4
c                                200803           15          5
b                                200803           15          5
a                                200803           12          6
a                                200801           10          7
 
  select aname,amonth,
        sum(avalue) avalue,
        row_number() over (order by sum(avalue) desc) asort
   from ta
  group by aname,amonth;

ANAME                      AMONTH       AVALUE      ASORT
-------------------------------- -------- ---------- ----------
b                                200801           33          1
a                                200802           31          2
c                                200802           31          3
c                                200801           30          4
b                                200802           21          5
c                                200803           15          6
b                                200803           15          7
a                                200803           12          8
a                                200801           10          9

  ---取avalue最小的3个记录
  select * from
  (select aid,aname,amonth,avalue,rank() over (order by avalue asc) asort from ta)
  where asort <=3

       AID ANAME                    AMONTH       AVALUE      ASORT
---------- -------------------------------- -------- ---------- ----------
         1 a                                200801           10          1
         3 a                                200803           12          2
         6 b                                200803           15          3
         9 c                                200803           15          3

  ---取每个aname的avalue最小的2个记录
  select * from
  (select aid,aname,amonth,avalue,rank() over (partition by aname order by avalue asc) asort from ta)
  where asort <=2;

       AID ANAME                   AMONTH       AVALUE      ASORT
---------- -------------------------------- -------- ---------- ----------
         1 a                                200801           10          1
         3 a                                200803           12          2
         6 b                                200803           15          1
         5 b                                200802           21          2
         9 c                                200803           15          1
         7 c                                200801           30          2

  ---lag--lead--取前/后数据作为列值----
  /*
     lap/lead(exp,{offset},{default})
     访问之前/后的行,offset是缺省为1 的正数,表示相对行数,default是当超出选定窗范围时的返回值(如前行/后行不存在则返回default)
  */
 
  select aname,
         amonth,
         lag(svalue, 1,6) over(partition by aname order by amonth) pre_value,
         svalue cur_value,
         lead(svalue, 1,8) over(partition by aname order by amonth) post_value
    from (select aname, amonth, sum(avalue) svalue
            from ta
           group by aname, amonth);

ANAME                     AMONTH    PRE_VALUE  CUR_VALUE POST_VALUE
-------------------------------- -------- ---------- ---------- ----------
a                                200801            6         10         31
a                                200802           10         31         12
a                                200803           31         12          8
b                                200801            6         33         21
b                                200802           33         21         15
b                                200803           21         15          8
c                                200801            6         30         31
c                                200802           30         31         15
c                                200803           31         15          8
 
   ---每行显示每个aname的最大最小值
    select aname,
       amonth,
       avalue,
       min(avalue) keep(dense_rank first order by avalue) over(partition by aname) minval,
       max(avalue) keep(dense_rank last order by avalue) over(partition by aname) maxval
  from ta;

ANAME                            AMONTH       AVALUE     MINVAL     MAXVAL
-------------------------------- -------- ---------- ---------- ----------
a                                200801           10         10         31
a                                200802           31         10         31
a                                200803           12         10         31
b                                200801           33         15         33
b                                200802           21         15         33
b                                200803           15         15         33
c                                200801           30         15         31
c                                200802           31         15         31
c                                200803           15         15         31

---如下sql中可以把over(partition by aname)改成over()试试
select aid,aname,amonth,avg(avalue) over(partition by aname) aavg from ta order by aid;
        AID ANAME                   AMONTH         AAVG
---------- --------------------------- -------- ----------
         1 a                                200801   17.6666667
         2 a                                200802   17.6666667
         3 a                                200803   17.6666667
         4 b                                200801           23
         5 b                                200802           23
         6 b                                200803           23
         7 c                                200801   25.3333333
         8 c                                200802   25.3333333
         9 c                                200803   25.3333333

select aname,amonth,avalue,avalue/sum(avalue) over() aratio,ratio_to_report(avalue) over() bratio  from ta;

ANAM AMONTH     AVALUE     ARATIO     BRATIO
---- ------ ---------- ---------- ----------
a    200801         10 .050505051 .050505051
a    200802         31 .156565657 .156565657
a    200803         12 .060606061 .060606061
b    200801         33 .166666667 .166666667
b    200802         21 .106060606 .106060606
b    200803         15 .075757576 .075757576
c    200801         30 .151515152 .151515152
c    200802         31 .156565657 .156565657
c    200803         15 .075757576 .075757576

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/559237/viewspace-483622/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-09

  • 博文量
    61
  • 访问量
    182195