Oracle分析函數

分析函數，通過對SQL語言進行擴充。

例子：将計算scott方案中emp表的員工薪資的運作總計，分部門運作總計。

select ename,deptno，sal，SUM（sal） OVER （order by deptno，ename）運作總計，SUM（sal） OVER（PARTITION by deptno order by ename）分部門運作總計，ROW_NUMBER（）OVER（PARTITION by deptno order by ename） 序列  from emp where deptno IN（10,20） order by deptno，ename

1.sum和over是分析函數的語句，sum是一個分析函數，盡管這個函數與分組函數中的sum同名，但是在與over關鍵字連用時，sum被辨別為分析函數。

2.order by 是可選的關鍵字，有些函數需要，

3.PARTTITION BY 是可選的分區子句

4.row_number（）函數根據排序标準，傳回每個組的行編号，row_number（）傳回的行号根據分區與排序的不同而不同。

分析函數結構

一.分析函數名

oracle提供了26個分析函數 分為五大類

1.等級函數：用于前N種查詢，比如ROW_NUMBER函數，RANK， DENSE_RANK等

select o.deptno，o.job，sum（o.sal） 部門職級彙總，rank（ ）over（order by sum（o.sal） desc） 薪資等級， dense_rank() over（order by sum（o.sal）desc）dense_rank  排名，row_number（） over （partition by o.deptno order  by sum（o.sal） desc） 分組行号，sum（sum（o.sal）） over  （partition by o.deptno order by o.job）部門薪資運作總計 from emp o where deptno in （10，20，30） and job is not  null group by o.deptno，o.job; 

可以看到rank和dense_rank對記錄進行了排名。

2.開窗函數：用來計算行的累計值，這些函數與分組函數同名， 比如sum count avg等

sum（t.sal） over （order by t.deptno，t.ename）  running_total

3.制表函數：與開窗函數相似，制表函數也允許對一個結果集執 行多種聚合運算，比如min，max sum count等，與視窗函數不同 的是，制表函數不能指定一個本地視窗，是以總是在整個分區或 整個組上産生相同的結果

sum（t.sal） over（）running_total2

可以看到比開窗函數基礎上少了order by子句

4.LAG和LEAD函數：允許在結果集中向前或向後檢索值，如果要避 免資料的自連接配接，它們是非常有用的

5.其他統計函數：例如var_pop  var_samp  和stdev_pop及線性 的衰減函數等這些函數計算任何未排序分區的統計值。

二.分區子句

分區子句使用partition by關鍵字，用來簡單的結果集分為N組。

select deptno，ename，empno row_number （） over（partition by deptno order by empno） 分組行号 from emp where deptno in （10，20，30）;

當指定了partition by 關鍵字時 行号在切換到不同的deptno時 将自動從1開始重新配置設定

三.排序子句

order by 子句用于指定分組中資料的排序方式，排序方式會明顯地影響任何分析函數的結果。

分析函數總是按order by對行排序，然而，分析函數中的order by子句隻能對各個分組進行排序，而不能保證查詢結果有序。要保證最後的查詢結果有序，可以使用查詢的order by子句

四.開窗子句

開窗子句必須定義在order by子句的後面，用來定義一個變化或固定的資料視窗方法，分析函數将對這些資料進行操作。在分區内部基于任何的變化或固定的視窗中，通過視窗讓分析函數來計算其值

例如統計各部門的工資小計及所有部門的薪資總計

select deptno，sum（sal） 部門薪資小計，sum（sum（sal））over（order by deptno rows between unbounded preceding and unbounded following ）部門總計 from emp group by deptno;

rows between unbounded preceding and unbounded following這個子句是oracle提供的開窗子句，是指将計算第一條到最後一條的記錄，也就是表中所有的記錄

分析函數清單

1.COUNT函數

統計各分區中各組的行數

select empno，ename count（*） over （partition by deptno order by empno）條數小記 from emp;

Distinct用來統計唯一值，僅在分區子句中使用，不能在order by 和開窗子句中使用

統計與目前員工工資的差異在50~150之間的其他員工的記錄

select empno，ename，sal，count（*）over（order by sal range between 50 preceding and 150 following）薪水差異個數 from emp;

