Purpose
COVAR_POP() returns the population covariance of a set of number pairs. 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
COVAR_POP(expr1, expr2)[ OVER (analytic_clause) ]
Parameters
| Parameter | Description |
|---|---|
| expr1 | The first expression, which is of a numeric data type or a data type that can be implicitly converted to a numeric data type. |
| expr2 | The second expression, which 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
- The database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to this data type, and returns a result of this data type.
- The positions of
expr1andexpr2have no influence over the return result, that is,COVAR_POP(expr1, expr2)is equivalent toCOVAR_POP(expr2, expr1). - The returned result is calculated by using the following formula:
(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n, wherenindicates the number of expression pairs (expr1,expr2).expr1andexpr2cannot be NULL.
Return type
If any argument is NULL, the function returns NULL. Otherwise, the return type is NUMBER.
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 |
+------+------+------+------+
12 rows in set
Example of an aggregate function
Calculate the population covariance of the number pairs in the col3 and col4 columns.
obclient> SELECT COVAR_POP(col3,col4) FROM tbl1;
+----------------------+
| COVAR_POP(COL3,COL4) |
+----------------------+
| -.75 |
+----------------------+
1 row in set
Example of an analytic function
Group data by the col1 column. Sort data by the col2 column. Calculate the cumulative population covariance of the values in the col3 and col4 columns.
obclient> SELECT col1,col3,col4,
COVAR_POP(col3,col4) OVER(PARTITION BY col1 ORDER BY col2) "COVAR_POP"
FROM tbl1;
+------+------+------+-------------------------------------------+
| COL1 | COL3 | COL4 | COVAR_POP |
+------+------+------+-------------------------------------------+
| 1 | 8 | 12 | 0 |
| 1 | 10 | 15 | 1.5 |
| 1 | 11 | 16 | 2.111111111111111111111111111111111111 |
| 2 | 9 | 14 | 0 |
| 2 | 10 | 15 | .25 |
| 2 | 8 | 13 | .6666666666666666666666666666666666666667 |
| 2 | 11 | 16 | 1.25 |
| 3 | 8 | 18 | 0 |
| 3 | 9 | 16 | -.5 |
| 3 | 10 | 15 | -1 |
| 3 | 11 | 12 | -2.375 |
| 3 | 12 | 10 | -4 |
+------+------+------+-------------------------------------------+
12 rows in set
