ROW_NUMBER
功能描述：返回有序組中一行的偏移量，從而可用於按特定標準排序的行號。
SAMPLE：下例返回每個員工再在每個部門中按員工號排序後的順序號

SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees
WHERE department_id < 50;

DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1

STDDEV
功能描述：計算當前行關於組的標準偏離。（Standard Deviation）
SAMPLE：下例返回部門30按僱傭日期排序的薪水值的累積標準偏離

SELECT last_name, hire_date,salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30;

LAST_NAME HIRE_DATE SALARY StdDev
------------------------- ---------- ---------- ----------
Raphaely 07-12月-94 11000 0
Khoo 18-5月 -95 3100 5586.14357
Tobias 24-7月 -97 2800 4650.0896
Baida 24-12月-97 2900 4035.26125
Himuro 15-11月-98 2600 3649.2465
Colmenares 10-8月 -99 2500 3362.58829

STDDEV_POP
功能描述：該函式計算總體標準偏離，並返回總體變數的平方根，其返回值與VAR_POP函式的平方根相同。（Standard Deviation－Population）
SAMPLE：下例返回部門20、30、60的薪水值的總體標準偏差

SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
WHERE department_id in (20,30,60);

DEPARTMENT_ID LAST_NAME SALARY POP_STD
------------- ------------------------- ---------- ----------
20 Hartstein 13000 3500
20 Fay 6000 3500
30 Raphaely 11000 3069.6091
30 Khoo 3100 3069.6091
30 Baida 2900 3069.6091
30 Colmenares 2500 3069.6091
30 Himuro 2600 3069.6091
30 Tobias 2800 3069.6091
60 Hunold 9000 1722.32401
60 Ernst 6000 1722.32401
60 Austin 4800 1722.32401
60 Pataballa 4800 1722.32401
60 Lorentz 4200 1722.32401

STDDEV_SAMP
功能描述：該函式計算累積樣本標準偏離，並返回總體變數的平方根，其返回值與VAR_POP函式的平方根相同。（Standard Deviation－Sample）
SAMPLE：下例返回部門20、30、60的薪水值的樣本標準偏差

SELECT department_id, last_name, hire_date, salary,
STDDEV_SAMP(salary) OVER
(PARTITION BY department_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees
WHERE department_id in (20,30,60);

DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV
------------- ------------------------- ---------- ---------- ----------
20 Hartstein 17-2月 -96 13000
20 Fay 17-8月 -97 6000 4949.74747
30 Raphaely 07-12月-94 11000
30 Khoo 18-5月 -95 3100 5586.14357
30 Tobias 24-7月 -97 2800 4650.0896
30 Baida 24-12月-97 2900 4035.26125
30 Himuro 15-11月-98 2600 3649.2465
30 Colmenares 10-8月 -99 2500 3362.58829
60 Hunold 03-1月 -90 9000
60 Ernst 21-5月 -91 6000 2121.32034
60 Austin 25-6月 -97 4800 2163.33077
60 Pataballa 05-2月 -98 4800 1982.42276
60 Lorentz 07-2月 -99 4200 1925.61678[@more@]

Oracle 9i 分析函式參考手冊(7)

相關文章