Purpose
This function calculates the population covariance of a set of numerical pairs. 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 calculation. It calculates the covariance for a set of rows and returns multiple values. - When used as an aggregate function, it calculates 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 numerical expression, which is of a numeric data type or can be implicitly converted to a numeric data type. |
| expr2 | The second numerical expression, which is 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 calculation. 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 parameters
expr1andexpr2do not affect the result, that is,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 (expr1, expr2) pairs, and both expr1 and expr2 are not empty.
Return type
If any parameter is empty, it returns NULL. Otherwise, it returns a NUMBER value.
Examples
Assume that 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 |
+------+------+------+------+
12 rows in set
Aggregate function example
Calculate the population covariance of the numerical pairs in columns col3 and col4.
obclient> SELECT COVAR_POP(col3,col4) FROM tbl1;
+----------------------+
| COVAR_POP(COL3,COL4) |
+----------------------+
| -.75 |
+----------------------+
1 row in set
Analytic function example
Group by column col1 and sort by column col2, then calculate the cumulative population covariance of the numerical pairs in columns col3 and col4.
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