Purpose
VAR_POP() returns the population variance for a group of values (with NULL values ignored). You can use this function as an aggregate or analytic function.
Note
- When you use this function as an analytic function, use the
OVERclause to define a window over the data on which the function operates. The function operates on a group of rows to return a list of values. - When you use this function as an aggregate function, the function operates on a set of rows and returns a single value. You do not need to add the
OVERclause.
Syntax
VAR_POP(expr) [ OVER (analytic_clause) ]
Parameters
| Parameter | Description |
|---|---|
| expr | The numeric expression that participates in the calculation. This parameter is of a numeric data type or a data type that can be implicitly converted to a numeric data type. |
| OVER | You can use the OVER clause to define a window over the data on which the function operates. For more information, see Analytic functions. |
Note
If this function is applied to an empty set, NULL is returned. The returned result is calculated by using the following formula: SUM((expr - (SUM(expr) / COUNT(expr)))²)/ COUNT(expr)
Return type
The return type is the same as the data type of the expr parameter, or this function returns NULL.
Examples
The table tbl1 has been created.
obclient> SELECT * FROM tbl1;
+------+------+------+------+
| COL1 | COL2 | COL3 | COL4 |
+------+------+------+------+
| 1 | A1 | 8 | 12 |
| 1 | A2 | 10 | 15 |
| 1 | A3 | 11 | 16 |
| 2 | B1 | 9 | 14 |
| 2 | B2 | 10 | 15 |
| 2 | B3 | 8 | 13 |
| 2 | B4 | 11 | 16 |
| 3 | C1 | 8 | 18 |
| 3 | C2 | 9 | 16 |
| 3 | C3 | 10 | 15 |
| 3 | C4 | 11 | 12 |
| 3 | C5 | 12 | 10 |
| 4 | d1 | 8 | NULL |
| 4 | d1 | 9 | 10 |
| 4 | d1 | 10 | NULL |
+------+------+------+------+
15 rows in set
Example of an aggregate function
Calculate the population variance of the col4 column.
obclient> SELECT VAR_POP(col4) FROM tbl1;
+------------------------------------------+
| VAR_POP(COL4) |
+------------------------------------------+
| 5.53846153846153846153846153846153846154 |
+------------------------------------------+
1 row in set
Example of an analytic function
Group data by the col2 column in ascending order and obtain the cumulative population variance of col4.
obclient> SELECT col1,col2,col4,VAR_POP(col4) OVER(ORDER BY col2) "VAR_POP" FROM tbl1;
+------+------+------+------------------------------------------+
| COL1 | COL2 | COL4 | VAR_POP |
+------+------+------+------------------------------------------+
| 1 | A1 | 12 | 0 |
| 1 | A2 | 15 | 2.25 |
| 1 | A3 | 16 | 2.888888888888888888888888888888888889 |
| 2 | B1 | 14 | 2.1875 |
| 2 | B2 | 15 | 1.84 |
| 2 | B3 | 13 | 1.8055555555555555555555555555555555555 |
| 2 | B4 | 16 | 1.95918367346938775510204081632653061229 |
| 3 | C1 | 18 | 3.109375 |
| 3 | C2 | 16 | 2.88888888888888888888888888888888888889 |
| 3 | C3 | 15 | 2.6 |
| 3 | C4 | 12 | 3.10743801652892561983471074380165289255 |
| 3 | C5 | 10 | 4.55555555555555555555555555555555555558 |
| 4 | d1 | NULL | 5.53846153846153846153846153846153846154 |
| 4 | d1 | 10 | 5.53846153846153846153846153846153846154 |
| 4 | d1 | NULL | 5.53846153846153846153846153846153846154 |
+------+------+------+------------------------------------------+
15 rows in set