How to do a linear regression in postgresql?

Question

In a regression Y=aX+b, regr_intercept(Y, X) equals "b" and rregr_slope(Y, X) equals "a"?

Answer 1

You have not supplied much details but here you go.

Regression

A regression line is simply a line y = ax + b that is able to compute an output variable y for an input variable x. A line can be described by two parameters, also called coefficients:

• the slope a

• the intercept b

Finding Slope & Intercept

Suppose you have two numeric columns, Y and X populated with the desired X and Y

CREATE TABLE foo(
 id serial PRIMARY KEY,
 X integer NOT NULL,
 Y integer NOT NULL
);
INSERT INTO foo VALUES (0,10,3);
INSERT INTO foo VALUES (1,20,5);

You can find slope as follows.

SELECT regr_slope(y, x) slope FROM foo;
SELECT regr_intercept(y, x) intercept FROM foo;

Results of query:

slope: 0.2

intercept: 1

SQL Fiddle