Книга: 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)
Использование COUNTQ в качестве агрегатной функции
Очень "вредная" функция 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;
PROJ_ID |
TOTAL_BUDGET |
NUM_DEPARTMENTS |
====== |
====== |
====== |
GUIDE |
650000.00 |
2 |
HWRII |
520000.00 |
3 |
MAPDB |
111000.00 |
3 |
MKTPR |
1480000.00 |
5 |
VBASE |
2600000.00 |
3 |
Неагрегатные выражения
Возможность использования для группирования выражений внутренних и внешних функций открывает широкий диапазон возможностей преобразования хранимых атрибутов для генерации выходных наборов, которые нельзя получить другим путем.
Например, внутренняя функция 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). Это означает, что когда порядковый номер указывает на подзапрос, этот подзапрос будет выполняться, по меньшей мере, дважды.
- Создание свободно позиционируемых элементов
- 6.4. Рабочий лист Excel и его структурные элементы
- Письма с элементами графики и вложениями
- Элемент STYLE
- 4.4. Логические элементы и синтез логических схем
- 4.7 Структурные элементы SAN
- Добавление, изменение и удаление элементов списка
- Восстановление элементов списка из Корзины
- Вложение файлов в элементы списков
- Логические элементы
- 5.2. Основные приемы работы, элементы текстового редактора
- Правило 16. Группируйте связанные между собой элементы