在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