Групповая функция не является оракулом одной группы

Обновлено: 21.11.2024

Я надеялся узнать, может ли кто-нибудь объяснить, почему возникает эта ошибка. Я столкнулся с сообщением «ORA-00937: не групповая функция с одной группой».

Я собираюсь продемонстрировать ошибку, используя двойную таблицу.

Эта инструкция SQL ниже выполняется без ошибок:

Однако, когда я помещаю его во встроенное представление с соединением, я получаю сообщение об ошибке, о котором я упоминал выше.

Мне стало любопытно, и я заменил ВНУТРЕННЕЕ СОЕДИНЕНИЕ на ЛЕВОЕ СОЕДИНЕНИЕ. Теперь ошибка не возникает.

Кажется, проблема возникает из-за "COUNT(*) OVER(PARTITION BY T.DUMMY) AS RC", но что именно вызывает это или как мне выяснить, что вызывает это?

Спасибо за помощь.

Лучший ответ

Последовательность анализа, используемая оптимизатором, была следующей:

Стратегия 1. Затраты на объединение t4 с представлением t3 без сложного слияния представлений без предиката объединения

Стратегия 2. Затраты на использование комплексного слияния представлений

Стратегия 3. Стоимость объединения t4 с представлением t3 без сложного слияния представлений, но с использованием предиката соединения pushdown . что вызвало сбой.

Предикат соединения: t4.dummy = t3.dummy,

Я думаю, что в случае с отправкой предикатов это означает, что Oracle пытается оптимизировать следующее, которое затем будет выполняться для каждой строки, возвращаемой из t4.

COUNT(*) OVER(PARTITION BY T.DUMMY) AS RC,

ГДЕ

Нет очевидной причины, по которой этот блок запроса должен приводить к ошибке ORA-00937, и я даже не могу подтвердить, что именно так выглядит виновный блок запроса, потому что файл трассировки останавливается до того, как будет выгружен "непроанализированный оператор". . (В вашей «нулевой» версии не отображается предложение where, а в версии 19.3 на данный момент предложение where не отображается).

Особенно досадная деталь этого запроса заключается в том, что я думаю, что оптимизатор мог удалить столбец "count(*) over ()" до того, как он сделал что-либо еще, и тогда проблема могла никогда не появиться.

P.S. Третьим вариантом было бы поместить подсказку /*+ no_push_pred */ в само встроенное представление, но я всегда призываю людей явно называть свои блоки запросов и использовать механизм подсказок, который ссылается на имена блоков запросов. В более сложных случаях это означает, что параметр формата dbms_xplan со значением «псевдоним» упрощает интерпретацию плана.

Ответы

Должно быть, это одна из многих ошибок Oracle, связанных с объединением сложных представлений (объединение агрегированного встроенного представления). Оптимизатор говорит себе: я присоединяюсь к DUAL с результатом агрегированного запроса, где группировка находится в том же столбце, что и объединение. Это внутреннее соединение, поэтому я мог бы сначала выполнить соединение, а затем сгруппировать по столбцу соединения. И когда он говорит это себе, Oracle думает, что T3.DUMMY — это то же самое, что и T.DUMMY, поэтому он решает сгруппировать по T3.DUMMY. Но тогда, в конце, он должен РАЗДЕЛИТЬСЯ НА T(4).DUMMY в аналитической функции, что и вызывает проблему.

Конечно, трансформация неправильная. Разработчики Oracle, которые писали код для оптимизатора, накосячили. В отличие от других подобных багов, где трансформация абсолютно неверна, в данном случае ее действительно можно исправить — оптимизатор должен признать, что аналитическая функция может РАЗДЕЛИТЬ ПО T3.DUMMY, и в этом случае она не будет жаловаться на «ни одного- групповая групповая функция». Просто некачественная работа в Oracle Corp.

Как я уже сказал, есть несколько других подобных ошибок. (Увы, я не платный клиент, поэтому у меня нет доступа к MOS, чтобы указать вам точный номер ошибки.) В любом случае вы можете попробовать эти две вещи, чтобы убедиться в природе этой ошибки: <р>1. Измените аналитическую функцию на (PARTITION BY NULL). Вы увидите, что «плохой» запрос работает просто отлично. РАЗДЕЛ T.DUMMY является причиной проблемы.

<р>2. Добавьте подсказку NO_QUERY_TRANSFORMATION во внешний SELECT (оставив ваш «плохой» запрос без изменений). Вы увидите, что все работает отлично.

Это дополнение к вашему собственному дополнительному тесту: если вы измените соединение на левое внешнее соединение, группировка по T.DUMMY больше не совпадает с группировкой по T3.DUMMY, поэтому группируется по T4.DUMMY (после выполнение объединения), и аналитическая функция работает просто отлично.

Ошибка ORA-00937 возникает при неправильном использовании команды GROUP BY. Команда GROUP BY позволяет пользователю просматривать строки, имеющие общее значение поля в одной строке. Например, генеральный директор малого бизнеса хочет просмотреть сотрудников, которые отработали наибольшее количество часов по отделам в прошлом месяце.Другим случаем может быть случай, когда директор школы хочет просмотреть учащихся в списках почетных гостей по классным комнатам. В таких случаях пользователь может запустить оператор GROUP BY для просмотра результатов.

