Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-01 |
|
|
View PDF |
Syntax
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictionsPurpose
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.
See Also:
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversionThis function is same as the square root of the VAR_SAMP
function. When VAR_SAMP
returns null, this function returns null.
See Also:
"About SQL Expressions" for information on valid forms of expr
Aggregate Example
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 ORDER BY department_id, last_name, hire_date, salary, cum_sdev; DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV ------------- --------------- --------- ---------- ---------- 10 Whalen 17-SEP-87 4400 20 Fay 17-AUG-97 6000 4949.74747 20 Hartstein 17-FEB-96 13000 30 Baida 24-DEC-97 2900 4035.26125 30 Colmenares 10-AUG-99 2500 3362.58829 30 Himuro 15-NOV-98 2600 3649.2465 30 Khoo 18-MAY-95 3100 5586.14357 30 Raphaely 07-DEC-94 11000 . . . 100 Greenberg 17-AUG-94 12000 2121.32034 100 Popp 07-DEC-99 6900 1801.11077 100 Sciarra 30-SEP-97 7700 1925.91969 100 Urman 07-MAR-98 7800 1785.49713 110 Gietz 07-JUN-94 8300 2616.29509 110 Higgins 07-JUN-94 12000 Grant 24-MAY-99 7000