asked
How to write a linear regression stored procedure in SQL Server
Linear algorithm:- Step 1:- Select Object service name from [OBJECT_SERVICE] table and Stored into OOS Step 2:- Select month and count from [OBJECT_MON_ACTUALS] table Step 3:- Select Object Month (Xi) value stored into Xi Step 4:- Select the value object Month count stored into Yi Step5:- Calculate Square of Object Month stored into X^2 X^2 = square (Xi) Step 6:- Calculate the value Object Month multiply with (1/Object Month Count) stored in to XiYi XiYi = Xi *(Yi) Step 7:- Calculate the value square of 1 divided by Object Month Count stored into Yi^2 Yi^2 = Square (Yi). Step 8:- Calculate the value 1/ select total no of records in current object of service in [OBJECT_MON_ACTUALS] table and Stored into 1/n. Step 9: - Calculate Total sum of the value of each column Xi, Yi, X^2, XiYi, Yi^2, 1/n Step 10:- Calculate Method 1 Slope value: - = (Sum(XiYi)-(1/n *Sum(Xi)*Sum(Yi))/ (Sum(X^2)-(1/n*Square(Sum(Xi)))) Step 11:- Calculate Method 1 Intercept value:- = (Average (Yi)-(((XiYi-(1/n*Sum(Xi)*Sum(Yi)))/ (Sum (X^2)-(1/n* Square(Sum(Xi))))*Average(Xi))) = (Average (Yi)-(Slope*Average (Xi))) Step 12:- Create new MTH name month and update the values 1- 12 months. Step 13:- Calculate Method 1 Value :- = 1/(intercept+(Slope * MTH)) stored into Method 1 Step12 :- Calculate the Method2 Slope Value:- = ((Sum (XiYi) – (1/n *(Sum(Xi)*Sum(Yi)))/ SQRT((Sum(X^2)-(1/n *(Square(sum(Xi))) *(Sum(Yi^2) – (1/n*Square(sum(Yi)))))*(Stdev(Yi)/Stdev(Xi)) Step 13:- Calculate the Method2 intercept value = Average (Yi) – (slope *average(Xi)) Step 14:- Calculate the Method 2 value = 1/(intercept +(slope*MTH)) stored into Method 2 Step15:- Calculate the Method 3 Slope Value = Covariance.P(Xi,Yi) / Var.P(Xi) Step 16:- Calculate the Method 3 intercept value = Average(Yi) – ((Covariance.S(Xi,Yi)/ Var.P(Xi))* Average(Xi)) Or Average(Yi) – (slope* Average(Xi)) Step 17 :- Calculate the Method 3 value = 1/(intercept +(slope*MTH)) stored into Method3 Step 18 :- Calculate the value RSQ = ((sum(XiYi) – (1/n*(sum(Xi)*sum(Yi))))/SQRT(Sum(X^2) – (1/n*Square(sum(Xi))))*(sum(Yi^2) – (1/n * square(sum(Yi)))))
Hi maheshulunda, I think what everyone is driving at is that you need to put in some effort yourself - no-one is going to just drop a solution in your lap. I mean, I barely ever did my *my own* homework... Often with complex problems, the difficulty is in seeing where to start. We all get a little intimidated by things sometimes and even more so when first starting out. This is absolutely normal. The way to get round this is to stop thinking in terms of the whole, but to split the overall task into a simpler sequence of smaller, more manageable tasks. It appears to me that you have several distinct sets of data that you want to return. Each of these sets is comprised of a number of different calculated elements. So start with the elements, by themselves. No stored procedure wrapper, no joining, no nothing. Just simple SQL statements that generate a result from a column value. e.g. select MyCalcuatedSquare = power(MyColumnValue,2) from MyTable Once you can calculate each element, then you can start to think about how you link them together to form a set.
pls guy give me some suggestion on this.![alt text][1] [1]: /storage/temp/1638-linear.png
Homework? I don't do homework unless I'm getting the grade. If you're stuck on a particular part of that process, detail what you've tried and what's failing. That I could help with.
hi grant.. This is requirement and i need to create a stored procedure in sql server.. two tables are there..
There is some discussion of this issue here:
http://ask.sqlservercentral.com/questions/96778/can-this-linear-regression-algorithm-for-sql-serve.html The solution is a bit clunky but it works.
18 People are following this question.
