快捷導(dǎo)航

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

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

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

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

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

1、建表

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

2、插入實(shí)驗(yàn)數(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í)驗(yàn)數(shù)據(jù)

select * from earnings;

查詢結(jié)果如下

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

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

查詢結(jié)果如下

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

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

查詢結(jié)果如下

6、cube函數(shù)按照月份，地區(qū)進(jìn)行收入?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)計(jì)求和的函數(shù)。

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

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

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

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

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

另外那個(gè) nulls last 是把空值放在最后。

rollup和cube區(qū)別：

如果是ROLLUP(A, B, C)的話，GROUP BY順序

(A、B、C)
(A、B)
(A)

最后對(duì)全表進(jìn)行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)，GROUP BY順序

(A、B、C)
(A、B)
(A、C)
(A)
(B、C)
(B)
(C)

最后對(duì)全表進(jìn)行GROUP BY操作。

7、grouping函數(shù)在以上例子中，是用rollup和cube函數(shù)都會(huì)對(duì)結(jié)果集產(chǎn)生null，這時(shí)候可用grouping函數(shù)來確認(rèn)
該記錄是由哪個(gè)字段得出來的

grouping函數(shù)用法，帶一個(gè)參數(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)兩個(gè)相同的數(shù)據(jù)，那么rank會(huì)進(jìn)行跳躍式的排名，

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

但是dense_rank不會(huì)跳躍式的排名，兩個(gè)第二接下來還是第三；

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

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

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

查詢結(jié)果如下

12、max，min，avg和sum函數(shù)綜合運(yùn)用按照月份和地區(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ù)求出每個(gè)打工者上個(gè)月和下個(gè)月有沒有賺錢(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ù)可以在一次查詢中取出某個(gè)字段的前N行和后N行的數(shù)據(jù)（可以是其他字段的數(shù)據(jù)，比如根據(jù)字段甲查詢上一行或下兩行的字段乙）

語(yǔ)法如下：

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

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

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

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

您可能感興趣的文章: