MySQL中的斯皮尔曼相关系数-如何计算平均评级？

Question

如何计算相等值的平均排名？

我正在尝试编写一个计算Spearman相关系数的MySQL查询。我的想法是编写一个计算Pearson相关系数的查询，并在计算之前使用RANK () OVER (ORDER BY X)对值进行排名。

我已经设法让它工作了，请看下面的代码。但我的问题是，所有在排名前是相同值的值在排名后都会变成相同的值。我的研究告诉我，相等值应该得到所有相等值的平均排名。如何将其合并到我的查询中？

我的代码：

#setting values
SELECT
    @ax := avg(ranked_x) AS avg_x, 
    @ay := avg(ranked_y) AS avg_y,
    @div := (stddev_samp(ranked_x) * stddev_samp(ranked_y)) AS stddev
FROM
  (SELECT
      RANK() OVER(ORDER BY x) as ranked_x,
      RANK() OVER(ORDER BY y) as ranked_y
   FROM table) AS D;

#calculating the correlation coefficient with the values from the last query
SELECT
    sum( ( ranked_x - @ax ) * (ranked_y - @ay) ) / ((count(*) -1) * @div) AS correlation
FROM
   (SELECT
      RANK() OVER(ORDER BY x) as ranked_x,
      RANK() OVER(ORDER BY y) as ranked_y
   FROM table) AS D;

示例：

具有不同值的数据集，例如2，16，27，28，42，将被排序为1，2，3，4，5。具有多个相同值的数据集，例如2，2，6，6，6，将被排序为1.5，1.5，4，4，4。

这是因为这两个2的排名是1和2，1和2之间的平均值是1.5。这三个4是排名的数字3、4和5，这三个值的平均值是4。

Answer 1

根据Wiki : Spearman's rank correlation coefficient上提供的描述

WITH 
cte1 AS ( SELECT x,
                 y,
                 CAST(RANK() OVER(ORDER BY x) AS SIGNED) as ranked_x,
                 CAST(RANK() OVER(ORDER BY y) AS SIGNED) as ranked_y
          FROM src_table ),
cte2 AS ( SELECT x,
                 y,
                 ranked_x,
                 ranked_y,
                 ranked_x - ranked_y AS di,
                 (ranked_x - ranked_y) * (ranked_x - ranked_y) as di2
          FROM cte1 )
SELECT 1 - 6 * SUM(di2) / COUNT(*) / (COUNT(*) * COUNT(*) - 1) AS srcc
FROM cte2;

压缩后的内容相同：

WITH 
cte AS ( SELECT POW((CAST(RANK() OVER(ORDER BY x) AS SIGNED) - CAST(RANK() OVER(ORDER BY y) AS SIGNED)), 2) as di2
         FROM src_table )
SELECT 1 - 6 * SUM(di2) / COUNT(*) / (COUNT(*) * COUNT(*) - 1) AS srcc
FROM cte;

fiddle

Answer 2

也许这将有助于BTW什么小提琴？

DROP TABLE IF EXISTS T;
CREATE TABLE T
(VAL INT);
INSERT INTO  T VALUES
(2),(2),(6),(6),(6);

select t.val,aver
from t
join
(
select val, avg(rn) aver
from
(
SELECT VAL,
         row_number() over (order by val) rn
FROM T
) s
group by val
) s
on s.val = t.val
;

+------+--------+
| val  | aver   |
+------+--------+
|    2 | 1.5000 |
|    2 | 1.5000 |
|    6 | 4.0000 |
|    6 | 4.0000 |
|    6 | 4.0000 |
+------+--------+
5 rows in set (0.001 sec)