數(shù)據(jù)庫中的函數(shù)封裝了一些通用的功能，例如日期類型和字符串類型之間的轉(zhuǎn)換，每個數(shù)據(jù)庫系統(tǒng)都內(nèi)置了一些函數(shù)，當(dāng)然用戶也可以自定義函數(shù)。

在Oracle數(shù)據(jù)庫中，函數(shù)可總分為單行函數(shù)、分組函數(shù)「亦稱聚合函數(shù)」、分析函數(shù)三類。

單行函數(shù)

單行函數(shù)分為五種類型：字符函數(shù)、數(shù)值函數(shù)、日期函數(shù)、轉(zhuǎn)換函數(shù)、通用函數(shù)。比如：

--大小寫控制函數(shù)

select lower('Hello World') 轉(zhuǎn)小寫, upper('Hello World') 轉(zhuǎn)大寫 from dual;

--initcap: 首字母大寫

select initcap('hello world') 首字符大寫 from dual;

--字符控制函數(shù)

-- concat: 字符連接函數(shù), 等同于 ||

select concat('Hello',' World') from dual; 

分組函數(shù)

分組函數(shù)「亦稱聚合函數(shù)」能在select或select的having子句中使用，當(dāng)用于select子串時常常都和GROUP BY一起使用。多行函數(shù)分為接收多個輸入，返回一個輸出。比如：

--分組數(shù)據(jù)：求各個部門的平均工資

select deptno,avg(sal) from emp group by deptno; 

--group by作用于多列： 按部門，不同的工種，統(tǒng)計平均工資

--group by作用于多列：先按照***列分組;如果相同，再按照第二列分組

select deptno,job,avg(sal) from emp group by deptno,job; 

--：求部門的平均工資大于2000的部門

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; 

分析函數(shù)

分析函數(shù)是Oracle專門用于解決復(fù)雜報表統(tǒng)計需求的功能強大的函數(shù)，它可以在數(shù)據(jù)中進(jìn)行分組然后計算基于組的某種統(tǒng)計值，并且每一組的每一行都可以返回一個統(tǒng)計值，為我們分析數(shù)據(jù)提供了一種簡單高效的處理方式。

在分析函數(shù)出現(xiàn)以前，我們必須使用自聯(lián)查詢，子查詢或者內(nèi)聯(lián)視圖，甚至復(fù)雜的存儲過程實現(xiàn)的語句，現(xiàn)在只要一條簡單的SQL語句就可以實現(xiàn)了，而且在執(zhí)行效率方面也有相當(dāng)大的提高。

分析函數(shù)和分組函數(shù)的不同

普通的分組函數(shù)用group by分組，每個分組返回一個統(tǒng)計值，而分析函數(shù)采用partition by分組，并且每組每行都可以返回一個統(tǒng)計值。

分析函數(shù)的形式

常用的分析函數(shù)如下所列：

row_number() over(partition by ... order by ...)  
rank() over(partition by ... order by ...)  
dense_rank() over(partition by ... order by ...)  
count() over(partition by ... order by ...)  
max() over(partition by ... order by ...)  
min() over(partition by ... order by ...)  
sum() over(partition by ... order by ...)  
avg() over(partition by ... order by ...)  
first_value() over(partition by ... order by ...)  
last_value() over(partition by ... order by ...)  
lag() over(partition by ... order by ...)  
lead() over(partition by ... order by ...) 

分析函數(shù)常見應(yīng)用場景

一般可以解決這樣的問題：

①查找上一年度各個銷售區(qū)域排名前10的員工

②按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶

③查找上一年度銷售最差的部門所在的區(qū)域

④查找上一年度銷售***和最差的產(chǎn)品

我們看看上面的幾個問題就可以感覺到這幾個查詢和我們?nèi)粘Ｓ龅降牟樵冇行┎煌唧w有：

①需要對同樣的數(shù)據(jù)進(jìn)行不同級別的聚合操作

②需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進(jìn)行多次的比較

③需要在排序完的結(jié)果集上進(jìn)行額外的過濾操作

分析函數(shù)初體驗

