sql - postgresql corr 聚合函数返回 null

Question

我有两个完美(或完全不完美？)相关的数字，我想找到它们之间的相关性。原始场景不同且更复杂，但问题出在 pg 使用的相关方法中的某个地方。请考虑以下查询:

    WITH all_series AS (
      select t as id, 'One' as name, 1 as num from generate_series(1, 10) t
      UNION
      select t as id, 'Two' as name, 2 as num from generate_series(1, 10) t
      ORDER BY name, id
    )

    SELECT (t1.name || '|' || t2.name) as names, corr(t2.num, t1.num) c
    FROM all_series t1
    INNER JOIN all_series t2 ON t1.id = t2.id
    WHERE t1.name > t2.name
    GROUP BY (t1.name || '|' || t2.name)
    ORDER BY (t1.name || '|' || t2.name)

如果您删除组并打开选择，数字将完全对齐，这应该给出一些相关性......但它给出空(甚至不是零)。

问候，

Answer 1

我猜你想要生成序列的相关性，而不是常数 1:

WITH all_series AS (
  select t as id, 'One' as name, 1 as num, t.val from generate_series(1, 10) t(val)
  UNION ALL
  select t as id, 'Two' as name, 2 as num, t.val from generate_series(1, 10) t(val)
  ORDER BY name, id
)
SELECT (t1.name || '|' || t2.name) as names, corr(t2.val, t1.val) c
FROM all_series t1
INNER JOIN all_series t2 ON t1.id = t2.id
WHERE t1.name > t2.name
GROUP BY (t1.name || '|' || t2.name);

您的版本正在对 num 进行关联，它是常数(“1”或“2”)。我猜 NULL 是计算中除以零的结果。两个常量列的相关性应该是1，但也是退化的情况。