Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-12-2007
11:39 PM

06-12-2007
11:39 PM

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?

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2007
12:31 AM

06-13-2007
12:31 AM

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

;

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

;

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2007
01:44 AM

06-13-2007
01:44 AM

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.

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) ,

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2007
01:58 AM

06-13-2007
01:58 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2007
02:07 AM

06-13-2007
02:07 AM

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.

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

Yes, it work now.

Many thanks.