簡單介紹幾個分析函數(shù)的使用樣例，讓大家能夠近距離體驗一下Oracle分析函數(shù)的強大，Oracle的資料還是比較好找的「相對于DB2來說」，搜索「Oracle分析函數(shù)」關(guān)鍵字即可獲取更多相關(guān)用法，這些樣例均在scott用戶下成功運行。

例1，顯示各部門員工的工資，并附帶顯示該部門的***工資

執(zhí)行SQL

select e.deptno, 
       e.empno, 
       e.ename, 
       e.sal, 
       last_value(e.sal)  
       over(partition by e.deptno  
            order by e.sal rows  
            --unbounded preceding and unbouned following針對當(dāng)前所有記錄的前一條、后一條記錄，也就是表中的所有記錄 
            --unbounded：不受控制的，***的 
            --preceding：在...之前 
            --following：在...之后 
            between unbounded preceding and unbounded following) max_sal 
  from emp e; 

運行結(jié)果

例2，按照deptno分組，然后計算每組值的總和

執(zhí)行SQL

select empno, 
       ename, 
       deptno, 
       sal, 
       sum(sal) over(partition by deptno order by ename) max_sal 
  from emp; 

運行結(jié)果

例3，當(dāng)前行的上一行(rownum-1)到當(dāng)前行的下輛行(rownum+2)的匯總

執(zhí)行SQL

select empno, 
       ename, 
       deptno, 
       sal, 
       --注意rows between 1 preceding and 1 following 是指當(dāng)前行的上一行(rownum-1)到當(dāng)前行的下輛行(rownum+2)的匯總 
       sum(sal) over(partition by deptno  
                     order by ename  
                     rows between 1 preceding and 2 following) max_sal 
  from emp; 

運行結(jié)果

例4，***測試

執(zhí)行SQL

select 
       deptno 部門編號,ename 員工姓名,sal 薪水,       
       avg(sal) over(partition by deptno) 該部門薪水均值, 
       sum(sal) over(partition by deptno) 該部門薪水總額, 
       count(sal) over(partition by deptno) 部門員工數(shù)量, 
       dense_rank() over(partition by deptno order by sal desc) 該人員的部門薪水排行1, 
       row_number() over(partition by deptno order by sal desc) 該人員的部門薪水排行2, 
       dense_rank() over(order by sal desc) 該人員的全公司薪水排行,         
       min(sal) over(partition by deptno) 該部門的***薪水1 ,  
       min(sal) keep(dense_rank first order by sal) over(partition by deptno) 該部門的***薪水2 ,  
       first_value(sal) over(partition by deptno order by sal) 該部門的***薪水3,       
       max(sal) over(partition by deptno) 該部門的***薪水1, 
       max(sal) keep(dense_rank last order by sal) over(partition by deptno) 該部門的***薪水2, 
       last_value(sal) over(partition by deptno order by sal) 該部門的***薪水3, 
       last_value(sal) over(partition by deptno order by sal rows between unbounded preceding and unbounded following ) 該部門的***薪水4,   
       lag(ename, 1, '00') over(order by sal desc) 薪水在自己前一位的人, 
       lead(ename, 1, '00') over(order by sal desc) 薪水在自己后一位的人 
  from emp e 
order by deptno,sal,ename 

運行結(jié)果

注意：

「該部門的***薪水1\2\3」等結(jié)果是一樣的，只是使用了不同的寫法而已。

last_value()的不同寫法導(dǎo)致「該部門的***薪水3」和「該部門的***薪水4」結(jié)果是不同的，可以這樣去理解：last_value()默認(rèn)統(tǒng)計范圍是 rows between unbounded preceding and current row，因此需要加上rows between unbounded preceding and unbounded following ，才能得到正確的統(tǒng)計結(jié)果，「該部門的***薪水4」的統(tǒng)計結(jié)果才是正確的。

【本文為51CTO專欄作者“朱國立”的原創(chuàng)稿件，轉(zhuǎn)載請通過作者微信公眾號“開發(fā)者圓桌”獲取聯(lián)系和授權(quán)】

戳這里，看該作者更多好文