COVAR_POP Function

covar_pop(y, x)

Description

The COVAR_POP function computes the population covariance between two numeric columns. Covariance measures the degree to which two variables change together, and is a statistical measure of variable correlation.

Parameters

• y: A numeric expression serving as the dependent variable. Must be a numeric type that can be cast to DOUBLE.
• x: A numeric expression serving as the independent variable. Must be a numeric type that can be cast to DOUBLE.

Return Type

• Returns a DOUBLE value representing the population covariance.
  • Positive value: x and y tend to change in the same direction (positive correlation)
  • Negative value: x and y tend to change in opposite directions (negative correlation)
  • Close to 0: weak correlation between variables

Notes

• During computation, NULL values are ignored.
• If there are fewer than 1 valid data point, returns NULL.
• Population covariance uses the formula with denominator n, whereas sample covariance uses (n-1), so population covariance is typically smaller than sample covariance.

Examples

1. Basic usage: compute population covariance

SELECT covar_pop(c1, c2)
FROM VALUES (1, 1), (2, 2), (2, 2), (3, 3) AS tab(c1, c2);
+-------------------+
| covar_pop(c1, c2) |
+-------------------+
| 0.5               |
+-------------------+

2. Comparison with sample covariance

SELECT covar_pop(c1, c2), covar_samp(c1, c2)
FROM VALUES (1, 1), (2, 2), (2, 2), (3, 3) AS tab(c1, c2);
+-------------------+--------------------+
| covar_pop(c1, c2) | covar_samp(c1, c2) |
+-------------------+--------------------+
| 0.5               | 0.6666666666666666 |
+-------------------+--------------------+

3. Perfect positive correlation data

SELECT covar_pop(x, y)
FROM VALUES (1, 2), (2, 4), (3, 6), (4, 8) AS t(x, y);
+------------------+
| covar_pop(x, y)  |
+------------------+
| 2.5              |
+------------------+

4. Perfect negative correlation data

SELECT covar_pop(x, y)
FROM VALUES (1, 8), (2, 6), (3, 4), (4, 2) AS t(x, y);
+------------------+
| covar_pop(x, y)  |
+------------------+
| -2.5             |
+------------------+

5. Uncorrelated data

SELECT covar_pop(x, y)
FROM VALUES (1, 5), (2, 5), (3, 5), (4, 5) AS t(x, y);
+------------------+
| covar_pop(x, y)  |
+------------------+
| 0.0              |
+------------------+