如何利用集合在neo4j中使用平均值函数

Question

我要计算两个向量的协方差，如集合A=1，2，3，4 B=5，6，7，8。

Cov(A，B)= Sigma(ai-AVGa)*(bi-AVGb) / (n-1)

我的协方差计算问题是：

1)编写时不能有嵌套的聚合函数

SUM((ai-avg(a)) * (bi-avg(b)))

( 2)或以另一种形式，我如何提取两个集合，其中一个减少如下：

REDUCE(x= 0.0, ai IN COLLECT(a) | bi IN COLLECT(b) | x + (ai-avg(a))*(bi-avg(b)))

3)如果不可能在oe中提取两个集合，那么减少当它们被分离时，如何关联它们的值来计算协方差

REDUCE(x= 0.0, ai IN COLLECT(a) | x + (ai-avg(a)))
REDUCE(y= 0.0, bi IN COLLECT(b) | y + (bi-avg(b)))

我的意思是我可以写嵌套约简吗？

4)有没有“放松”、“提取”的方法？

谢谢你的帮助。

Answer 1

赛博赛姆的回答是完全正确的，但如果你想避免n^2笛卡儿产品的双重展开，你可以这样做：

WITH [1,2,3,4] AS a, [5,6,7,8] AS b
WITH REDUCE(s = 0.0, x IN a | s + x) / SIZE(a) AS e_a,
     REDUCE(s = 0.0, x IN b | s + x) / SIZE(b) AS e_b,
     SIZE(a) AS n, a, b
RETURN REDUCE(s = 0.0, i IN RANGE(0, n - 1) | s + ((a[i] - e_a) * (b[i] - e_b))) / (n - 1) AS cov;

编辑：

不需要任何人，但让我更详细地说明为什么要避免https://stackoverflow.com/a/34423783/2848578中的双重展开。正如我在下面所说的，Cypher中的UNWINDing k长度-n集合会导致n^k行。因此，让我们取两个长度-3集合来计算协方差。

> WITH [1,2,3] AS a, [4,5,6] AS b
UNWIND a AS aa
UNWIND b AS bb
RETURN aa, bb;
   | aa | bb
---+----+----
 1 |  1 |  4
 2 |  1 |  5
 3 |  1 |  6
 4 |  2 |  4
 5 |  2 |  5
 6 |  2 |  6
 7 |  3 |  4
 8 |  3 |  5
 9 |  3 |  6

现在我们有了n^k = 3^2 = 9行。此时，取这些标识符的平均值，意味着我们取9个值的平均值。

> WITH [1,2,3] AS a, [4,5,6] AS b
UNWIND a AS aa
UNWIND b AS bb
RETURN AVG(aa), AVG(bb);
   | AVG(aa) | AVG(bb)
---+---------+---------
 1 |     2.0 |     5.0

同样，正如我下面所说，这不影响答案，因为重复的数字向量的平均值总是相同的。例如，{1,2,3}的平均值等于{1,2,3,1,2,3}的平均值。对于较小的n值来说，这可能无关紧要，但是当您开始获得更大的n值时，您将看到性能下降。

假设你有两个长度-1000向量。计算每一次双展开的平均数：

> WITH RANGE(0, 1000) AS a, RANGE(1000, 2000) AS b
UNWIND a AS aa
UNWIND b AS bb
RETURN AVG(aa), AVG(bb);
   | AVG(aa) | AVG(bb)
---+---------+---------
 1 |   500.0 |  1500.0

714 ms

明显慢于使用“减少”：

> WITH RANGE(0, 1000) AS a, RANGE(1000, 2000) AS b
RETURN REDUCE(s = 0.0, x IN a | s + x) / SIZE(a) AS e_a,
       REDUCE(s = 0.0, x IN b | s + x) / SIZE(b) AS e_b;
   | e_a   | e_b   
---+-------+--------
 1 | 500.0 | 1500.0

4 ms

为了将它们结合起来，我将在长度-1000向量上对这两个查询进行完整的比较：

> WITH RANGE(0, 1000) AS aa, RANGE(1000, 2000) AS bb
UNWIND aa AS a
UNWIND bb AS b
WITH aa, bb, SIZE(aa) AS n, AVG(a) AS avgA, AVG(b) AS avgB
RETURN REDUCE(s = 0, i IN RANGE(0,n-1)| s +((aa[i]-avgA)*(bb[i]-avgB)))/(n-1) AS
 covariance;
   | covariance
---+------------
 1 |    83583.5

9105 ms

> WITH RANGE(0, 1000) AS a, RANGE(1000, 2000) AS b
WITH REDUCE(s = 0.0, x IN a | s + x) / SIZE(a) AS e_a,
     REDUCE(s = 0.0, x IN b | s + x) / SIZE(b) AS e_b,
          SIZE(a) AS n, a, b
          RETURN REDUCE(s = 0.0, i IN RANGE(0, n - 1) | s + ((a[i] - e_a) * (b[i
] - e_b))) / (n - 1) AS cov;
   | cov    
---+---------
 1 | 83583.5

33 ms

Answer 2

编辑

这应该计算协方差(根据您的公式)，给定您的样本输入：

WITH [1,2,3,4] AS aa, [5,6,7,8] AS bb
UNWIND aa AS a
UNWIND bb AS b
WITH aa, bb, SIZE(aa) AS n, AVG(a) AS avgA, AVG(b) AS avgB
RETURN REDUCE(s = 0, i IN RANGE(0,n-1)| s +((aa[i]-avgA)*(bb[i]-avgB)))/(n-1) AS covariance;

当n很小时，这种方法是可以的，就像原始样本数据一样。

然而，正如@NicoleWhite和@jjaderberg所指出的，当n不小时，这种方法将效率低下。@NicoleWhite的回答是一个优雅的通用解决方案。

Answer 3

您如何到达集合A和B？avg函数是一个聚合函数，不能在REDUCE上下文中使用，也不能应用于集合。在达到这一点之前，你应该计算出你的平均值，但是如何才能做到最好取决于你是如何得到两个值集合的。如果您所处的位置有单独的结果项，然后通过collect获取A和B，那么就可以使用avg了。例如：

WITH [1, 2, 3, 4] AS aa UNWIND aa AS a
WITH collect(a) AS aa, avg(a) AS aAvg
RETURN aa, aAvg

这两种收藏品

WITH [1, 2, 3, 4] AS aColl UNWIND aColl AS a
WITH collect(a) AS aColl, avg(a) AS aAvg
WITH aColl, aAvg,[5, 6, 7, 8] AS bColl UNWIND bColl AS b
WITH aColl, aAvg, collect(b) AS bColl, avg(b) AS bAvg
RETURN aColl, aAvg, bColl, bAvg

有了这两个平均值之后，让我们称它们为aAvg和bAvg，以及两个集合，aColl和bColl，您可以这样做

RETURN REDUCE(x = 0.0, i IN range(0, size(aColl) - 1) | x + ((aColl[i] - aAvg) * (bColl[i] - bAvg))) / (size(aColl) - 1) AS covariance