Предложение GROUP BY

Предложение GROUP BY

Когда запрос включает предложение GROUP BY, TO спецификация столбца и таблицы, а именно:

SELECT {<список-столбцов>}

FROM <спецификация-таблицы>

[WHERE <условие-поиска>]

передается следующей стадии обработки, где строки разделяются на одну или более вложенных групп.

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

Его синтаксис:

SELECT <список-группируемых-полей> FROM <спецификация-таблицы>

[WHERE...]

GROUP BY <группирующий-элемент> [COLLATE последовательность-сортировки]

[, <группирующий-элемент> [. . ] ]

HAVING <предикат-группируемых-столбцов>

[ORDER BY .. .] ;

Список группируемых полей

Группа формируется путем объединения (агрегирования) всех строк, где столбец, указанный в списке столбцов и в предложении GROUP BY, имеет общее значение. Логика агрегирования означает, что заданный в SELECT список полей группирующего запроса резко ограничен полями, указанными в качестве аргументов в предложении GROUP BY. Поля, имеющие спецификации, соответствующие этим требованиям, часто называются группируемыми. Если вы задаете столбец или выражение поля, которые не являются группируемыми, то запрос будет отменен.

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

* Агрегатное выражение, оперирующее со столбцом базы данных, который не находится в предложении 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].

. ! .