Oracle分析函数

假设是ROLLUP(A, B, C)的话，GROUP BY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUP BY操作。

假设是GROUP BY CUBE(A, B, C)，GROUP BY顺序
(A、B、C)
(A、B)
(A、C)
(A)
(B、C)
(B)
(C)
最后对全表进行GROUP BY操作。

7、grouping函数

在以上样例中。是用rollup和cube函数都会对结果集产生null。这时候可用grouping函数来确认
该记录是由哪个字段得出来的
grouping函数使用方法，带一个參数，參数为字段名，结果是依据该字段得出来的就返回1，反之返回0

select decode(grouping(earnmonth),1,‘所有月份‘,earnmonth) 月份,  
       decode(grouping(area),1,‘所有地区‘,area) 地区, sum(personincome) 总金额  
from earnings  
group by cube(earnmonth,area)  
order by earnmonth,area nulls last;

查询结果例如以下

8、rank() over开窗函数

依照月份、地区，求打工收入排序

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   
       rank() over (partition by earnmonth,area order by personincome desc) 排名  
from earnings; 

查询结果例如以下

9、dense_rank() over开窗函数

依照月份、地区，求打工收入排序2

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   
       dense_rank() over (partition by earnmonth,area order by personincome desc) 排名  
from earnings; 

查询结果例如以下

10、row_number() over开窗函数

依照月份、地区，求打工收入排序3

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   
       row_number() over (partition by earnmonth,area order by personincome desc) 排名  
from earnings; 

查询结果例如以下

通过(8)(9)(10)发现rank,dense_rank,row_number的差别：
结果集中假设出现两个同样的数据。那么rank会进行跳跃式的排名，
比方两个第二。那么没有第三接下来就是第四；
可是dense_rank不会跳跃式的排名。两个第二接下来还是第三；
row_number最牛，即使两个数据同样。排名也不一样。

11、sum累计求和

依据月份求出各个打工者收入总和，依照收入由少到多排序

select earnmonth 月份,area 地区,sname 打工者,   
       sum(personincome) over (partition by earnmonth,area order by personincome) 总收入  
from earnings;

查询结果例如以下

12、max，min，avg和sum函数综合运用

依照月份和地区求打工收入最高值，最低值，平均值和总额

select distinct earnmonth 月份, area 地区,  
       max(personincome) over(partition by earnmonth,area) 最高值,  
       min(personincome) over(partition by earnmonth,area) 最低值,  
       avg(personincome) over(partition by earnmonth,area) 平均值,  
       sum(personincome) over(partition by earnmonth,area) 总额  
from earnings;

查询结果例如以下

13、lag和lead函数

求出每一个打工者上个月和下个月有没有赚钱(personincome大于零即为赚钱)

select earnmonth 本月,sname 打工者,  
       lag(decode(nvl(personincome,0),0,‘没赚‘,‘赚了‘),1,0) over(partition by sname order by earnmonth) 上月,  
       lead(decode(nvl(personincome,0),0,‘没赚‘,‘赚了‘),1,0) over(partition by sname order by earnmonth) 下月  
from earnings;

查询结果例如以下

说明：Lag和Lead函数能够在一次查询中取出某个字段的前N行和后N行的数据（能够是其它字段的数据，比方依据字段甲查询上一行或下两行的字段乙）

语法例如以下：
lag(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
lead(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)。
当中：
value_expression：能够是一个字段或一个内建函数。
offset是正整数。默觉得1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行。
default就是用于处理这种信息，默觉得空。

?
再讲讲所谓的开窗函数，依本人遇见，开窗函数就是 over([query_partition_clase] order_by_clause)。

比方说。我採用sum求和，rank排序等等。可是我依据什么来呢？over提供一个窗体，能够依据什么什么分组，就用partition by，然后在组内依据什么什么进行内部排序，就用 order by。

Oracle分析函数

标签：技术分享   返回   信息   nts   from   技术   net   gpo   font