Книга: Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Группирующий элемент

Группирующий элемент

Предложение GROUP BY получает список группирующих элементов:

* в Firebird 1.0.x группирующим элементом может быть только имя столбца или соответствующее выражение внешней функции (UDF);

* Firebird 1.5 расширил диапазон группирующих элементов, добавив также номер, порядковое числительное, представляющее номер позиции (слева направо) соответствующего элемента из списка полей SELECT. Этот номер может быть использован вместе с существующей возможностью для аргументов ORDER BY;

* версии 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). Это означает, что когда порядковый номер указывает на подзапрос, этот подзапрос будет выполняться, по меньшей мере, дважды.

Оглавление книги