MySQL自定义函数使用聚合函数触发ERROR 1111错误求助

问题描述 #

编写的皮尔逊相关系数SQL查询可直接正常运行，但将逻辑封装为MySQL自定义函数PEARSON_R后，执行SELECT PEARSON_R(Items_Available, Store_Sales) FROM store_sales;时触发ERROR 1111 (HY000): Invalid use of group function错误，只要函数中使用聚合函数就会出现该问题。

可正常运行的查询代码 #

SELECT
  (
    SUM(Items_Available * Store_Sales) - (SUM(Items_Available) * SUM(Store_Sales)) / COUNT(*)
  ) / (
    SQRT(
      SUM(Items_Available * Items_Available) - (SUM(Items_Available) * SUM(Items_Available)) / COUNT(*)
    ) * SQRT(
      SUM(Store_Sales * Store_Sales) - (SUM(Store_Sales) * SUM(Store_Sales)) / COUNT(*)
    )
  ) as pearson_r
FROM
  store_sales

报错的自定义函数代码 #

DELIMITER $$
DROP FUNCTION IF EXISTS PEARSON_R $$
CREATE FUNCTION PEARSON_R(X INT, Y INT) RETURNS FLOAT DETERMINISTIC
BEGIN
  RETURN (SUM(X * Y) - (SUM(X) * SUM(Y)) / COUNT(*)) / (SQRT(SUM(X * X) - (SUM(X) * SUM(X)) / COUNT(*)) * SQRT(SUM(Y * Y) - (SUM(Y) * SUM(Y)) / COUNT(*)));
END$$
DELIMITER ;

问题原因 #

MySQL的标量自定义函数是行级处理函数，每一行数据都会触发一次函数调用，函数参数X和Y接收的是当前行的单个字段值。而SUM、COUNT这类聚合函数是针对整个数据集或分组进行计算的，在行级函数中直接调用聚合函数时，MySQL无法确定聚合的范围（会错误地将参数视为单行值进行聚合），从而触发"无效使用分组函数"的错误。

原查询能正常运行，是因为它直接在SELECT中使用聚合函数，此时聚合范围明确为整个store_sales表的所有行。

解决方案 #

方案1：使用视图封装逻辑 #

如果仅需针对固定表计算皮尔逊系数，可以将原查询封装为视图，直接查询视图获取结果：

CREATE VIEW pearson_store_sales AS
SELECT
  (
    SUM(Items_Available * Store_Sales) - (SUM(Items_Available) * SUM(Store_Sales)) / COUNT(*)
  ) / (
    SQRT(
      SUM(Items_Available * Items_Available) - (SUM(Items_Available) * SUM(Items_Available)) / COUNT(*)
    ) * SQRT(
      SUM(Store_Sales * Store_Sales) - (SUM(Store_Sales) * SUM(Store_Sales)) / COUNT(*)
    )
  ) as pearson_r
FROM
  store_sales;

调用方式：

SELECT * FROM pearson_store_sales;

方案2：使用存储过程实现通用计算 #

如果需要对任意表和字段计算皮尔逊系数，可使用存储过程结合动态SQL实现：

DELIMITER $$
DROP PROCEDURE IF EXISTS CALC_PEARSON_R $$
CREATE PROCEDURE CALC_PEARSON_R(IN table_name VARCHAR(255), IN col_x VARCHAR(255), IN col_y VARCHAR(255))
BEGIN
  SET @sql = CONCAT(
    'SELECT ',
    '(SUM(', col_x, '*', col_y, ') - (SUM(', col_x, ') * SUM(', col_y, ')) / COUNT(*)) / ',
    '(SQRT(SUM(', col_x, '*', col_x, ') - (SUM(', col_x, ') * SUM(', col_x, ')) / COUNT(*)) * ',
    'SQRT(SUM(', col_y, '*', col_y, ') - (SUM(', col_y, ') * SUM(', col_y, ')) / COUNT(*))) AS pearson_r ',
    'FROM ', table_name
  );
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

调用方式（传入表名和两个字段名）：

CALL CALC_PEARSON_R('store_sales', 'Items_Available', 'Store_Sales');

内容的提问来源于stack exchange，提问作者Mickaël Andrieu