range between來判斷sal值是否在50到150之間

2.sum函數

用來彙總分區中的記錄

3.avg函數

例如要計算emp表中每個部門的平均薪資表。

select deptno，empno，ename，sal，avg（sal） over （partition by deptno order by deptno）avg_sal from emp;

4.min和max函數

用來計算分區中的最小值或最大值，這兩個函數的聲明文法

查詢同一部門中雇傭日期比目前員工早的員工的最低薪水與最高薪水

select deptno，empno，ename，hiredata，sal，min（sal） over（partition by deptno order by hiredate rang unbounded preceding）最低薪水 max（sal） over（partition by deptno order by hiredate range unbounded preceding ）最高薪水 from emp;

5.rank，dense_rank和row_number函數

rank和dense_rank都用來記錄編号，根據order by子句中表達式的值，計算它們與其他行的相對位置，每一行賦一個數字序号，形成一個從1開始的序列，将相同的值得到同樣的數字序号。不同之處在于rank将相同的行配置設定同樣的序号之後，後面的行将跳躍，比如如果兩行序數為1，則沒有序數2，直接跳到序數3，而dense_rank則沒有任何跳躍值。

row_number也是從1開始用來為記錄配置設定行号，與rank和dense_rank不同的是，不管是否存在重複行，分區類的序列值始終遞增。

select deptno，ename，sal，mgr，

rank（） over（order by deptno） rank 結果，

dense_rank（） over（order by deptno）dense_rank 結果，

row_number（）over（order by deptno）row_number 結果 from

emp where deptno in （10，20，30） and mgr is not null order by deptno;

6.FIRST 和LAST 函數

first和last 取dense_rank傳回的集合中取出排在第一的行和最後一行。

下面的語句使用first和last傳回emp表中具有最高提成的員工薪水和具有最低提成的員工薪水：

select deptno，

min（sal）keep（dense_rank first order by comm）最低提成薪水，

max（sal）keep （dense_rank last order by comm）最高提成薪水

from emp where deptno in（10，20，30） group by deptno;

顯示最高提成和最低提成員工的薪資

7.first_value和last_value 函數

分别用來傳回over子句中查詢出來的第一條記錄和最後一條記錄。

擷取部門中第一條薪資記錄值和最後一條記錄值

select deptno，empno，sal，

first_value（sal） over （partition by deptno order by empno）"第一個值"，

last_value（sal）over（partition by deptno order by empno） "最後一個值"

from emp where deptno in（10，20）;

8.lag和lead函數

lag的功能是傳回指定列col前n1行的值（如果前n1行已經超出比照範圍，則傳回n2，如果不指定n2則預設傳回null），如果不指定n1，其預設值為1;lead函數與此相反，傳回指定列coll後面的n1行的值。

使用lag和lead函數查找目前雇傭的前一個雇員的薪水和後一個雇員的薪水

select ename，hiredate，sal，deptno，

lag（sal，1，0）over（order by hiredate）as 前一個雇員薪水，

lead（sal，1，0）over（order by hiredate）as 後一個雇員薪水 

from emp where deptno=30;

可以看到由于第一條記錄的前一條不存在，是以前一個雇員的薪水傳回0;最後一條記錄由于不存在後一條記錄，是以後一個雇員的薪水也為0

分析函數使用示例

1.記錄排名

人事部需要對emp表中各個不同部門的員工薪資總計進行排名，可以使用如下：

select deptno ，empno，sum（sal） dept_sales，

rank（）over（partition by deptno order by sum（sal）

dese nulls last）薪資排名_跳号，

dense_rank（）over（partition by deptno order by sum（sal ）desc nulls last）薪資排名_同級同号，

row_number（）over（partition by deptno order by sum（sal ）desc nulls last）薪資排名_不跳号

from emp group by deptno，empno，ename;

語句中使用nulls last  使得null值排在分區的最後。

2.首尾記錄查詢

查詢出員工薪資中最高和最低薪資的員工編号，聽起來需要兩個分組查詢，實際上使用分組函數後，一行語句就可以得到想要的結果。

select min（empno）keep（dense_rank first order by sum（sal）desc   nulls last）薪資排名首位，

       min（empno）keep(dense_rank last order by sum(sal) desc  nulls last) 薪資排名尾位

from emp where sal is not null and deptno is not null group by empno;

keep用于告訴oracle儲存符合keep後面語句條件的記錄。

3.前後排名查詢

查詢部門的員工薪資排名在前2位的員工清單

select deptno，empno，ename，dept_sales，薪資排名 from

（

select deptno，empno，ename，sum（sal）dept_salse，

   desc nulls last）薪資排名

from emp where deptno is not null group by deptno，empno，ename— ）where 薪資排名<=2;

上述查詢結果作為一個内聯視圖，通過對内聯視圖傳回的排名進行排序，很輕松就得到了部門分組中的薪資的前2名。當然如果要擷取排名在後2位的員工，隻需要order by 子句中将排序進行反轉，按正向進行排序即可

select deptno，empno，ename，sum（sal）dept_sales，

rank（）over（aprtition by deptno order by sum（sal）  nulls last）薪資排名

from emp where deptno is not null group by deptno，empno，ename）

where 薪資排名<=2;

4.層次查詢

所為查詢，是指按比率得到結果數，比如想查詢薪資排名在前1/3的員工清單。 要查詢薪資在前1/3的員工清單

select * from

NTILE（3）over（partition by deptno 

order by sum（sal）nulls last）rank_ratio

from emp where deptno is not null 

group by deptno，empno，ename）

where rank ratio=1;

5。範圍統計查詢

範圍查詢是分析函數發揮其功能的重點，範圍查詢是指查詢目前記錄的前面或後面的記錄進行統計。

比如想知道員工史密斯的雇傭日期前10天和後10天新進員工的最高薪資。

select empno，ename，hiredate，sal，

MAX（sal）over（order by hiredate rows between 10 preceding and

current row） "前10天入職員工最高薪資"，

MAX（sal）over（order by hiredate rows between current row and 10

following）"後10天入職員工最高薪資"

from emp

where deptno in（10，20，30）and sal is not null;

6.相鄰記錄比較

查詢目前員工前一個入職的員工和後一個入職的員工與目前員工的薪資差異

select ename，hiredate，deptno，sal，sal-prev_sal "與前面的差異" ，sal-next_sal "與後面的差異"

from （select ename，hiredate，sal，deptno，

LAG （sal，1，0）over（order by hiredate）as prev_sal，

LEAD（sal，1，0）over（order by hiredate）as next_sal

where deptno is not null and sal is not null）;

7.抑制重複

人事部需要随機地提取各部門在1980 1981 和1982年入職的任意一個員工，由于在各個部門中這幾個年份入職的員工較多，是以人事部門隻是随意地提取一個員工即可，不要求統計提取，可以使用如下的分析查詢語句：

select * 

from （select empno，ename，sal，hiredate，

ROW_NUMBER（）over（partition by extract

（year from hiredate） order by empno） rn

where hiredate is not null

and extract （year from hiredate） in （1981，1982，1983））

where rn=1;

8.行列轉換查詢

select job，empno，ename

row_number（）over（partition by job order by ename）rn

where job is not null;

select max（decode（job，'職員'，ename，NULL））"職員名稱"，

    max（decode（job，'分析人員'，ename，NULL））"分析人員名稱"，

    max（decode（job，'經理'，ename，NULL））"經曆名稱"，

      max（decode（job，'老闆'，ename，NULL））"老闆名稱"，

    max（decode（job，'銷售人員'，ename，NULL））"銷售人員名稱"

from（select job，empno，ename，row_number（）over （partition by  job order by ename）rn from emp where job is not null）x

在PL/SQL中使用分析函數

declare

type refempcur is ref cursor;

empcur refempcur;

jobname varchar（20）;

ename   varchar2（20）;

empno   number;

rn int;

begin

open empcur for

select job，empno，ename，row_number （） over （partition by job  order by ename）rn from emp where job is not null;

loop

exit when empcur%NOTFOUND;

fetch empcur

into jobname，empno，ename，rn;

dbms_output.put_line（jobname || '  '||empno || '  '||ename||'   '||rn）;

end loop;

end;

      本文轉自潘闊 51CTO部落格，原文連結：http://blog.51cto.com/pankuo/1630251，如需轉載請自行聯系原作者