GROUP BY используется в сочетании с агрегатными функциями для фильтрации результатов по значению. Команда GROUP BY может быть очень полезна для просмотра выбранного набора результатов. Он широко используется, поэтому важно использовать правильный синтаксис при выполнении инструкции.

Проблема

Когда вы столкнетесь с ORA-00923, вы увидите сообщение об ошибке

ORA-00923: не групповая функция с одной группой

Документация Oracle указывает причину следующим образом:

Список SELECT не может включать как групповую функцию, такую как AVG, COUNT, MAX, MIN, SUM, STDDEV или VARIANCE, так и отдельное выражение столбца, если отдельное выражение столбца не включено в предложение GROUP BY.

Другими словами, вы попытались выполнить оператор SELECT, требующий предложения GROUP BY, без включения предложения GROUP BY. Если вы используете агрегатную функцию в своем запросе на выборку (например, AVG, COUNT, MAX, MIN…), вы должны иметь предложение GROUP BY.

Решение

Чтобы устранить ошибку, вы можете либо удалить групповую функцию или выражение столбца из предложения SELECT, либо добавить предложение GROUP BY, включающее выражения столбца.

Если вы решите добавить предложение GROUP BY, обязательно включите выражения столбцов и соблюдайте правильный порядок. Возьмем пример генерального директора малого бизнеса, который хочет просмотреть список сотрудников, отработавших наибольшее количество часов, организованный по отделам. Правильный синтаксис, включающий предложение GROUP BY, будет следующим:

ВЫБРАТЬ отдел, MAX(часы) AS «большинство часов»

ГРУППИРОВАТЬ ПО ОТДЕЛАМ;

Заглядывая вперед

Помните, что если вы используете агрегатную функцию в запросе на выборку, вы также должны иметь предложение GROUP BY. Вы не можете ссылаться на неагрегированный столбец в SELECT, который также не указан в предложении GROUP BY. Для успешного выполнения запроса необходимо либо удалить групповую функцию или выражение столбца из SELECT, либо добавить предложение GROUP BY, включающее выражение столбца.

Соблюдение этого правила и обеспечение правильного синтаксиса запроса должны предотвратить появление ошибки ORA-00937 в будущем. Несмотря на то, что процесс исправления этой ошибки не слишком сложен, обратитесь к администратору базы данных или лицензированному консультанту Oracle, если у вас по-прежнему возникают проблемы с ORA-00937.

Причина: список SELECT не может включать как групповую функцию, такую как AVG, COUNT, MAX, MIN, SUM, STDDEV или VARIANCE, так и выражение отдельного столбца, если только выражение отдельного столбца не включено в предложение GROUP BY. .

Действие: удалите групповую функцию или выражение отдельного столбца из списка SELECT или добавьте предложение GROUP BY, которое включает все перечисленные выражения отдельных столбцов.

Вот еще один пример того, как пользователь столкнулся с ORA-00903 на форумах OraFAQ:

Я пытаюсь использовать созданную мной функцию, которая будет рассчитывать счета моих клиентов, включая скидки.

Однако каждый раз, когда я пытаюсь использовать эту функцию, я получаю сообщение ORA-00903. Есть предложения?

В вашей функции отсутствует предложение GROUP BY. Попробуйте извлечь SQL и запустить его в SQL * Plus, пока он снова не заработает. Вероятно, ORA-00937 может быть обнаружен во время компиляции, но даже если функция показывает, что она была скомпилирована, такие ошибки, как ORA-00937, все равно могут возникать.

Кроме того, ошибка ORA-00937 может возникать из-за вашего предложения SELECT. Обратите внимание, что COUNT() и SUM() должны быть сгруппированы по всем элементам в предложении SELECT.

Бурлесон — американская команда

Примечание. Эта документация по Oracle была создана в качестве справочника по поддержке и обучению Oracle для использования нашими специалистами-консультантами по настройке производительности администраторов баз данных. Не стесняйтесь задавать вопросы на нашем форуме Oracle.

Проверьте опыт! Любой, кто рассматривает возможность использования услуг эксперта службы поддержки Oracle, должен самостоятельно проверить свои полномочия и опыт, а не полагаться на рекламу и самопровозглашенный опыт. Все законные эксперты Oracle публикуют свои квалификации Oracle.

Ошибки? Технология Oracle меняется, и мы стараемся обновлять нашу информацию о поддержке BC Oracle. Если вы обнаружите ошибку или у вас есть предложение по улучшению нашего контента, мы будем признательны за ваш отзыв. Просто электронная почта:

и укажите URL-адрес страницы.

Burleson Consulting

Оракул поддержки баз данных

Помощь, как обойти ошибку: не работает групповая функция с одной группой.

Внутренние запросы работают. Можете ли вы помочь с моей ошибкой?

Когда я запускаю это в Toad, он выделяет sub1 как проблему, и я пробовал разные вещи, но все равно выдает ту же ошибку.

