# 通过例子理解分析函数

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

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

/*
访问之前/后的行，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

• 博文量
61
• 访问量
182195