Regression: Predict Next Value

Analytics
Enthusiast

Regression: Predict Next Value

Dear who try before,

If I create a regression by using,
Y=aX+c
then a=Regr_slope and c=Regr_Intercept

Did SQL allow me to predict the Y value if X value given?
How to write the SQL?

4 REPLIES
Enthusiast

Re: Regression: Predict Next Value

There are functions already available to do that .......

to give a sample ...

SELECT X, A*X + C Y
FROM
(
SELECT REGR_SLOPE(Y, X) A, REGR_INTERCEPT(Y, X) C
FROM
REGRESSIONDATA
) REGRVALS(A, C), REGRESSIONDATA
ORDER BY 1
;
Enthusiast

Re: Regression: Predict Next Value

Thanks.

But I had a question, why there are multiple output? Something wrong to my SQL?

Sel 7,A*Prpd_Vchr_Typ_Cd+C Y
FROM (
Sel Regr_Slope (Tot_Reload_Amt , Prpd_Vchr_Typ_Cd) A
,Regr_Intercept (Tot_Reload_Amt , Prpd_Vchr_Typ_Cd) C
From ) REGRVALS (A , C) ,
Group By 1,2

Result:
7 Y
7 -0.33
7 2.91
7 4.54
7 6.16
7 9.40
7 11.03

X=Prpd_Vchr_Typ_Cd
Y=Tot_Reload_Amt

Many thanks for helping.
Enthusiast

Re: Regression: Predict Next Value

Is this the actual query ? I see a few syntax issues (like no tablename given after the from in the inner query - derived table, there's a comma after the derived table name, etc ...)

Anyways, here's an example of what you are trying to accomplish.

Here X = 7 is the input.

SELECT 7 X, A*X + C Y
FROM
(
SELECT REGR_SLOPE(Y, X) A, REGR_INTERCEPT(Y, X) C
FROM
REGRESSIONDATA
) REGRVALS(A, C);

The inner query returns only one row. And so the outer query's result will also be one record.
Enthusiast

Re: Regression: Predict Next Value

Thanks!
Yes, this is a actual query, to respect the company, I did hide the table name.

Yes, it work now.

Many thanks.