Oracle分析函數(shù)
數(shù)據(jù)庫中的函數(shù)封裝了一些通用的功能，例如日期類型和字符串類型之間的轉(zhuǎn)換，每個(gè)數(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子串時(shí)常常都和GROUP BY一起使用。多行函數(shù)分為接收多個(gè)輸入，返回一個(gè)輸出。比如：
--分組數(shù)據(jù)：求各個(gè)部門的平均工資
- select deptno,avg(sal) from emp group by deptno;
--group by作用于多列： 按部門，不同的工種，統(tǒng)計(jì)平均工資
--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ù)雜報(bào)表統(tǒng)計(jì)需求的功能強(qiáng)大的函數(shù)，它可以在數(shù)據(jù)中進(jìn)行分組然后計(jì)算基于組的某種統(tǒng)計(jì)值，并且每一組的每一行都可以返回一個(gè)統(tǒng)計(jì)值，為我們分析數(shù)據(jù)提供了一種簡(jiǎn)單高效的處理方式。
在分析函數(shù)出現(xiàn)以前，我們必須使用自聯(lián)查詢，子查詢或者內(nèi)聯(lián)視圖，甚至復(fù)雜的存儲(chǔ)過程實(shí)現(xiàn)的語句，現(xiàn)在只要一條簡(jiǎn)單的SQL語句就可以實(shí)現(xiàn)了，而且在執(zhí)行效率方面也有相當(dāng)大的提高。
分析函數(shù)和分組函數(shù)的不同
普通的分組函數(shù)用group by分組，每個(gè)分組返回一個(gè)統(tǒng)計(jì)值，而分析函數(shù)采用partition by分組，并且每組每行都可以返回一個(gè)統(tǒng)計(jì)值。
分析函數(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)用場(chǎng)景
一般可以解決這樣的問題：
①查找上一年度各個(gè)銷售區(qū)域排名前10的員工
②按區(qū)域查找上一年度訂單總額占區(qū)域訂單總額20%以上的客戶
③查找上一年度銷售最差的部門所在的區(qū)域
④查找上一年度銷售***和最差的產(chǎn)品
我們看看上面的幾個(gè)問題就可以感覺到這幾個(gè)查詢和我們?nèi)粘Ｓ龅降牟樵冇行┎煌?，具體有：
①需要對(duì)同樣的數(shù)據(jù)進(jìn)行不同級(jí)別的聚合操作
②需要在表內(nèi)將多條數(shù)據(jù)和同一條數(shù)據(jù)進(jìn)行多次的比較
③需要在排序完的結(jié)果集上進(jìn)行額外的過濾操作
分析函數(shù)初體驗(yàn)
簡(jiǎn)單介紹幾個(gè)分析函數(shù)的使用樣例，讓大家能夠近距離體驗(yàn)一下Oracle分析函數(shù)的強(qiáng)大，Oracle的資料還是比較好找的「相對(duì)于DB2來說」，搜索「Oracle分析函數(shù)」關(guān)鍵字即可獲取更多相關(guān)用法，這些樣例均在scott用戶下成功運(yùn)行。
例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針對(duì)當(dāng)前所有記錄的前一條、后一條記錄，也就是表中的所有記錄
- --unbounded：不受控制的，***的
- --preceding：在...之前
- --following：在...之后
- between unbounded preceding and unbounded following) max_sal
- from emp e;
運(yùn)行結(jié)果
例2，按照deptno分組，然后計(jì)算每組值的總和
執(zhí)行SQL
- select empno,
- ename,
- deptno,
- sal,
- sum(sal) over(partition by deptno order by ename) max_sal
- from emp;
運(yùn)行結(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;
運(yùn)行結(jié)果
例4，***測(cè)試
執(zhí)行SQL
- select
- deptno 部門編號(hào),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
運(yùn)行結(jié)果
注意：
「該部門的***薪水1\2\3」等結(jié)果是一樣的，只是使用了不同的寫法而已。
last_value()的不同寫法導(dǎo)致「該部門的***薪水3」和「該部門的***薪水4」結(jié)果是不同的，可以這樣去理解：last_value()默認(rèn)統(tǒng)計(jì)范圍是 rows between unbounded preceding and current row，因此需要加上rows between unbounded preceding and unbounded following ，才能得到正確的統(tǒng)計(jì)結(jié)果，「該部門的***薪水4」的統(tǒng)計(jì)結(jié)果才是正確的。
【本文為51CTO專欄作者“朱國立”的原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)通過作者微信公眾號(hào)“開發(fā)者圓桌”獲取聯(lián)系和授權(quán)】
