Purpose
You can call this function to return the population variance for a group of values (with NULL values being ignored). You can use it as an aggregate or analytic function.
Note
- When you use it 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 it 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 | Specifies the numeric expression that participates in the calculation. This parameter is of the numeric data type or can be implicitly converted to the 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 about analytic functions, see Analytic functions. |
Note
If this function is applied to an empty set,
NULLis returned. The returned result is calculated by using the following formula:SUM((expr - (SUM(expr) / COUNT(expr)))²)/ COUNT(expr)
Return types
This function returns the same value type as the expr parameter or returns NULL.
Examples
The tbl1 table 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
Aggregate example
Calculate the population variance of the col4 column.
obclient> SELECT VAR_POP(col4) FROM tbl1;
+------------------------------------------+
VAR_POP(COL4)
+------------------------------------------+
5.53846153846153846153846153846153846154
+------------------------------------------+
1 row in set
Analytic example
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