Oracle分析函數

標籤：技術分享   返回   資訊   nts   from   技術   net   gpo   font   

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

我們拿案例來學習，這樣理解起來更easy一些。

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;

查詢結果例如以下

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" />

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; 

查詢結果例如以下

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" />

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分析函數