mysql - 计算每一行相关性的最快方法？

Question

好吧，我有一个包含数百万行的表 data。我想对每一行(从第 1 行到当前行减 1)进行相关研究。例如第一行被省略。第 2 行的 result 列将使用第 1 行提供相关性。第 3 行的 result 列将使用第 1 行和第 2 行提供相关性。等等。

可以使用以下方法计算整个表的相关性:

SELECT (Count(*)*Sum(x*y)-Sum(x)*Sum(y))/
         (sqrt(Count(*)*Sum(x*x)-Sum(x)*Sum(x))*
          sqrt(Count(*)*Sum(y*y)-Sum(y)*Sum(y))) AS TotalCorelation FROM Data;

我想尽可能避免使用连接，因为它会花费很多时间，有时甚至会出现超时错误，超过 300 秒)。还有什么选择？

示例表数据结构:

id, x, y, result
1 , 4, 2, null
2 , 6, 3, -0.2312
3 , 5, 5, 0.42312
4 , 6, 2, -0.5231
5 , 5, 5, 0.22312
6 , 3, 7, -0.2312
7 , 2, 9, 0.42231
8 , 7, 2, 0.32253
9 , 9, 5, 0.32431

id : primary key 
x and y : The data 
result: correlation

Answer 1

我想是这样的:

SELECT d2.ID, d2.x, d2.y, d2.result, 
       (Count(*)*Sum(d1.x*d1.y)-Sum(d1.x)*Sum(d1.y))/
         (sqrt(Count(*)*Sum(d1.x*d1.x)-Sum(d1.x)*Sum(d1.x))*
          sqrt(Count(*)*Sum(d1.y*d1.y)-Sum(d1.y)*Sum(d1.y))) AS TotalCorelation
FROM Data d1
RIGHT JOIN Data d2 ON d1.id < d2.id
GROUP BY d2.ID
ORDER BY d2.ID

如果没有用于从 N 行计算 N+1 相关性的封闭形式，则必须使用这样的二次连接。

我假设您的基本公式是正确的。但我不确定它是——当我只是在总数据集上运行它时，我没有得到结果 0.32431 , 我得到 -0.552773693079 .

这是一个线性实现:

SET @SumX = 0;
SET @SumY = 0;
SET @Count = 0;
SET @SumX2 = 0;
SET @SumY2 = 0;
SET @SumXY = 0;

SELECT id, x, y,
       @SumX := @SumX + x AS SumX,
       @SumY := @SumY + y AS SumY,
       @Count := @Count + 1 AS ct,
       @SumX2 := @SumX2 + x*x AS SumX2,
       @SumY2 := @SumY2 + y*y AS SumY2,
       @SumXY := @SumXY + x*y AS SumXY,
       IF(@Count > 1,
          (@Count*@SumXY-@SumX*@SumY)/
            (sqrt(@Count*@SumX2-@SumX*@SumX)*
             sqrt(@Count*@SumY2-@SumY*@SumY)), NULL) AS TotalCorelation
FROM DATA
ORDER BY id

SQLFIDDLE