Хелен Борри - Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

* версии Firebird 1.5 и более поздние также имеют возможность группировать по большинству функциональных выражений, таких как CAST(). EXTRACT(),

SUBSTRING(), UPPER(), CASE() и COALESCE().

Следующий оператор завершает создание запроса, начатого в предыдущем примере:

SELECT PROJ_ID,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY PROJ_ID;

PROJ_ID TOTAL_BUDGET

GUIDE 650000.00

HWRII 520000.00

MAPDB 111000.00

MKTPR 1480000.00

VBASE 2600000.00

Группирующим элементом не может быть выражение, включающее агрегатную функцию, такую как AVG(), SUM(), MAX(), MIN() или COUNT() - они выполняют обобщение в том же группирующем контексте (на том же уровне), что и группирующий элемент. Это ограничение включает любые агрегатные выражения, которые встроены внутрь другого выражения. Например, синтаксический анализатор DSQL будет "ругаться", если вы попытаетесь сделать следующее:

SELECT

PROJ_ID,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY 2;

ISC ERROR CODE:335544569

Cannot use an aggregate function in a GROUP BY clause

(Нельзя использовать агрегатную функцию в предложении GROUP BY)

Очень "вредная" функция COUNT() используется разумно в группирующем запросе для подсчета количества для групп. Рассмотрим следующую модификацию нашего примера. Столбец DEPT_NO в наших требованиях не является подходящим кандидатом ни для группирующего, ни для группируемого элементов, однако он может дать информацию о таких элементах в контексте группирования по PROJ_ID:

SELECT PR0J_ID,

SUM (PROJECTED_BUDGET) AS T0TAL_BUDGET,

COUNT (DEPT_N0) AS NUM_DEPARTMENTS

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY PROJ_ID;

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

Например, внутренняя функция EXTRACT() оперирует с типами данных даты и времени и возвращает части даты - числа, которые выделяют год, месяц, день, час и т.д. из подобных типов данных. Следующий пример для версии 1.5 запрашивает таблицу MEMBERSHIP и выводит статистику, показывающую количество участников, объединенных в каждом месяце, независимо от года или для даты их объединения:

SELECT

MEMBER_TYPE,

EXTRACT (MONTH FROM JOINJDATE) AS MONTH_NUMBER, /* 1, 2, и т.д. */

COUNT (JOIN_DATE) AS MEMBERS_JOINED

FROM MEMBERSHIP

GROUP BY

MEMBER_TYPE, EXTRACT(MONTH FROM JOIN_DATE);

Большое количество полезных функций доступно в библиотеках внешних функций для преобразования дат, строк и чисел в элементы для группирования. Следующий пример иллюстрирует группирование при использовании некоторых функций, найденных в поставляемой библиотеке ib udf:

SELECT STRLEN(RTRIM(RDB$RELATION_NAME)),

COUNT(*)

FROM RDB$RELATIONS

GROUP BY STRLEN(RTRIM(RDB$RELATION_NAME))

ORDER BY 2;

Это будет работать в любой версии Firebird.

Отдельные выражения в настоящий момент недоступны в списке GROUP BY. Например, синтаксический анализатор отклоняет группирующий элемент, который содержит символ конкатенации ||. Следовательно, запрос

SELECT

PROJ_ID || '-1994' AS PROJECT,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY PROJ_ID || '-1994';

в Firebird 1.5 вернет следующее исключение:

ISC ERROR CODE:335544569

Token unknown - line 6, char 21

||

Использование порядкового номера поля выражения будет работать без проблем:

SELECT

PROJ_ID || '-1994' AS PROJECT,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY 1;

Использование порядкового номера выходного столбца в предложении GROUP BY "копирует" выражение из списка выбора (как это делает и предложение ORDER BY). Это означает, что когда порядковый номер указывает на подзапрос, этот подзапрос будет выполняться, по меньшей мере, дважды.

Подвыражение HAVING является фильтром для сгруппированного вывода, которое работает аналогично тому, как предложение WHERE фильтрует несгруппированный набор.

Условие HAVING использует в точности тот же синтаксис предикатов, что и условие WHERE, но его не следует путать с предложением WHERE. Фильтр HAVING применяется к группам после того, как набор будет разделен. Вам может еще понадобиться предложение WHERE для фильтрации входного набора.

В Firebird 1.0.x вы можете задать условие HAVING С использованием столбцов, которые не включены в состав группируемых элементов - "ленивое предложение WHERE" и "дефект" в терминах стандартных соглашений. Начиная с версии 1.5 в HAVING могут быть использованы только группируемые элементы.

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

Чтобы улучшить производительность, используйте условия WHERE для предварительной фильтрации групп, a HAVING используйте для фильтрации результатов, полученных от агрегатных выражений. Например, группа, полученная при использовании выражения SUM(X), может быть отфильтрована с помощью HAVING SUM(X) > <���минимальное-значение>. Следовательно, предложение HAVING обычно принимает агрегатное выражение в качестве своего аргумента.

Взяв предыдущий запрос, мы можем использовать предложение WHERE для фильтрации групп проектов, которые появляются в выходном наборе, и использовать предложение HAVING для установки начального диапазона сумм, которые мы хотим просматривать:

SELECT

PROJ_ID

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

AND PROJ_ID STARTING WITH 'M'

GROUP BY PROJ_ID

HAVING SUM(PROJECTED BUDGET) >= 100000;

PROJ_ID TOTAL_BUDGET

MAPDB 111000.00

MKTPR 1480000.00

Предложение HAVING может принимать сложные аргументы, содержащие логические операции AND и OR, которые используют ту же логику приоритетов, что и предложение WHERE.

Если вам нужно сгруппировать текстовый столбец с использованием последовательности сортировки, отличной от той, которая была определена по умолчанию для этого столбца, вы можете включить предложение COLLATE. Подробную информацию о COLLATE см. в разд. "Последовательность сортировки" главы 11.

Начиная с Firebird версии 1.5, элементы в списке ORDER BY В группирующем запросе должны быть либо агрегатными функциями, допустимыми в контексте группирования, либо элементами, которые представлены в списке GROUP BY.