MySQL 5中创建皮尔逊相关系数函数的问题及适配方案咨询

我尝试在MySQL 5中创建一个皮尔逊相关系数函数PearsonCorrelation，该函数接收两列（x、y）作为输入，返回单个Decimal类型的相关系数值。以下是我的函数定义：

-- Calculate pearson correlation coefficient.
-- INPUT: X and Y should be columns of data (decimal) 
-- RETURN: A value between -1 and 1 depending on the strength of the relationship between the 2 columns e.g. 0.43.

DELIMITER $$

CREATE FUNCTION PearsonCorrelation(
    x Decimal(10,1),
    y Decimal(10,1)
)
RETURNS Decimal(10,1)
DETERMINISTIC
BEGIN
    DECLARE correlation_coefficient  DECIMAL(3,2);
    SET correlation_coefficient = (avg(x * y) - avg(x) * avg(y)) / (sqrt(avg(x * x) - avg(x) * avg(x)) * sqrt(avg(y * y) - avg(y) * avg(y)));
    RETURN(correlation_coefficient);
END $$

DELIMITER ;

但执行函数调用时出现错误：invalid use of group function。我准备了如下测试数据，该数据集的预期相关系数为0.86：

CREATE TABLE data_table
(
x Decimal(3,1) NOT NULL,
y Decimal(3,1) NOT NULL
);
INSERT INTO data_table 
VALUES(11.2, 10.4),
(9.7, 4.6),
(4.5, 2.1);

我计划按如下方式调用函数：

Select PearsonCorrelation(x,y) as corrcoef
FROM data_table;

现明确问题：是否可以将表列作为参数传入该相关系数函数？若可以，应如何适配该函数以实现需求？

可以将表列作为参数传入，但你的函数存在两个核心问题：

1. 参数类型错误：当前函数接收的是单个Decimal值，而非整列数据。当你传入列名时，MySQL会逐行传递单个值，而函数内部使用avg()这类聚合函数，只能在聚合查询中使用，不能在处理单行数据的函数里直接调用，这就是报错invalid use of group function的原因。
2. 函数特性不匹配：DETERMINISTIC表示相同输入返回相同结果，但聚合计算依赖整个数据集，不属于确定性函数，需要修改为NOT DETERMINISTIC。

正确的实现方案 #

方案1：修改函数为接收表名和列名（动态SQL实现）

这种方式可以灵活指定不同表和列：

DELIMITER $$

CREATE FUNCTION PearsonCorrelation(table_name VARCHAR(64), x_col VARCHAR(64), y_col VARCHAR(64))
RETURNS DECIMAL(10,2)
NOT DETERMINISTIC
BEGIN
    DECLARE corr DECIMAL(10,2);
    SET @sql = CONCAT(
        'SELECT (AVG(x*y) - AVG(x)*AVG(y)) / (SQRT(AVG(x*x) - AVG(x)*AVG(x)) * SQRT(AVG(y*y) - AVG(y)*AVG(y))) INTO @corr ',
        'FROM (SELECT `', x_col, '` AS x, `', y_col, '` AS y FROM ', table_name, ') AS t'
    );
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET corr = @corr;
    RETURN corr;
END $$

DELIMITER ;

调用方式：

SELECT PearsonCorrelation('data_table', 'x', 'y') AS corrcoef;

方案2：直接使用聚合查询（无需函数）

如果不需要复用函数，直接写查询更简单高效：

SELECT 
    (AVG(x*y) - AVG(x)*AVG(y)) / 
    (SQRT(AVG(x*x) - POWER(AVG(x),2)) * SQRT(AVG(y*y) - POWER(AVG(y),2))) AS corrcoef
FROM data_table;

这个查询会直接返回预期的0.86结果。

关键说明 #

• 聚合函数（如avg()、sqrt()）只能在处理整个数据集的上下文中使用，不能在单行处理的函数内部调用。
• 若要封装成函数，必须通过动态SQL来构造聚合查询，因为函数无法直接接收整列作为参数，只能通过表名和列名字符串间接获取数据。
• 注意处理分母为0的情况（当某列所有值相同时，分母会为0，导致计算错误），可以在函数中添加判断逻辑避免报错。

内容的提问来源于stack exchange，提问作者Tim