Skip to content
Coordinator DB
View as Markdown
View as Markdown

regr_avgy ​

Calculates the mean of the dependent variable over complete pairs.

Rows with a missing x are excluded even when y is present. Consequently, this function can differ from avg(y) on the same table.

Syntax

text
regr_avgy(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
sum(y) / 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

The paired mean of y as REAL. No 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(regr_avgy(reading, hour), 6) AS result
FROM measurements
FORMAT csv;
text
6.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_avgy(reading, hour)
FROM incomplete_measurements
FORMAT csv;
text
\N

Related functions