Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
Purpose
STDDEV_SAMP
computes the cumulative sample standard deviation and returns the square root of the sample variance. You can use it as both an aggregate and analytic function.
This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
This function is same as the square root of the VAR_SAMP
function. When VAR_SAMP
returns null, this function returns null.
Aggregate Example
Please refer to the aggregate example for STDDEV_POP.
Analytic Example
The following example returns the sample standard deviation of salaries in the employees
table by department:
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; DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV ------------- --------------- --------- ---------- ---------- 10 Whalen 17-SEP-87 4400 20 Hartstein 17-FEB-96 13000 20 Goyal 17-AUG-97 6000 4949.74747 30 Raphaely 07-DEC-94 11000 30 Khoo 18-MAY-95 3100 5586.14357 30 Tobias 24-JUL-97 2800 4650.0896 30 Baida 24-DEC-97 2900 4035.26125 . . . 100 Chen 28-SEP-97 8200 2003.33056 100 Sciarra 30-SEP-97 7700 1925.91969 100 Urman 07-MAR-98 7800 1785.49713 100 Popp 07-DEC-99 6900 1801.11077 110 Higgens 07-JUN-94 12000 110 Gietz 07-JUN-94 8300 2616.29509