SQL Reference - REGR

REGR_INTERCEPT

REGR_INTERCEPT is a mathematical function provided by SQL to compute the y-intercept of the linear regression line fitted to the provided set of data. The result represents a statistical correlation between two variables. This function comes handy when you want to perform statistical analysis within your SQL database environment.

REGR_INTERCEPT(Y, X)

y: This is the dependent variable in the regression equation. It denotes the response or the output value that the equation predicts.
x: This is the independent variable in the regression equation. It denotes the predictor or the input value that the equation uses to forecast the dependent variable Y.

Example

SELECT REGR_INTERCEPT(y, x) as intercept
FROM (SELECT 1 as x, 3 as y FROM dual
      UNION ALL SELECT 2 as x, 5 as y FROM dual
      UNION ALL SELECT 3 as x, 7 as y FROM dual)

Output

INTERCEPT
-----------
1

Explanation

The REGR_INTERCEPT function computes the y-intercept of the least-squares-fit linear equation determined by the (x, y) pairs. In the code above, (1, 3), (2, 5), and (3, 7) pairs are provided. These points all lie on the line y = 2x + 1, hence intercept of the line which is the expected output returns 1.

REGR_INTERCEPT(Y, X)

y: The dependent variable in the linear regression model. This variable’s values are predicted from the independent variable. Y can be any numerical type that PostgreSQL recognizes, including integers, floating point numbers, and decimals.
x: The independent variable in the linear regression model. Changes in this variable’s values are believed to explain changes in the dependent variable. Similar to Y, X can also be any numerical type recognized by PostgreSQL.

Example

CREATE TABLE data (x float, y float);
INSERT INTO data VALUES (1, 3), (2, 5), (3, 7), (4, 9);
SELECT REGR_INTERCEPT(y, x) FROM data;

Output

 regr_intercept
----------------
              1

Explanation

In this example, the function REGR_INTERCEPT(y, x) is used to compute the y-intercept of the least-squares regression line fitted to the points (x, y). The resulting output shows that the line crosses the y-axis at 1.