Skip to content
Coordinator DB
View as Markdown
View as Markdown

regr_sxx ​

Calculates the sum of squared deviations of the independent variable.

This is the denominator used by regr_slope. It is zero when every admitted x value is the same.

Syntax

text
regr_sxx(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
Sxx = sum((x - mean_x) * (x - mean_x))

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 nonnegative centered sum of squares 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_sxx(reading, hour), 6) AS result
FROM measurements
FORMAT csv;
text
5.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_sxx(reading, hour)
FROM incomplete_measurements
FORMAT csv;
text
\N

Related functions