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

在SQL中经常需要计算数据的趋势,如下表中,ID为123在1-5月的通话费。现在要预测6月份的通话费或者说需要观察它的变化趋势。对于这些数据使用回归分析 ,如果得到的直线的斜率大于零,则其话费呈增长的趋势;如果直线的斜率小于零,则这5个月的话费呈减小的趋势。

ID

1月话费

2月话费

3月话费

4月话费

5月话费

123

54

41

61

73

68

 

在excel 中作直线拟合,得到如下图。

如果以直线y=ax+b来拟合这些数据,则真实值与拟合值的误差为。现在目标是使得误差的平方和最小,即最小。

对上式求导数,得

解得

或者

在SQL中实现如下。

先将数据插入到一个表中。

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

继而计算a的值,b值同理可得。

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