Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

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

! ! !

ВНИМАНИЕ! В Firebird 1.0.x и InterBase требования к допустимости группирования являются менее ограничивающими. Группирующие запросы и клиентские вызовы хранимых процедур могут вызывать исключения, которые ранее не появлялись. Это давняя ошибка, о которой вы должны знать при миграции ваших старых приложений в Firebird версии 1.5 и выше.

. ! .

Агрегатные выражения

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

Агрегатными функциями являются SUMO, которая вычисляет итоги, MINO и MAXO, отыскивающие наименьшее и наибольшее значение соответственно, и AVGO, вычисляющая среднее значение. Функция COUNT() также ведет себя как агрегатная функция в группирующих запросах, возвращая счетчик строк для всех строк ниже контекста (уровня)группы.

В отличие от других группируемых элементов агрегатное выражение в списке SELECT не может быть использовано как элемент группы (см. разд. "Элемент группирования"), поскольку оно возвращает значение, которое вычисляется из значений на нижнем уровне группы.

Таблица PROJ_DEPT_BUDGET содержит строки пересечения проектов и отделов. Мы заинтересованы в отыскании итога по бюджетам, выделенным каждому проекту, независимо от отдела. Следующий список элементов, который рассматривался ранее в этом разделе, задает список полей из двух нужных нам элементов:

SELECT

PROJ_ID,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY PROJ_ID;

Эти две спецификации поля хороши в качестве группируемых элементов. Идентификатор отдела (DEPT_NO) не присутствует в списке, потому что список задает нужные нам по проекту итоги. Для получения этих итогов мы используем аргумент PROJ_ID В предложении GROUP BY.

С другой стороны, если мы хотим получить список бюджетов отделов, независимых от проектов, список полей должен включать DEPT_NO, чтобы он был аргументом в предложении GROUP BY:

SELECT

DEPT_NO,

SUM(PROJECTED_BUDGET) AS TOTAL_BUDGET

FROM PROJ_DEPT_BUDGET

WHERE FISCAL_YEAR = 1994

GROUP BY DEPT_NO;

Влияние NULL на агрегатные выражения

В агрегатных выражениях типа SUMO, AVG() и COUNT(<имя-столбца>) строки, содержащие NULL в соответствующем столбце, игнорируются. Функция AVG() создает числитель, суммируя все непустые значения, и знаменатель, подсчитывая строки, содержащие непустые значения.

! ! !

ПРИМЕЧАНИЕ. Если у вас есть столбцы, по которым вы собираетесь вычислять среднее значение, важно решить в процессе проектирования, как при вычислении среднего значения вы будете трактовать "пустые" экземпляры - как NULL (они будут исключены из вычисления) или как ноль. Вы можете реализовать нужное правило, используя значение по умолчанию или (лучше) триггер BEFORE INSERT[86].

. ! .

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

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

Подвыражение HAVING

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

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

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

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