Oracle分析函數(shù)(二)
1.AVG
功能描述：用于計(jì)算一個(gè)組和數(shù)據(jù)窗口內(nèi)表達(dá)式的平均值。
SAMPLE：下面的例子中列c_mavg計(jì)算員工表中每個(gè)員工的平均薪水報(bào)告，該平均值由當(dāng)前員工和與之具有相同經(jīng)理的前一個(gè)和后一個(gè)三者的平均數(shù)得來(lái)；
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 Kochhar 21-SEP-89 17000 17000
100 De Haan 13-JAN-93 17000 15000
100 Raphaely 07-DEC-94 11000 11966.6667
100 Kaufling 01-MAY-95 7900 10633.3333
100 Hartstein 17-FEB-96 13000 9633.33333
100 Weiss 18-JUL-96 8000 11666.6667
100 Russell 01-OCT-96 14000 11833.3333
2.COUNT
功能描述：對(duì)一組內(nèi)發(fā)生的事情進(jìn)行累積計(jì)數(shù)，如果指定*或一些非空常數(shù)，count將對(duì)所有行計(jì)數(shù)，如果指定一個(gè)表達(dá)式，count返回表達(dá)式非空賦值的計(jì)數(shù)，當(dāng)有相同值出現(xiàn)時(shí)，這些相等的值都會(huì)被納入被計(jì)算的值；可以使用DISTINCT來(lái)記錄去掉一組中完全相同的數(shù)據(jù)后出現(xiàn)的行數(shù)。
SAMPLE：下面例子中計(jì)算每個(gè)員工在按薪水排序中當(dāng)前行附近薪水在[n-50,n+150]之間的行數(shù)，n表示當(dāng)前行的薪水例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行沒有，所以count計(jì)數(shù)值cnt3為2（包括自己當(dāng)前行）；cnt2值相當(dāng)于小于等于當(dāng)前行的SALARY值的所有行數(shù)
SELECT last_name, salary, COUNT(*) OVER () AS cnt1,
COUNT(*) OVER (ORDER BY salary) AS cnt2,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING
AND 150 FOLLOWING) AS cnt3 FROM employees;
LAST_NAME SALARY CNT1 CNT2 CNT3
------------------------- ---------- ---------- ---------- ----------
Olson 2100 107 1 3
Markle 2200 107 3 2
Philtanker 2200 107 3 2
Landry 2400 107 5 8
Gee 2400 107 5 8
Colmenares 2500 107 11 10
Patel 2500 107 11 10
3.DENSE_RANK
功能描述：根據(jù)ORDER BY子句中表達(dá)式的值，從查詢返回的每一行，計(jì)算它們與其它行的相對(duì)位置。組內(nèi)的數(shù)據(jù)按ORDER BY子句排序，然后給每一行賦一個(gè)號(hào)，從而形成一個(gè)序列，該序列從1開始，往后累加。每次ORDER BY表達(dá)式的值發(fā)生變化時(shí)，該序列也隨之增加。有同樣值的行得到同樣的數(shù)字序號(hào)（認(rèn)為null時(shí)相等的）。密集的序列返回的時(shí)沒有間隔的數(shù)
SAMPLE：下例中計(jì)算每個(gè)員工按部門分區(qū)再按薪水排序，依次出現(xiàn)的序列號(hào)（注意與RANK函數(shù)的區(qū)別）
SELECT d.department_id , e.last_name, e.salary, DENSE_RANK()
OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN ('60', '90');
DEPARTMENT_ID LAST_NAME SALARY DRANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 3
60 Hunold 9000 4
90 Kochhar 17000 1
90 De Haan 17000 1
90 King 24000 2
RANK()與DENSE_RANK()用法相當(dāng)，但是有一個(gè)區(qū)別：DENSE_RANK在處理相同的等級(jí)時(shí)，等級(jí)的數(shù)值不會(huì)跳過,RANK()則跳過。
RANK
功能描述：根據(jù)ORDER BY子句中表達(dá)式的值，從查詢返回的每一行，計(jì)算它們與其它行的相對(duì)位置。組內(nèi)的數(shù)據(jù)按ORDER BY子句排序，然后給每一行賦一個(gè)號(hào)，從而形成一個(gè)序列，該序列從1開始，往后累加。每次ORDER BY表達(dá)式的值發(fā)生變化時(shí)，該序列也隨之增加。有同樣值的行得到同樣的數(shù)字序號(hào)（認(rèn)為null時(shí)相等的）。然而，如果兩行的確得到同樣的排序，則序數(shù)將隨后跳躍。若兩行序數(shù)為1，則沒有序數(shù)2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。
SAMPLE：下例中計(jì)算每個(gè)員工按部門分區(qū)再按薪水排序，依次出現(xiàn)的序列號(hào)（注意與DENSE_RANK函數(shù)的區(qū)別）
SELECT d.department_id , e.last_name, e.salary, RANK()
OVER (PARTITION BY e.department_id ORDER BY e.salary) as drank
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN ('60', '90');
DEPARTMENT_ID LAST_NAME SALARY DRANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 4
60 Hunold 9000 5
90 Kochhar 17000 1
90 De Haan 17000 1
90 King 24000 3
4.FIRST
功能描述：從DENSE_RANK返回的集合中取出排在最前面的一個(gè)值的行（可能多行，因?yàn)橹悼赡芟嗟龋?，因此完整的語(yǔ)法需要在開始處加上一個(gè)集合函數(shù)以從中取出記錄SAMPLE.
下面例子中DENSE_RANK按部門分區(qū)，再按傭金commission_pct排序，F(xiàn)IRST取出傭金最低的對(duì)應(yīng)的所有行，然后前面的MAX函數(shù)從這個(gè)集合中取出薪水最低的值；LAST取出傭金最高的對(duì)應(yīng)的所有行，然后前面的MIN函數(shù)從這個(gè)集合中取出薪水最高的值.
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000
Ande 80 6400 6100 14000
Lee 80 6800 6100 14000
Tuvault 80 7000 6100 14000
Sewall 80 7000 6100 14000
Marvins 80 7200 6100 14000
Bates 80 7300 6100 14000
5.FIRST_VALUE
功能描述：返回組中數(shù)據(jù)窗口的第一個(gè)值。
SAMPLE：下面例子計(jì)算按部門分區(qū)按薪水排序的數(shù)據(jù)窗口的第一個(gè)值對(duì)應(yīng)的名字，如果薪水的第一個(gè)值有多個(gè)，則從多個(gè)對(duì)應(yīng)的名字中取缺省排序的第一個(gè)名字
SELECT department_id, last_name, salary, FIRST_VALUE(last_name)
OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal
FROM employees
WHERE department_id in(20,30);
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------------------- ---------- --------------
20 Fay 6000 Fay
20 Hartstein 13000 Fay
30 Colmenares 2500 Colmenares
30 Himuro 2600 Colmenares
30 Tobias 2800 Colmenares
30 Baida 2900 Colmenares
30 Khoo 3100 Colmenares
30 Raphaely 11000 Colmenares
6.LAST
功能描述：從DENSE_RANK返回的集合中取出排在最后面的一個(gè)值的行（可能多行，因?yàn)橹悼赡芟嗟龋?，因此完整的語(yǔ)法需要在開始處加上一個(gè)集合函數(shù)以從中取出記錄
SAMPLE：下面例子中DENSE_RANK按部門分區(qū)，再按傭金commission_pct排序，F(xiàn)IRST取出傭金最低的對(duì)應(yīng)的所有行，然后前面的MAX函數(shù)從這個(gè)集合中取出薪水最低的值；LAST取出傭金最高的對(duì)應(yīng)的所有行，然后前面的MIN函數(shù)從這個(gè)集合中取出薪水最高的值
SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id in (20,80)
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID SALARY Worst Best
------------------------- ------------- ---------- ---------- ----------
Fay 20 6000 6000 13000
Hartstein 20 13000 6000 13000
Kumar 80 6100 6100 14000
Banda 80 6200 6100 14000
Johnson 80 6200 6100 14000
Ande 80 6400 6100 14000
Lee 80 6800 6100 14000
Tuvault 80 7000 6100 14000
Sewall 80 7000 6100 14000
Marvins 80 7200 6100 14000
Bates 80 7300 6100 14000
7.LAST_VALUE
功能描述：返回組中數(shù)據(jù)窗口的最后一個(gè)值。
SAMPLE：下面例子計(jì)算按部門分區(qū)按薪水排序的數(shù)據(jù)窗口的最后一個(gè)值對(duì)應(yīng)的名字，如果薪水的最后一個(gè)值有多個(gè)，則從多個(gè)對(duì)應(yīng)的名字中取缺省排序的最后一個(gè)名字
SELECT department_id, last_name, salary, LAST_VALUE(last_name)
OVER(PARTITION BY department_id ORDER BY salary) AS highest_sal
FROM employees
WHERE department_id in(20,30);
DEPARTMENT_ID LAST_NAME SALARY HIGHEST_SAL
------------- ------------------------- ---------- ------------
20 Fay 6000 Fay
20 Hartstein 13000 Hartstein
30 Colmenares 2500 Colmenares
30 Himuro 2600 Himuro
30 Tobias 2800 Tobias
30 Baida 2900 Baida
30 Khoo 3100 Khoo
30 Raphaely 11000 Raphaely
8.LAG
功能描述：可以訪問結(jié)果集中的其它行而不用進(jìn)行自連接。它允許去處理游標(biāo)，就好像游標(biāo)是一個(gè)數(shù)組一樣。在給定組中可參考當(dāng)前行之前的行，這樣就可以從組中與當(dāng)前行一起選擇以前的行。Offset是一個(gè)正整數(shù)，其默認(rèn)值為1，若索引超出窗口的范圍，就返回默認(rèn)值（默認(rèn)返回的是組中第一行），其相反的函數(shù)是LEAD
SAMPLE：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值
SELECT last_name, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';
LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- ---------- ---------- ----------
Khoo 18-5月 -95 3100 0
Tobias 24-7月 -97 2800 3100
Baida 24-12月-97 2900 2800
Himuro 15-11月-98 2600 2900
Colmenares 10-8月 -99 2500 2600
9.LEAD
功能描述：LEAD與LAG相反，LEAD可以訪問組中當(dāng)前行之后的行。Offset是一個(gè)正整數(shù)，其默認(rèn)值為1，若索引超出窗口的范圍，就返回默認(rèn)值（默認(rèn)返回的是組中第一行）
SAMPLE：下面的例子中每行的"NextHired"返回按hire_date排序的下一行的hire_date值
SELECT last_name, hire_date,
LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"
FROM employees WHERE department_id = 30;
LAST_NAME HIRE_DATE NextHired
------------------------- --------- ---------
Raphaely 07-DEC-94 18-MAY-95
Khoo 18-MAY-95 24-JUL-97
Tobias 24-JUL-97 24-DEC-97
Baida 24-DEC-97 15-NOV-98
Himuro 15-NOV-98 10-AUG-99
Colmenares 10-AUG-99
10.SUM
功能描述：該函數(shù)計(jì)算組中表達(dá)式的累積和。
SAMPLE：下例計(jì)算同一經(jīng)理下員工的薪水累積值
SELECT manager_id, last_name, salary,
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in (101,103,108);
MANAGER_ID LAST_NAME SALARY L_CSUM
---------- ------------------------- ---------- ----------
101 Whalen 4400 4400
101 Mavris 6500 10900
101 Baer 10000 20900
101 Greenberg 12000 44900
101 Higgins 12000 44900
103 Lorentz 4200 4200
103 Austin 4800 13800
103 Pataballa 4800 13800
103 Ernst 6000 19800
108 Popp 6900 6900
108 Sciarra 7700 14600
108 Urman 7800 22400
108 Chen 8200 30600
108 Faviet 9000 39600
11.MAX
功能描述：在一個(gè)組中的數(shù)據(jù)窗口中查找表達(dá)式的最大值。
SAMPLE：下面例子中dept_max返回當(dāng)前行所在部門的最大薪水值
SELECT department_id, last_name, salary,
MAX(salary) OVER (PARTITION BY department_id) AS dept_max
FROM employees WHERE department_id in (10,20,30);
DEPARTMENT_ID LAST_NAME SALARY DEPT_MAX
------------- ------------------------- ---------- ----------
10 Whalen 4400 4400
20 Hartstein 13000 13000
20 Fay 6000 13000
30 Raphaely 11000 11000
30 Khoo 3100 11000
30 Baida 2900 11000
30 Tobias 2800 11000
30 Himuro 2600 11000
30 Colmenares 2500 11000
12.MIN
功能描述：在一個(gè)組中的數(shù)據(jù)窗口中查找表達(dá)式的最小值。
SAMPLE：下面例子中dept_min返回當(dāng)前行所在部門的最小薪水值
SELECT department_id, last_name, salary,
MIN(salary) OVER (PARTITION BY department_id) AS dept_min
FROM employees WHERE department_id in (10,20,30);
DEPARTMENT_ID LAST_NAME SALARY DEPT_MIN
------------- ------------------------- ---------- ----------
10 Whalen 4400 4400
20 Hartstein 13000 6000
20 Fay 6000 6000
30 Raphaely 11000 2500
30 Khoo 3100 2500
30 Baida 2900 2500
30 Tobias 2800 2500
30 Himuro 2600 2500
30 Colmenares 2500 2500