## 在SQL中利用最小二乘计算回归系数

 ID 1月话费 2月话费 3月话费 4月话费 5月话费 123 54 41 61 73 68

Declare @ID varchar(50),@count1 int,@count2 int,@count3 int,@count4 int,@count5 int;

Declare @BillTable table(CustomerID varchar(50),x int,Count int);

Declare Bill_Cursor Cursor for

select ID, 1月话费, 2月话费, 3月话费, 4月话费, 5月话费

from 话费清单

open Bill_Cursor;

fetch Next from Bill_Cursor into @ID,@count1,@count2,@count3,@count4,@count5;

while @@FETCH_STATUS=0

begin

if (@count1 is not null) insert @BillTable values (@ID,1,@count1) else insert @BillTable values (@ID,null,@count1)

if (@count2 is not null) insert @BillTable values (@ID,2,@count2) else insert @BillTable values (@ID,null,@count2)

if (@count3 is not null) insert @BillTable values (@ID,3,@count3) else insert @BillTable values (@ID,null,@count3)

if (@count4 is not null) insert @BillTable values (@ID,4,@count4) else insert @BillTable values (@ID,null,@count4)

if (@count5 is not null) insert @BillTable values (@ID,5,@count5) else insert @BillTable values (@ID,null,@count5)

fetch Next from Bill_Cursor into @ID,@count1,@count2,@count3,@count4,@count5;

end

close Bill_Cursor;

deallocate Bill_Cursor;

select * into BillTable from @BillTable

select CustomerID, a=case when (count(Count)*sum(x*x)-sum(x)*sum(x))!=0

then (count(Count)*sum(x*Count) - Sum(x)*sum(Count))*1.0 /(count(Count)*sum(x*x)-sum(x)*sum(x))

else null

end

from BillTable

group by CustomerID

posted on 2009-05-22 18:25 zgw21cn 阅读(...) 评论(...) 编辑 收藏

• 随笔 - 54
• 文章 - 0
• 评论 - 63
• 引用 - 0