Liberty BASIC Community Forum
« Least Squares Fit using SQL »

Welcome Guest. Please Login or Register.
Sep 21st, 2017, 10:58am


Rules|Home|Help|Search|Recent Posts|Notification


« Previous Topic | Next Topic »
Pages: 1  Notify Send Topic Print
 thread  Author  Topic: Least Squares Fit using SQL  (Read 58 times)
meerkat
Junior Member
ImageImage


member is offline

Avatar




PM

Gender: Male
Posts: 59
xx Least Squares Fit using SQL
« Thread started on: Aug 17th, 2017, 10:07am »

This SQL command seems to work and give you the slope (m) and intercept (b)
Given a database with id,x, and y.
- id could be a product or stock ticker.
- x could be any value like day of year, or height.
- y can be sales, or price, or weight.

You can therefore predict y = mx + b

Code:
SELECT (count(*) * SUM(x*y) - SUM(x) * SUM(y))/
(count(*) * SUM(x*x) - SUM(x) * SUM(x)) AS m,
AVG(y) - AVG(x) *
(count(*) * SUM(x*y) - SUM(x) * SUM(y))/
(count(*) * SUM(x*x) - SUM(x) * SUM(x)) AS b
FROM the_data group by id; 


If you want to update your database you need 2 fields to hold m and be.
Code:
update the_data set 
m = (SELECT (count(*) * SUM(x*y) - SUM(x) * SUM(y))/
(count(*) * SUM(x*x) - SUM(x) * SUM(x)) from rawdata as rm
where rm.id = the_data.id),
b = (SELECT AVG(y) - AVG(x) *
(count(*) * SUM(x*y) - SUM(x) * SUM(y))/
(count(*) * SUM(x*x) - SUM(x) * SUM(x)) from rawdata as rb
where rb.id = the_data.id) 


You can enter this SQL command to create a table and data.
Code:
create table the_data
(
id char(3),
x float,
y float,
b float,
m bloat
);

insert into the_data
values
(1,10,12),
(1,20,18),
(1,30,34),
(1,40,42),
(1,50,46),
(1,75,57),
(1,35,718),
(2,44,112),
(2,19,118),
(2,22,314),
(2,66,412),
(2,61,416),
(2,63,517),
(2,74,718); 


Dan
« Last Edit: Aug 17th, 2017, 10:09am by meerkat » User IP Logged

Pages: 1  Notify Send Topic Print
« Previous Topic | Next Topic »

Rules|Home|Help|Search|Recent Posts|Notification

Donate $6.99 for 50,000 Ad-Free Pageviews!

| |

This forum powered for FREE by Conforums ©
Sign up for your own Free Message Board today!
Terms of Service | Privacy Policy | Conforums Support | Parental Controls