corr

Calculates the Pearson correlation coefficient.

Correlation measures the strength and direction of a linear relationship. A value near 1 indicates that both variables rise together; a value near -1 indicates that one falls as the other rises.

Syntax

text

corr(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 / sqrt(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 -1 and 1. Fewer than two complete pairs, or zero variance in either variable, 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(corr(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 corr(reading, hour)
FROM incomplete_measurements
FORMAT csv;

text

\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.

corr ​

corr