分析函数——rollup,cube,rank,partition by

 

记录开始前，先看看测试表的表结构吧

该表字段由上而下分别代表月份，行区，区县，套餐和收入。

根据该表完成以下练习：

　　1.汇总出一个各行区和全市的收入报表：

　　　　a).仅按行区分组：

　　

select decode(grouping(xingqu),1,'合计',xingqu) xingqu,sum(income) 总收入 from trainquestion_5_1 group by rollup(xingqu);

 　　

查询结果显示各个行区的收入以及全市的总收入。

　　　　b).按照月份和行区分别分组：

select decode(grouping_id(xingqu,monthtime),2,'小计',3,'合计',xingqu) xingqu,
decode(grouping_id(xingqu,monthtime),1,'小计',3,'合计',monthtime) monthtime,
sum(income) 总收入 
from trainquestion_5_1
group by rollup(xingqu,monthtime);

 结果显示月份和行区的组合分组以及全市的总收入。

　　2.按月份和行区汇总一个各行区和全市以及各月和全年的收入报表:

select decode(grouping_id(xingqu,monthtime),2,'小计',3,'合计',xingqu) xingqu,
decode(grouping_id(xingqu,monthtime),1,'小计',3,'合计',monthtime) monthtime,
sum(income) 总收入 
 from trainquestion_5_1 
group by cube(xingqu,monthtime);

　　3.取得各月收入排名前十的套餐的收入，并给出名次:　　

select monthtime,taocan,总收入,排名
 from
 (
select monthtime,taocan,sum(income) 总收入,
Rank() over(order by sum(income) desc) 排名 
from trainquestion_5_1 
group by monthtime,taocan
) 
where 排名<11;

　　　注：这里应该注意的是rank、dense_rank、row_number的区别：

　　　　　如，在排名字段中存在相同的值，即存在并列排名是时，执行下面查询语句，列出排名情况：　

select monthtime,taocan,sum(income) 总收入,
Rank() over(order by sum(income) desc) rank,
dense_rank() over(order by sum(income) desc) dense_rank,
row_number() over(order by sum(income) desc) row_number
 from trainquestion_5_1 
group by monthtime,taocan;

 

　　　注意对比查询结果中存在并列排名的列，rank在并列排名后，保留5、6直接到达第七名，dense_rank则在第4名之后顺序排第5名，而row_number则忽略掉并列情况，只根据查询顺序流出4、5、6、7...

　　　所以，在实际应用中，应当注意三种排名的区别，尤其存在并列排名时，row_number易丢掉数据，而rank和dense_rank则各有特点。

　　5.取出各月收入最多的行区的收入:

select * 
from 
(
select monthtime,xingqu,sum(income) 总收入,
Rank() over(partition by monthtime order by sum(income) desc)  from trainquestion_5_1 
group by monthtime,xingqu
) 
where t='1';