выберите sub2.point_id, sub2.total_released

(выбрать sub1.POINT_ID, sum(sum(nvl(sub1.Release,0)) - sum(nvl(sub1.Recall,0))) как total_released

выберите d.pointid_b как POINT_ID,

d.release_qty_a как выпуск,

r.recall_qty как отзыв

из емкости_release_Change c ПРИСОЕДИНЯЙТЕСЬ к емкости_релиз_change_Detail d

ВКЛ d.cp_release_seq = c.cp_release_Seq

ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ capacity_release_recall r

ВКЛ d.cp_release_detail_seq = r.cp_release_detail_seq

где c.r_start_date >= to_date («10.05.2020», «мм/дд/гггг») и c.r_end_date

--и d.pointid_b = 1006227

группировать по sub1.point_id, sub1.Release, sub1.Recall) sub2

группировать по sub2.point_id

Это набор результатов из sub1:

Лучший ответ

присоединиться к таблице2 d на d.cp_release_seq = c.cp_release_seq

левая таблица соединения 3 r на d.cp_release_detail_seq = r.cp_release_detail_seq

где c.r_start_date >= to_date('10.05.2020', 'дд/мм/гггг') и

выбрать pointid_b, объединить(сумма(выпуск_количество_а), 0) - объединить(сумма(отзыв_количество), 0) всего_выпущено

из данных

группировать по pointid_b

Примечание: объединения предотвращают получение нулевых результатов, если одно из двух объединений имеет значение null. Удалите, если не требуется.

1006032 — это 279, а не 289, как вы упомянули.

Ответы

Помните азбуку GROUP BY:

При использовании предложения GROUP BY и/или агрегатной функции все в предложении SELECT должно быть:

(A) агрегатная функция,

(B) одно из выражений "группировать по",

(D) то, что полностью зависит от вышеизложенного. (Например, если вы «ГРУППИРУЕТЕ ПО TRUNC(dt)», вы можете ВЫБРАТЬ «TO_CHAR (TRUNC(dt), 'Mon-DD')»).

Во внешнем запросе sub2.total_released не является ни одним из перечисленных выше.

Это одна ошибка; у вас могут быть и другие ошибки. Способ их решения зависит от того, что вы хотите сделать.

Какова цель внешнего запроса?

Спасибо за публикацию примера данных. Вы разместили желаемые результаты из этих данных и объяснение того, как вы получаете эти результаты из предоставленных данных? Я их нигде не вижу, но вы разместили так много материала, что я мог пропустить результаты и объяснение.

Было бы лучше, если бы вы опубликовали только те таблицы и столбцы, которые играют определенную роль в этой проблеме. Похоже, что в трех опубликованных вами таблицах около 40 столбцов, но запрос, который вы пробовали, использует только около 10 из них.

Желаемый результат из примера данных – 2 столбца: POINT_ID и Total_Released.

что-то вроде этого: хотя строк будет больше, чем на этом снимке экрана ниже.

Все данные, которые я разместил, необходимы для агрегирования, чтобы составить список нескольких Point_ID, и представляют собой результирующий набор, из которого c.r_start_date >= to_date ('5/10/2020', 'мм/дд/гггг') и c. r_end_date

Хорошо, это те результаты, которые вы хотите получить от опубликованных вами образцов данных. Теперь объясните, как вы получаете эти результаты из данных. Откуда берется значение point_id? Я не вижу значения 1111 в примерах данных. Является ли оно производным от какого-либо другого значения, например 1111111? Покажите, как вы вычисляете значение 200 из примера данных.

Если вы будете получать несколько строк выходных данных в рабочей среде, было бы лучше протестировать образцы данных, которые дали как минимум две строки.

Я сократил до двух point_ID и отрезал столбцы, которые нам не нужны для этого запроса.

Вот мои тестовые таблицы и их данные.

СОЗДАТЬ ТАБЛИЦУ TABLE1

НОМЕР CP_RELEASE_SEQ НЕ НУЛЬ,

CONTRACT VARCHAR2(25 BYTE) NOT NULL,

R_START_DATE ДАТА НЕ НУЛЕВАЯ,

R_END_DATE ДАТА НЕ НУЛЕВАЯ,

СОЗДАТЬ ТАБЛИЦУ TABLE2

НОМЕР CP_RELEASE_DETAIL_SEQ НЕ NULL,

НОМЕР CP_RELEASE_SEQ НЕ НУЛЬ,

CONTRACT VARCHAR2(25 BYTE) NOT NULL,

POINTID_B VARCHAR2 (20 BYTE) NOT NULL,

RELEASE_QTY_A ЧИСЛО НЕ НУЛЕВОЕ,

СОЗДАТЬ ТАБЛИЦУ TABLE3

НОМЕР CP_RELEASE_RECALL_SEQ НЕ NULL,

НОМЕР CP_RELEASE_DETAIL_SEQ НЕ NULL,

НОМЕР CP_RELEASE_SEQ НЕ НУЛЬ,

НОМЕР RECALL_QTY НЕ NULL,

Вставить в TABLE1