ПРЕДЛОЖЕНИЯ GROUP BY и HAVING

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

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

Определить сколько в среднем получают сотрудники в зависимости от занимаемой ими должности

SELECT position, AVG (salary)

FROM staff

GROUP BY position;

На логическом уровне запрос выполняется следующим образом:

1. Сотрудники делятся на группы, по одной группе для каждой занимаемой должности. В каждой группе все сотрудники имеют одно и то же значение в столбце position.

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

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

На запросы, в которых используется группировка, накладываются дополнительные ограничения:

- столбцы с группировкой должны представлять собой реальные столбцы таблиц, перечисленных в предложении FROM.

- нельзя группировать строки на основании значения вычисляемого выражения.

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

- константа;

- статистическая функция, возвращающая одно значение для всех строк, входящих в группу;

- столбец группировки, который по определению имеет одно и то же значение во всех строках группы;

- выражение, включающее в себя перечисленные выше элементы.

На практике в список возвращаемых столбцов запроса с группировкой всегда входят столбец группировки и статистическая функция. Если последняя не указана, значит, запрос можно более просто выразить с помощью предиката DISTINCT без использования предложения GROUP BY. И наоборот, если не включить в результаты запроса столбец группировки, то невозможно будет определить, к какой группе относится каждая строка результатов. Другими словами, все имена столбцов в SELECT должны присутствовать и во фразе GROUP BY, за исключением случаев, когда имя столбца используется в обобщающей функции. Обратное утверждение неверно.

В стандарте ANSI/ISO определено, что два значения NULL в предложении GROUP BY равны. Если две строки имеют значение NULL в одинаковых столбцах группировки и идентичные значения во всех остальных столбцах группировки, то они помещаются в одну группу.

Условие отбора групп (предложение HAVING). Точно так же, как предложение WHERE используется для отбора отдельных строк, участвующих в запросе, предложение HAVING можно применить для отбора групп строк. Его формат соответствует формату предложения WHERE, т.е. состоит из ключевого слова HAVING, за которым следует условие отбора. Рассмотрим пример:

Подсчитать количество сотрудников работающих в каждом из офисов, исключив офисы, в которых работает менее 2 человек

SELECT bno, count (sno)

FROM staff

GROUP BY bno

HAVING COUNT sno>2;

Ограничения на условия отбора групп.Предложение HAVING используется для того, чтобы включать и исключать группы строк из результатов запроса, поэтому используемое в нем условие отбора применяется не к отдельным строкам, а к группе в целом, т.е. используется только совместно с GROUP BY. Это значит, что в условие отбора может входить:

- константа;

- статистическая функция, возвращающая одно значение для всех строк, входящих в группу;

- столбец группировки, который по определению имеет одно и то же значение во всех строках группы;

- выражение, включающее в себя перечисленные выше элементы.

На практике условие отбора предложения HAVING всегда должно включать в себя как минимум одну статистическую функцию. Если это не так, значит, условие отбора можно переместить в предложение WHERE. Чтобы определить, где следует указать условие отбора - в предложении WHERE или HAVING - необходимо вспомнить, как применяются эти предложения:

- предложение WHERE применяется к отдельным строкам, поэтому выражения, содержащиеся в нем, должны вычисляться для отдельных строк и в нем никогда не применяются статистические функции;

- предложение HAVING применяется к группам строк, поэтому выражения, содержащиеся в нем, должны вычисляться для групп строк. При этом стандарт требует, чтобы имена столбцов, использующиеся в HAVING, обязательно присутствовали в списке GROUP BY или применялись в статистических функциях.

Как и условия отбора в предложении WHERE, условие отбора в предложении HAVING может дать один из следующих результатов:

- если условие отбора имеет значение TRUE, группа строк остается и для нее генерируется одна строка результатов запроса;

- если условие отбора имеет значение FALSE или NULL, группа строк исключается, и строка для нее в результатах запроса не генерируется.