VARIANCE
Purpose
This function returns the variance of expr within a window or group of rows. This equates to the following formula:
Syntax
variance::=
Usage Notes
- With DISTINCT, the function considers duplicate values of expr only once. It considers all occurrences with ALL. If the function contains neither ALL nor DISTINCT, the default is ALL.
- If a window or group has only one row, then the result of this function is 0.
Examples
Aggregate Function
Result
DEPARTMENT | VARIANCE |
ACCOUNTS | 1.125e+08 |
HR | 2.0625e+08 |
Analytic Function
SELECT
id, department, hire_date, current_salary,
VARIANCE(current_salary) OVER (PARTITION BY department ORDER BY hire_date) VARIANCE
FROM employee_table ORDER BY department, hire_date;
Result
ID | DEPARTMENT | HIRE_DATE | CURRENT_SALARY | VARIANCE |
2005 | ACCOUNTS | 2013-01-01 | 80000 | 0 |
2003 | ACCOUNTS | 2015-07-01 | 80000 | 0 |
2002 | ACCOUNTS | 2017-01-01 | 65000 | 56250000 |
2004 | ACCOUNTS | 2017-01-01 | 70000 | 56250000 |
2001 | ACCOUNTS | 2018-07-01 | 55000 | 1.125e+08 |
1003 | HR | 2014-01-01 | 90000 | 0 |
1002 | HR | 2016-01-01 | 70000 | 1.3333333e+08 |
1004 | HR | 2016-01-01 | 70000 | 1.3333333e+08 |
1001 | HR | 2018-01-01 | 55000 | 2.0625e+08 |