Purpose
This function calculates the population covariance of a set of pairs of 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 computation. It calculates the covariance for a set of rows and returns multiple values. - When used as an aggregate function, it aggregates the covariance for a set of rows and returns a single value. In this case, you do not need to use the
OVERclause.
Syntax
COVAR_POP(expr1, expr2)[ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| expr1 | The first numeric expression, which is a value of a numeric data type or can be implicitly converted to a numeric data type. |
| expr2 | The second numeric expression, which is a value of a numeric data type or can be implicitly converted to a numeric data type. |
| OVER | Use the OVER clause to define the window for computation. For more information, see Analytic Function Description. |
Note
- The database determines the parameter with the highest numeric precedence, implicitly converts the other parameters to this data type, and returns the result in this data type.
- The positions of the parameters
expr1andexpr2do not affect the result. In other words,COVAR_POP(expr1, expr2)is equal toCOVAR_POP(expr2, expr1). - The calculation formula for the result is:
(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n, wherenis the number of pairs of (expr1,expr2) expressions, and bothexpr1andexpr2are not empty.
Return type
If any parameter is empty, it returns NULL. Otherwise, it returns a value of the NUMBER type.
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 |
+------+------+------+------+
12 rows in set
Example of aggregate function
Calculate the population covariance of the pairs of values 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 analytic function
Group the data by the col1 column and sort the data by the col2 column. Calculate the cumulative population covariance of the pairs of 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