Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
variance::=
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions |
VARIANCE
returns variance of expr
. You can use it as an aggregate or analytic function.
Oracle calculates the variance of expr
as follows:
If you specify DISTINCT
, then you can specify only the query_partition_clause
of the analytic_clause
. The order_by_clause
and windowing_clause
are not allowed.
See Also:
|
The following example calculates the variance of all salaries in the sample employees
table:
SELECT VARIANCE(salary) "Variance" FROM employees; Variance ---------- 15283140.5
The query returns the cumulative variance of salary values in Department 30 ordered by hire date.
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