Purpose
This function returns the sample variance of a set of numeric values, ignoring NULL values. You can use this function as an aggregate or analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for the calculation. It calculates the variance for a set of rows and returns multiple values. - When used as an aggregate function, it calculates the variance for a set of rows and returns a single value. In this case, the
OVERclause is not required.
Syntax
VAR_SAMP(expr) [ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| expr | Specifies the numeric expression to be calculated. It can be a numeric data type or a value that can be implicitly converted to a numeric data type. |
| OVER | Defines the window for the calculation using the OVER clause. For more information, see Analytic Function Description. |
Note
If the function is applied to an empty set, it returns NULL. The calculation formula is: (SUM(expr - (SUM(expr) / COUNT(expr)))²) /(COUNT(expr) - 1).
Return type
The same data type as the parameter expr or NULL.
Examples
A table named 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
Aggregate function example
Calculate the sample variance of column col4.
obclient> SELECT VAR_SAMP(col4) FROM tbl1;
+----------------+
| VAR_SAMP(COL4) |
+----------------+
| 6 |
+----------------+
1 row in set
Analytic function example
Sort column col2 in ascending order and calculate the cumulative sample variance of column 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