to do this.
I have a table
TABLE1
[Column 1- 2001]
[Column 2- 2002]
[Column 3- 2003]
[Column 4 - 2004]
[Column 5 - 2005]
[Column 6 - 2006]
[Column 7 - Slope]
[2001][2002][2003][2004][2005][2006] [Slope]
[1] [2] [3] [4] [5] [6] [1]
[1.2] [.9] [4] [5] [5.4] [6.2] [?]
Slope is defined as "M" in the equation y=mx+b
I need a way a finding the linear equation that best fits the points so
I can have SQL calculate the slope.
Are there any smart people around that would know how to do this?
thankssql guy123 (stoppal@.hotmail.com) writes:
Quote:
Originally Posted by
This is a real challenge. I hope someone is smart enough to know how
to do this.
>
I have a table
>
TABLE1
[Column 1- 2001]
[Column 2- 2002]
[Column 3- 2003]
[Column 4 - 2004]
[Column 5 - 2005]
[Column 6 - 2006]
[Column 7 - Slope]
>
>
[2001][2002][2003][2004][2005][2006] [Slope]
[1] [2] [3] [4] [5] [6] [1]
[1.2] [.9] [4] [5] [5.4] [6.2] [?]
>
>
Slope is defined as "M" in the equation y=mx+b
>
I need a way a finding the linear equation that best fits the points so
I can have SQL calculate the slope.
>
Are there any smart people around that would know how to do this?
Smart? Either you did learn linear regression in school or you didn't.
I will have to admit that although I taught mathematical statistics as a
student, that I've forgotten the forumulas. But I looked them up in my
statisticcs book:
m = SUM((Xi - AVG(X))*(Yi - AVG(Y)) / SUM(SQR(Xi - AVG(X))
where Xi and Yi are the individual values for the X and Y variables.
For the computation in SQL you can of course not use SUM and AVG since
your table is turned the wrong way. The years should have been columns
instead.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your information. I never learned the actual formula,
only how to click the linear regression button in excel.
Is there a way to transform the table into the correct format? Maybe a
tranverse function in SQL?
thanks
Erland Sommarskog wrote:
Quote:
Originally Posted by
sql guy123 (stoppal@.hotmail.com) writes:
Quote:
Originally Posted by
This is a real challenge. I hope someone is smart enough to know how
to do this.
I have a table
TABLE1
[Column 1- 2001]
[Column 2- 2002]
[Column 3- 2003]
[Column 4 - 2004]
[Column 5 - 2005]
[Column 6 - 2006]
[Column 7 - Slope]
[2001][2002][2003][2004][2005][2006] [Slope]
[1] [2] [3] [4] [5] [6] [1]
[1.2] [.9] [4] [5] [5.4] [6.2] [?]
Slope is defined as "M" in the equation y=mx+b
I need a way a finding the linear equation that best fits the points so
I can have SQL calculate the slope.
Are there any smart people around that would know how to do this?
>
Smart? Either you did learn linear regression in school or you didn't.
>
I will have to admit that although I taught mathematical statistics as a
student, that I've forgotten the forumulas. But I looked them up in my
statisticcs book:
>
m = SUM((Xi - AVG(X))*(Yi - AVG(Y)) / SUM(SQR(Xi - AVG(X))
>
where Xi and Yi are the individual values for the X and Y variables.
>
For the computation in SQL you can of course not use SUM and AVG since
your table is turned the wrong way. The years should have been columns
instead.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||sql guy123 (stoppal@.hotmail.com) writes:
Quote:
Originally Posted by
Thank you for your information. I never learned the actual formula,
only how to click the linear regression button in excel.
>
Is there a way to transform the table into the correct format? Maybe a
tranverse function in SQL?
There is an UNPIVOT operator in SQL 2005. But it's mainly syntactic
sugar, and you can easily unpivot without it:
SELECT year, value = CASE year WHEN 2001 THEN [2001]
WHEN 2002 THEN [2002]
...
END
FROM tbl
CROSS JOIN (SELECT 2001 UNION ALL
SELECT 2002 UNION ALL
...) AS year
My thought, though, was that you would redesign the table on a
permanent basis.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment