程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 常用Oracle分析函數大全,oracle函數大全

常用Oracle分析函數大全,oracle函數大全

編輯:Oracle教程

常用Oracle分析函數大全,oracle函數大全


Oracle的分析函數功能非常強大,工作這些年來經常用到。這次將平時經常使用到的分析函數整理出來,以備日後查看。

我們拿案例來學習,這樣理解起來更容易一些。

1、建表

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

2、插入實驗數據

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、查看實驗數據

select * from earnings; 

查詢結果如下


4、sum函數按照月份,統計每個地區的總收入

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

查詢結果如下


5、rollup函數按照月份,地區統計收入

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

查詢結果如下


6、cube函數按照月份,地區進行收入匯總

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

查詢結果如下

小結:sum是統計求和的函數。

group by 是分組函數,按照earnmonth和area先後次序分組。

以上三例都是先按照earnmonth分組,在earnmonth內部再按area分組,並在area組內統計personincome總合。

group by 後面什麼也不接就是直接分組。

group by 後面接 rollup 是在純粹的 group by 分組上再加上對earnmonth的匯總統計。

group by 後面接 cube 是對earnmonth匯總統計基礎上對area再統計。

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

rollup和cube區別:

如果是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分析函數大全,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復大家的。在此也非常感謝大家對幫客之家網站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved