The VARIANCE function returns the variance of the column that is specified by a parameter.
Syntax
VARIANCE([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
If the function is used as an analytic function, you must use the full syntax of a window function. The function calculates a set of rows and returns multiple values. If the function is used as an aggregate function, the function aggregates a set of rows and returns only one value. In this case, you do not need to add the OVER keyword.
Parameters
| Parameter | Description |
|---|---|
| DISTINCT | Removes duplicate values from the column and ignores NULL values in the column during the query. |
| UNIQUE | Removes duplicate values from the column and ignores NULL values in the column during the query. |
| ALL | Retains duplicate values in the column and ignores NULL values in the column during the query. Default value: ALL. |
| expr | The data column or expression of the numeric type, character type, date type, or other types. |
| OVER | Uses the OVER clause to define a window for calculation. |
Notice
If you specify the DISTINCT or UNIQUE keyword, order_by_clause and windowing_clause cannot appear in analytic_clause.
Return type
The data of the NUMBER type is returned.
Examples
Examples of the analytic function
The following statements create the employees table and insert data into the table:
CREATE TABLE employees (manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss', '2019-07-11',13500);
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners', '2018-12-01',14000);
INSERT INTO employees VALUES(200, 'Ross', '2019-06-11',13500);
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part', '2018-08-11',14000);
COMMIT;
Execute the following statement to calculate the variance of the salary column:
SELECT last_name, salary, VARIANCE(salary) OVER (ORDER BY hiredate) "Variance"
FROM employees WHERE manager_id = 100 ORDER BY last_name, salary, "Variance";
The following query result is returned:
+-----------+--------+-------------------------------------------+
| LAST_NAME | SALARY | Variance |
+-----------+--------+-------------------------------------------+
| De Haan | 11000 | 22110000 |
| Errazuriz | 1400 | 45000 |
| Hartstein | 14000 | 40200000 |
| Partners | 14000 | 36783000 |
| Raphaely | 1700 | 0 |
| Raphaely | 1700 | 30000 |
| Russell | 13000 | 36318392.85714285714285714285714285714286 |
| Weiss | 13500 | 38991428.57142857142857142857142857142857 |
+-----------+--------+-------------------------------------------+
Examples of the aggregate function
Execute the following statement to calculate the variance of the salary column:
SELECT VARIANCE(salary) FROM employees;
The following query result is returned:
+-----------------------------------------+
| VARIANCE(SALARY) |
+-----------------------------------------+
| 30318181.818181818181818181818181818182 |
+-----------------------------------------+