Purpose
You can call this function to return the sample 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_SAMP(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) - 1)
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 sample variance of the col4 column.
obclient> SELECT VAR_SAMP(col4) FROM tbl1;
+----------------+
VAR_SAMP(COL4)
+----------------+
6
+----------------+
1 row in set
Analytic example
Group data by the col2 column in ascending order and obtain the cumulative sample variance of col4.
obclient> SELECT col1,col2,col4,VAR_SAMP(col4) OVER(ORDER BY col2) "VAR_POP" FROM tbl1;
+------+------+------+------------------------------------------+
COL1 COL2 COL4 VAR_POP
+------+------+------+------------------------------------------+
1 A1 12 NULL
1 A2 15 4.5
1 A3 16 4.3333333333333333333333333333333333335
2 B1 14 2.91666666666666666666666666666666666667
2 B2 15 2.3
2 B3 13 2.1666666666666666666666666666666666666
2 B4 16 2.28571428571428571428571428571428571433
3 C1 18 3.55357142857142857142857142857142857143
3 C2 16 3.25
3 C3 15 2.88888888888888888888888888888888888889
3 C4 12 3.4181818181818181818181818181818181818
3 C5 10 4.969696969696969696969696969696969697
4 d1 NULL 6
4 d1 10 6
4 d1 NULL 6
+------+------+------+------------------------------------------+
15 rows in set