Skip to content
Coordinator DB
View as Markdown
View as Markdown

covar_pop ​

Calculates the population covariance.

Use population covariance when the input contains the entire population you want to describe. The denominator is the number of complete pairs.

Syntax

text
covar_pop(y, x)

Arguments

  • y — The dependent numeric variable.
  • x — The independent numeric variable.

A row contributes only when both arguments are non-NULL. The means and the count in the formulas below use these complete pairs, rather than each column separately.

Calculation

text
Sxy / n

Here n is the number of complete pairs, mean_x and mean_y are their arithmetic means, Sxx and Syy are their centered sums of squares, and Sxy is their centered cross-product sum.

Returned value

A REAL covariance. No complete pairs returns NULL; a single complete pair returns 0.0.

The result is approximate, including when the inputs are exact integers or decimals. Nonfinite inputs return NULL; finite arithmetic overflow raises NUMERIC_OVERFLOW.

Examples

Measurements with a linear relationship

The complete observations below follow reading = 2 * hour + 1. The two incomplete observations are excluded.

sql
CREATE TABLE measurements (hour INTEGER, reading INTEGER);
INSERT INTO measurements VALUES (1, 3), (2, 5), (3, 7), (4, 9), (5, NULL), (NULL, 100);
sql
SELECT round(covar_pop(reading, hour), 6) AS result
FROM measurements
FORMAT csv;
text
2.5

No complete observations

sql
CREATE TABLE incomplete_measurements (hour INTEGER, reading INTEGER);
INSERT INTO incomplete_measurements VALUES (1, NULL), (NULL, 5);
sql
SELECT covar_pop(reading, hour)
FROM incomplete_measurements
FORMAT csv;
text
\N

Related functions