mysql 回归分析,线性回归，在MySQL中寻找斜率

I'm trying to find the slope of a dataset that has DATETIME as the x axis, and a number on the y axis.

I've tried the a number of approaches, and nothing will match the slope of the line when I plug the data into Excel, it's off by multiple orders of magnitude.

This is what I have right now, but it's giving me a slope of -1.13e-13 instead of -0.008

SELECT (SUM((x-xBar)*(y-yBar)))/(SUM((x-xBar))*SUM((x-xBar)))) as slope

from (select unix_timestamp(date) as x,

(select avg(unix_timestamp(date)) from datatable) as xBar,

value as y,

(select avg(value) from datatable) as yBar from datatable) as d;

Any help would be greatly appreciated, thanks.

Update:

I've also tried

SELECT effortId,

( COUNT(*)*SUM(unix_timestamp(date)*value) -SUM(unix_timestamp(date))*SUM(value) ) /

(COUNT(*)*SUM(unix_timestamp(date)^2)-SUM(unix_timestamp(date))^2) AS Slope

FROM datatable;

and get a completely different answer (-0.0019), is this more accurate? Anyone know?

解决方案

What are the X units? You'll probably have to convert the time values explicitly to what you expect, whether it is seconds, hours, or days.