Skip to content
Coordinator DB
View as Markdown
View as Markdown

covar_samp ​

Calculates the sample covariance.

Use sample covariance when the input is a sample from a larger population. The denominator is one less than the number of complete pairs.

Syntax

text
covar_samp(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 - 1)

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. Fewer than two complete pairs returns NULL.

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_samp(reading, hour), 6) AS result
FROM measurements
FORMAT csv;
text
3.333333

No complete observations

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

Related functions