Skip to content
Coordinator DB
View as Markdown
View as Markdown

regr_sxy ​

Calculates the sum of products of centered input pairs.

Dividing this sum by the pair count gives population covariance. Dividing by one less than the pair count gives sample covariance.

Syntax

text
regr_sxy(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 = sum((x - mean_x) * (y - mean_y))

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

The centered cross-product sum as REAL. No complete pairs returns NULL; one 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(regr_sxy(reading, hour), 6) AS result
FROM measurements
FORMAT csv;
text
10.0

No complete observations

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

Related functions