Power Regression

I need to write some SQL to do a power regression for a trendline. I have 2 columns of data which represent my X, Y data and all I'm after is the a and the b for the function y=ax^b. Has anyone ran into this before? I know SSAS has a linear regression function but my data really only fits the power model.

I solved this guy by writing a CLR stored procedure that does the regression. I must say I have been immensely happy with both CTE's and CLR stored procedures.

|||You can do a least squares log-log regression on your data. Here's an example that adapts the least squares example at http://www.users.drew.edu/skass/sql/LeastSquares.sql.txt to do the log-log regression. Note that this may not be the precise definition of "best fit" you need, if a linear fit of the log-log data is not your objective. data. create table Bob ( i int identity(1,1) primary key, x float, y float ) declare @.j float set @.j = 1 while @.j < 100 begin set @.j = @.j + rand(checksum(newid())) insert into Bob select @.j, (3.27+rand())*power(@.j,5+rand()/3) end; declare @.b float, @.log_a float select @.b = (count(x)*sum(x*y)-sum(x)*sum(y)) /(count(x)*sum(x*x) - sum(x)*sum(x)), @.log_a = (sum(y)*sum(x*x) - sum(x)*sum(x*y)) /(count(x)*sum(x*x) - sum(x)*sum(x)) from ( select log(x) as x, log(y) as y from Bob ) as Bob select 'y = '+convert(varchar,exp(@.log_a))+'*x^'+convert(varchar,@.b) select x, y, exp(@.log_a)*power(x,@.b) as y_predicted from Bob go drop table Bob akula@.discussions.microsoft.com wrote:
> I need to write some SQL to do a power regression for a trendline. I
> have 2 columns of data which represent my X, Y data and all I'm after is
> the a and the b for the function y=ax^b. Has anyone ran into this
> before? I know SSAS has a linear regression function but my data
> really only fits the power model.
>
>|||Sorry - this is a better repro: create table Bob ( i int identity(1,1) primary key, x float, y float ) declare @.j float set @.j = 1 while @.j < 100 begin set @.j = @.j + rand(checksum(newid())) insert into Bob select @.j, (3.27+rand())*power(@.j,5+rand()/3) end; declare @.b float, @.log_a float select @.b = (count(x)*sum(x*y)-sum(x)*sum(y)) /(count(x)*sum(x*x) - sum(x)*sum(x)), @.log_a = (sum(y)*sum(x*x) - sum(x)*sum(x*y)) /(count(x)*sum(x*x) - sum(x)*sum(x)) from ( select log(x) as x, log(y) as y from Bob ) as Bob select 'y = '+convert(varchar,exp(@.log_a))+'*x^'+convert(varchar,@.b) select x, y, exp(@.log_a)*power(x,@.b) as y_predicted from Bob go drop table Bob Steve Kass Drew University akula@.discussions.microsoft.com wrote:
> I need to write some SQL to do a power regression for a trendline. I
> have 2 columns of data which represent my X, Y data and all I'm after is
> the a and the b for the function y=ax^b. Has anyone ran into this
> before? I know SSAS has a linear regression function but my data
> really only fits the power model.
>
>sql