oracle常用函数3

105。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 
106。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 

107。SUM 
功能描述：该函数计算组中表达式的累积和。 
SAMPLE：下例计算同一经理下员工的薪水累积值 
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 
108。VAR_POP 
功能描述：（Variance Population）该函数返回非空集合的总体变量（忽略null），VAR_POP进行如下计算： 
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr) 
SAMPLE：下例计算1998年每月销售的累积总体和样本变量（本例在SH用户下运行） 
SELECT t.calendar_month_desc, 
VAR_POP(SUM(s.amount_sold)) 
OVER (ORDER BY t.calendar_month_desc) "Var_Pop", 
VAR_SAMP(SUM(s.amount_sold)) 
OVER (ORDER BY t.calendar_month_desc) "Var_Samp" 
FROM sales s, times t 
WHERE s.time_id = t.time_id AND t.calendar_year = 1998 
GROUP BY t.calendar_month_desc; 
CALENDAR Var_Pop Var_Samp 
-------- ---------- ---------- 
1998-01 0 
1998-02 6.1321E+11 1.2264E+12 
1998-03 4.7058E+11 7.0587E+11 
1998-04 4.6929E+11 6.2572E+11 
1998-05 1.5524E+12 1.9405E+12 
1998-06 2.3711E+12 2.8453E+12 
1998-07 3.7464E+12 4.3708E+12 
1998-08 3.7852E+12 4.3260E+12 
1998-09 3.5753E+12 4.0222E+12 
1998-10 3.4343E+12 3.8159E+12 
1998-11 3.4245E+12 3.7669E+12 
1998-12 4.8937E+12 5.3386E+12 
109。VAR_SAMP 
功能描述：（Variance Sample）该函数返回非空集合的样本变量（忽略null），VAR_POP进行如下计算： 
(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1) 
SAMPLE：下例计算1998年每月销售的累积总体和样本变量 
SELECT t.calendar_month_desc, 
VAR_POP(SUM(s.amount_sold)) 
OVER (ORDER BY t.calendar_month_desc) "Var_Pop", 
VAR_SAMP(SUM(s.amount_sold)) 
OVER (ORDER BY t.calendar_month_desc) "Var_Samp" 
FROM sales s, times t 
WHERE s.time_id = t.time_id AND t.calendar_year = 1998 
GROUP BY t.calendar_month_desc; 
CALENDAR Var_Pop Var_Samp 
-------- ---------- ---------- 
1998-01 0 
1998-02 6.1321E+11 1.2264E+12 
1998-03 4.7058E+11 7.0587E+11 
1998-04 4.6929E+11 6.2572E+11 
1998-05 1.5524E+12 1.9405E+12 
1998-06 2.3711E+12 2.8453E+12 
1998-07 3.7464E+12 4.3708E+12 
1998-08 3.7852E+12 4.3260E+12 
1998-09 3.5753E+12 4.0222E+12 
1998-10 3.4343E+12 3.8159E+12 
1998-11 3.4245E+12 3.7669E+12 
1998-12 4.8937E+12 5.3386E+12 
110。VARIANCE 
功能描述：该函数返回表达式的变量，Oracle计算该变量如下： 
如果表达式中行数为1，则返回0 
如果表达式中行数大于1，则返回VAR_SAMP 
SAMPLE：下例返回部门30按雇佣日期排序的薪水值的累积变化 
SELECT last_name, salary, VARIANCE(salary) 
OVER (ORDER BY hire_date) "Variance" 
FROM employees 
WHERE department_id = 30; 
LAST_NAME SALARY Variance 
------------------------- ---------- ---------- 
Raphaely 11000 0 
Khoo 3100 31205000 
Tobias 2800 21623333.3 
Baida 2900 16283333.3 
Himuro 2600 13317000 
Colmenares 2500 11307000