regr_r2 ​

Calculates the coefficient of determination of a linear regression.

The result describes how much of the variation in y is explained by a straight-line relationship with x. A result of 1 indicates a perfect fit.

Syntax

regr_r2(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

Sxy * Sxy / (Sxx * Syy)

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 value between 0 and 1. No complete pairs or zero variation in x returns NULL. If x varies and y is constant, the result is 1.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.

CREATE TABLE measurements (hour INTEGER, reading INTEGER);
INSERT INTO measurements VALUES (1, 3), (2, 5), (3, 7), (4, 9), (5, NULL), (NULL, 100);

SELECT round(regr_r2(reading, hour), 6) AS result
FROM measurements
FORMAT csv;

1.0

No complete observations

CREATE TABLE incomplete_measurements (hour INTEGER, reading INTEGER);
INSERT INTO incomplete_measurements VALUES (1, NULL), (NULL, 5);

SELECT regr_r2(reading, hour)
FROM incomplete_measurements
FORMAT csv;

\N

Related functions

• regr_count counts the pairs used by the calculation.
• regr_slope and regr_intercept describe the fitted line.
• covar_pop, covar_samp and corr describe the relationship between the variables.