快捷導(dǎo)航

常用Oracle分析函數(shù)大全

更新時間：2016年08月25日 11:09:22 作者：Jay_1989

本文是小編日常手機整理的有關(guān)oracle分析函數(shù)，oracle分析函數(shù)功能非常強大，工作也常用到，所以大家務(wù)必要掌握，對oracle分析函數(shù)知識感興趣的朋友一起看看吧

Oracle的分析函數(shù)功能非常強大，工作這些年來經(jīng)常用到。這次將平時經(jīng)常使用到的分析函數(shù)整理出來，以備日后查看。

我們拿案例來學(xué)習(xí)，這樣理解起來更容易一些。

1、建表

create table earnings -- 打工賺錢表 
( 
 earnmonth varchar2(6), -- 打工月份 
 area varchar2(20), -- 打工地區(qū) 
 sno varchar2(10), -- 打工者編號 
 sname varchar2(20), -- 打工者姓名 
 times int, -- 本月打工次數(shù) 
 singleincome number(10,2), -- 每次賺多少錢 
 personincome number(10,2) -- 當月總收入 
)

2、插入實驗數(shù)據(jù)

insert into earnings values('200912','北平','511601','大魁',11,30,11*30);  
insert into earnings values('200912','北平','511602','大凱',8,25,8*25);  
insert into earnings values('200912','北平','511603','小東',30,6.25,30*6.25);  
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);  
insert into earnings values('200912','北平','511605','賤敬',30,11,30*11);  
insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);  
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);  
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);  
insert into earnings values('200912','金陵','511304','小俐',0,18,0);  
insert into earnings values('200912','金陵','511305','雪兒',11,9.88,11*9.88);  
insert into earnings values('201001','北平','511601','大魁',0,30,0);  
insert into earnings values('201001','北平','511602','大凱',14,25,14*25);  
insert into earnings values('201001','北平','511603','小東',19,6.25,19*6.25);  
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);  
insert into earnings values('201001','北平','511605','賤敬',21,11,21*11);  
insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);  
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);  
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);  
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);  
insert into earnings values('201001','金陵','511305','雪兒',11,9.88,11*9.88);  
commit;

3、查看實驗數(shù)據(jù)

select * from earnings;

查詢結(jié)果如下

4、sum函數(shù)按照月份，統(tǒng)計每個地區(qū)的總收入

select earnmonth, area, sum(personincome)  
from earnings  
group by earnmonth,area;

查詢結(jié)果如下

5、rollup函數(shù)按照月份，地區(qū)統(tǒng)計收入

select earnmonth, area, sum(personincome)  
from earnings  
group by rollup(earnmonth,area);

查詢結(jié)果如下

6、cube函數(shù)按照月份，地區(qū)進行收入?yún)R總

select earnmonth, area, sum(personincome)  
from earnings  
group by cube(earnmonth,area)  
order by earnmonth,area nulls last;

查詢結(jié)果如下

小結(jié)：sum是統(tǒng)計求和的函數(shù)。

group by 是分組函數(shù)，按照earnmonth和area先后次序分組。

以上三例都是先按照earnmonth分組，在earnmonth內(nèi)部再按area分組，并在area組內(nèi)統(tǒng)計personincome總合。

group by 后面什么也不接就是直接分組。

group by 后面接 rollup 是在純粹的 group by 分組上再加上對earnmonth的匯總統(tǒng)計。

group by 后面接 cube 是對earnmonth匯總統(tǒng)計基礎(chǔ)上對area再統(tǒng)計。

另外那個 nulls last 是把空值放在最后。

rollup和cube區(qū)別：

如果是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函數(shù)在以上例子中，是用rollup和cube函數(shù)都會對結(jié)果集產(chǎn)生null，這時候可用grouping函數(shù)來確認
該記錄是由哪個字段得出來的

grouping函數(shù)用法，帶一個參數(shù)，參數(shù)為字段名，結(jié)果是根據(jù)該字段得出來的就返回1，反之返回0

select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份,  
    decode(grouping(area),1,'全部地區(qū)',area) 地區(qū), sum(personincome) 總金額  
from earnings  
group by cube(earnmonth,area)  
order by earnmonth,area nulls last;

查詢結(jié)果如下

8、rank() over開窗函數(shù)

按照月份、地區(qū)，求打工收入排序

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

查詢結(jié)果如下

9、dense_rank() over開窗函數(shù)按照月份、地區(qū)，求打工收入排序2

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

查詢結(jié)果如下

10、row_number() over開窗函數(shù)按照月份、地區(qū)，求打工收入排序3

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

查詢結(jié)果如下

通過(8)(9)(10)發(fā)現(xiàn)rank,dense_rank,row_number的區(qū)別：

結(jié)果集中如果出現(xiàn)兩個相同的數(shù)據(jù)，那么rank會進行跳躍式的排名，

比如兩個第二，那么沒有第三接下來就是第四；

但是dense_rank不會跳躍式的排名，兩個第二接下來還是第三；

row_number最牛，即使兩個數(shù)據(jù)相同，排名也不一樣。

11、sum累計求和根據(jù)月份求出各個打工者收入總和，按照收入由少到多排序

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

查詢結(jié)果如下

12、max，min，avg和sum函數(shù)綜合運用按照月份和地區(qū)求打工收入最高值，最低值，平均值和總額

select distinct earnmonth 月份, area 地區(qū),  
    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;

查詢結(jié)果如下

13、lag和lead函數(shù)求出每個打工者上個月和下個月有沒有賺錢(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;

查詢結(jié)果如下

說明：Lag和Lead函數(shù)可以在一次查詢中取出某個字段的前N行和后N行的數(shù)據(jù)（可以是其他字段的數(shù)據(jù)，比如根據(jù)字段甲查詢上一行或下兩行的字段乙）

語法如下：

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：可以是一個字段或一個內(nèi)建函數(shù)。

offset是正整數(shù)，默認為1，指往前或往后幾點記錄．因組內(nèi)第一個條記錄沒有之前的行，最后一行沒有之后的行，default就是用于處理這樣的信息，默認為空。

再講講所謂的開窗函數(shù)，依本人遇見，開窗函數(shù)就是 over([query_partition_clase] order_by_clause)。比如說，我采用sum求和，rank排序等等，但是我根據(jù)什么來呢？over提供一個窗口，可以根據(jù)什么什么分組，就用partition by，然后在組內(nèi)根據(jù)什么什么進行內(nèi)部排序，就用 order by。

以上所述是小編給大家介紹的常用Oracle分析函數(shù)大全，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！

您可能感興趣的文章: