meerkat
Junior Member
member is offline
Gender:
Posts: 59


Least Squares Fit using SQL
« Thread started on: Aug 17^{th}, 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
