Purpose
VAR_SAMP() returns the sample variance for a group of values (with NULLs 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_SAMP(expr) [ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| expr | The numeric expression that participates in the calculation. It 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) - 1).
Return type
The return type is the same as the data type of expr, 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 sample variance of the col4 column.
obclient> SELECT VAR_SAMP(col4) FROM tbl1;
+----------------+
| VAR_SAMP(COL4) |
+----------------+
| 6 |
+----------------+
1 row in set
Example of an analytic function
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