Skip to content
Coordinator DB
View as Markdown
View as Markdown

regr_intercept ​

Calculates the intercept of a least-squares regression line.

For a fitted line y = slope * x + intercept, the intercept is the predicted value of y when x is zero.

Syntax

text
regr_intercept(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
mean_y - regr_slope(y, 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 intercept as REAL. Fewer than two complete pairs, or zero variation in x, 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(regr_intercept(reading, hour), 6) AS result
FROM measurements
FORMAT csv;
text
1.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_intercept(reading, hour)
FROM incomplete_measurements
FORMAT csv;
text
\N

Related functions