Computes the population covariance between two numeric expressions. If either one of expressions is NULL - that input row is ignored.
Use this when analyzing an entire population rather than a sample. For covariance of sample see COVAR_SAMP.
Covariance vs Correlation
Covariance and correlation both describe how two variables change together, but they do so in different ways:
-
Covariance measures the direction of the relationship between two variables:
- Positive covariance: variables tend to increase together.
- Negative covariance: one increases as the other decreases.
- Its value is unbounded and depends on the units of the variables, making it hard to interpret on its own.
-
Correlation, specifically Pearson correlation, standardizes the relationship:
- It is the normalized version of covariance, giving a unitless measure between -1 and 1.
- This makes it easier to compare the strength of relationships between different pairs of variables.
For information on correlation see CORR
Syntax
COVAR_POP(<expr1>, <expr2>) [FILTER ([WHERE] <condition>)]
Parameters
| Parameter | Description | Supported input types |
|---|
<expr1> | First numeric expression to use for covariance computation. | DOUBLE PRECISION |
<expr2> | Second numeric expression to use for covariance computation. | DOUBLE PRECISION |
<condition> | An optional boolean expression to filter rows used in aggregation | BOOL |
Return Type
COVAR_POP returns a result of type DOUBLE PRECISION.
Examples
The playstats table contains the following data:
SELECT currentlevel, currentscore, currentspeed FROM playstats ORDER BY currentlevel DESC, currentscore DESC LIMIT 5;
| currentlevel int null | currentscore long null | currentspeed float null |
|---|
| 7 | 501667 | 345 |
| 7 | 501642 | 30 |
| 7 | 501618 | 62 |
| 7 | 501581 | 64 |
| 7 | 501544 | 171 |
Rows: 5Execution time: 4.19ms
currentscore and currentlevel in the playstats table are highly correlated, producing a large covariance value:
SELECT ROUND(COVAR_POP(currentscore::double precision, currentlevel::double precision), 2) AS covariance
FROM playstats;
| covariance double null |
|---|
| 73152.22 |
Rows: 1Execution time: 7.65ms
currentlevel and currentspeed are not correlated, so their covariance is close to zero:
SELECT ROUND(COVAR_POP(currentlevel::double precision, currentspeed::double precision), 4) AS covariance
FROM playstats;
| covariance double null |
|---|
| -0.8091 |
Rows: 1Execution time: 6.90ms
