Я не могу найти проблему с этой функцией SQL
Вот код функции (My)SQL (работает вне функции), но я не могу сохранить его как функцию для дальнейшего повторного использования...
Это пример рабочего запроса:
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
Я извлек бизнес-логику в этот UDF:
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 ;
Когда я пытаюсь выполнить этот код в командной строке, я получаю это бесполезное сообщение об ошибке:
> SELECT PEARSON_R(Items_Available, Store_Sales) FROM store_sales;
ERROR 1111 (HY000): Invalid use of group function
Есть ли у вас какие-либо идеи?
Я пытался сильно упростить функцию, но как только я использую групповую функцию, у меня возникает эта ошибка.
Ответы 2
Хотя я не вижу ничего плохого в реализации функции как таковой, я заметил, что вы используете агрегатную функцию без фактического агрегирования чего-либо (поэтому вы не используете предложение GROUP BY)
Пост с такой же ошибкой, как у вас: ОШИБКА 1111 (HY000): Недопустимое использование групповой функции
Основная проблема заключается в том, что ваша функция ссылается на агрегатные функции: COUNT, SUM... Они недопустимы в рамках функции, которая не может ссылаться, делать выводы или даже предполагать что-либо о контексте вызывающего запроса.
На мой взгляд, код функции даже не должен компилироваться (но он компилируется); мы можем воспроизвести проблему только с помощью:
select PEARSON_R (1, 2);
Error: ER_INVALID_GROUP_FUNC_USE: Invalid use of group function
Если вы хотите перенести часть логики в функцию, то ей нужно будет принимать каждое агрегатное значение в качестве аргумента. Подпись будет выглядеть так:
CREATE FUNCTION PEARSON_R(
SUM_X INT, -- SUM(X)
SUM_Y INT, -- SUM(Y)
SUM_XY INT, -- SUM(X * Y)
SUM_XX INT, -- SUM(X * X)
SUM_YY INT, -- SUM(Y * Y)
CNT INT -- COUNT(*)
) RETURNS FLOAT DETERMINISTIC
Затем вы бы вызывали его так в запросе:
SELECT PEARSON_R(
SUM(X),
SUM(Y),
SUM(X * Y),
SUM(X * X),
SUM(Y * Y),
COUNT(*)
) as pearson_r
FROM store_sales;
Обратите внимание, что вы вполне можете использовать подзапрос (или боковое соединение), а не функцию:
SELECT ( sum_xy - sum_x * sum_y) / ...
FROM (
SELECT PEARSON_R(
SUM(X) sum_x,
SUM(Y) sum_y,
SUM(X * Y) sum_xy,
SUM(X * X) sum_xx,
SUM(Y * Y) sum_yy,
COUNT(*) cnt
) as pearson_r
FROM store_sales
) t
Спасибо, это гораздо менее удобно в использовании, но я так понимаю, что групповые функции запрещены в функциях SQL: вы можете подтвердить?
Да, это вполне допустимый код для MySQL даже без группировки по операции «кроме» в функциях или процедурах. Спасибо за помощь.