數據庫中的函數封裝了一些通用的功能，例如日期類型和字符串類型之間的轉換，每個數據庫系統都內置了一些函數，當然用戶也可以自定義函數。

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

單行函數

單行函數分為五種類型：字符函數、數值函數、日期函數、轉換函數、通用函數。比如：

--大小寫控制函數

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

--initcap: 首字母大寫

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

--字符控制函數

-- concat: 字符連接函數, 等同于 ||

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

分組函數

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

--分組數據：求各個部門的平均工資

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

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

--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; 

分析函數

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

在分析函數出現以前，我們必須使用自聯查詢，子查詢或者內聯視圖，甚至復雜的存儲過程實現的語句，現在只要一條簡單的SQL語句就可以實現了，而且在執行效率方面也有相當大的提高。

分析函數和分組函數的不同

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

分析函數的形式

常用的分析函數如下所列：

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 ...) 

分析函數常見應用場景

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

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

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

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

④查找上一年度銷售***和最差的產品

我們看看上面的幾個問題就可以感覺到這幾個查詢和我們日常遇到的查詢有些不同，具體有：

①需要對同樣的數據進行不同級別的聚合操作

②需要在表內將多條數據和同一條數據進行多次的比較

③需要在排序完的結果集上進行額外的過濾操作

分析函數初體驗

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

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

執行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針對當前所有記錄的前一條、后一條記錄，也就是表中的所有記錄 
            --unbounded：不受控制的，***的 
            --preceding：在...之前 
            --following：在...之后 
            between unbounded preceding and unbounded following) max_sal 
  from emp e; 

運行結果

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

執行SQL

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

運行結果

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

執行SQL

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

運行結果

例4，***測試

執行SQL

select 
       deptno 部門編號,ename 員工姓名,sal 薪水,       
       avg(sal) over(partition by deptno) 該部門薪水均值, 
       sum(sal) over(partition by deptno) 該部門薪水總額, 
       count(sal) over(partition by deptno) 部門員工數量, 
       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 

運行結果

注意：

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

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

【本文為51CTO專欄作者“朱國立”的原創稿件，轉載請通過作者微信公眾號“開發者圓桌”獲取聯系和授權】

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