线性回归，在MySQL中寻找斜率 [英] Linear regression, finding slope in MySQL

问题描述

我正在尝试查找以DATETIME作为x轴，在y轴上有数字的数据集的斜率.

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

我尝试了多种方法，当我将数据插入Excel时，没有什么比这条线的斜率更合适的了，因为它有多个数量级.

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.

这就是我现在所拥有的，但是它的斜率是-1.13e-13而不是-0.008

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;

非常感谢您的帮助.

我也尝试过

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;

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;

并获得完全不同的答案(-0.0019)，这更准确吗?有人知道吗?

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

推荐答案

X单位是多少?您可能必须将时间值显式转换为期望的值，无论是秒，小时还是天.

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.

这篇关于线性回归，在MySQL中寻找斜率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！