Наконец, последним при вычислении табличного выражения используется раздел HAVING (если он присутствует).

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

Условие поиска раздела HAVING строится по тем же синтаксическим правилам, что и условие поиска раздела WHERE, и может включать те же самые предикаты. Однако имеются специальные синтаксические ограничения по части использования в условии поиска спецификаций столбцов таблиц из раздела FROM данного табличного выражения. Эти ограничения следуют из того, что условие поиска раздела HAVING задает условие на целую группу, а не на индивидуальные строки.

Поэтому в арифметических выражениях предикатов, входящих в условие выборки раздела HAVING , прямо можно использовать только спецификации столбцов, указанных в качестве столбцов группирования в разделе GROUP BY . Остальные столбцы можно специфицировать только внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX , вычисляющих в данном случае некоторое агрегатное значение для всей группы строк. Аналогично обстоит дело с подзапросами, входящими в предикаты условия выборки раздела HAVING : если в подзапросе используется характеристика текущей группы, то она может задаваться только путем ссылки на столбцы группирования.

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

HAVING COUNT

Выбрать коды товаров, покупаемых более чем одним покупателем:

SELECT stock FROM ordsale GROUP BY stock HAVING COUNT (*) > 1;

HAVING MIN

Получить значения минимального и максимального оклада для клерков каждого отдела, где самое низкое жалованье составляет менее $1,000:

SELECT deptno, MIN (sal), MAX (sal) FROM emp WHERE job = ‘CLERK’ GROUP BY deptno HAVING MIN (sal)

В предыдущей статье мы разобрали . Там я написал, что данная конструкция позволяет выбирать отдельные группы и для каждой группы вычислять функции, указанные после SELECT . А HAVING позволяет, согласно результату выполнения функций, отфильтровать лишние строки из групп. Разберём это подробнее.

Вспомним нашу предыдущую задачу, где мы вычисляли среднюю цену на молоко для конкретной сети супермаркетов. Давайте с Вами не просто посмотрим среднюю цену, но и при этом выведем только те сети супермаркетов, где средняя цена ниже 38 .

Для этой фильтрации по результатам выполнения агрегатной функции используется в SQL команда HAVING :

SELECT `shop_id`, AVG(`price`) FROM `table` GROUP BY `shop_id` HAVING AVG(`price`) < 38

В результате, вместо 4-х строк у нас будет лишь 3 :

shop_id	AVG(`price`)
1	37.5
2	36.0
3	37.0

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

Предложение HAVING используется в комбинации с предложением GROUP BY. Оно может быть использовано в операторе SELECT для фильтрации записей возвращемых предложением GROUP BY.

Синтаксис предложения HAVING

aggregate_function может быть функцией подобной SUM, COUNT, MIN, или MAX.

Пример использования функции SUM
Например, вы можете использовать функцию SUM для поиска названия отдела и суммы продаж (для соответствующих отделов). Предложение HAVING может выбрать только те отделы продажи которых больше $1000.

SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000 ;

Пример использования функции COUNT
Например, вы можете использовать функцию COUNT для выборки имени отдела и количество сотрудников (в соответствующем отделе) которые заработали более $25000 в год. Предложение HAVING отберет только те отделы, где таких работников более 10.

Пример использования функции MIN
Например, вы можете использовать функцию MIN для возврата названия отдела и минимальный доход этого отдела. Предложение HAVING вернет только те отделы у которых размер выручки начинается с $35000.

SELECT department, MIN(salary) AS "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) = 35000 ;

Пример использования функции MAX
Например, вы также можете использовать функцию для выборки имени отдела и максимальногй выручки отдела. Предложение HAVING вернет только те отделы, чей максимальный доход менее $50000.

SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department HAVING MAX(salary) < 50000 ;

Пример 2. База данных и таблица - те же, что и в примере 1.

Используя операторы SQL HAVING и GROUP BY, требуется вывести категории, в какой-либо части которых минимальное количество поданных объявлений не превышает 100. Для этого пишем следующий запрос:

SELECT Category, Units, MIN (Units) AS Minimum FROM Ads GROUP BY Category HAVING MIN (Units)

Пример 3. База данных и таблица - те же, что и в предыдущих примерах.

Используя операторы SQL HAVING и GROUP BY, требуется вывести категории объявлений, в которых более двух частей. Пишем следующий запрос:

SELECT Category, Part FROM Ads GROUP BY Category HAVING COUNT (*)>2

Результатом будет следующая таблица:

Category	Part
Досуг	Книги
Досуг	Музыка
Досуг	Игры
Недвижимость	Квартиры
Недвижимость	Дачи
Недвижимость	Дома

Самостоятельно поработайте с таблицей Staff (штатные сотрудники) базы данных фирмы. В ней есть столбцы Name (фамилия), Dept (номер отдела), Years (длительность трудового стажа) и Salary (размер заработной платы). Примеры для самостоятельного решения со ссылками для проверки решения - после таблицы.

Name	Dept	Years	Salary
Sanders	20	7	18357.5
Junkers	15	6	16232.8
Moonlight	15	8	21500.6
Pernal	20	8	18171.2
Aisen	15	7	19540.7
McGregor	15	7	15790.8
Marenghi	38	5	17506.8
Doctor	20	5	12322.4
Factor	38	8	16228.7

Пример 5. Определить номера отделов, в которых средний трудовой стаж сотрудников больше 6.5 лет.

Оператор SQL HAVING и сравнение со значением, возвращаемым квантором ALL или ANY (SOME)

Оператор SQL HAVING можно использовать для выборки данных, соответствующим результатам сравнения не только с заданным числом, но и со значением, возвращаемым квантором ALL или ANY (SOME). Квантор ALL возвращает из запроса, к которому он применяется, максимальное значение и тогда при помощи оператора HAVING происходит сравнение с максимальным значением. Например, ALL(10, 15, 20) вернёт 20. Квантор ANY (и его аналог SOME) возвращает минимальное значение и тогда при помощи оператора HAVING происходит сравнение с минимальным значением. Синтаксис запроса с оператором SQL HAVING, определяющий сравнение со значением, возвращаемым квантором ALL или ANY (SOME) выглядит следующим образом:.

SELECT ИМЕНА_СТОЛБЦОВ FROM ИМЯ_ТАБЛИЦЫ GROUP BY ИМЯ_СТОЛБЦА HAVING АГРЕГАТНАЯ_ФУНКЦИЯ (ИМЯ СТОЛБЦА) ОПЕРАТОР_СРАВНЕНИЯ КВАНТОР (SELECT АГРЕГАТНАЯ_ФУНКЦИЯ (ИМЯ СТОЛБЦА) FROM ИМЯ_ТАБЛИЦЫ GROUP BY ИМЯ_СТОЛБЦА)

Пример 7. Есть база данных "Театр". В ней есть таблица Play, содержащая данные о постановках в театре. В этой таблице есть поля PlayID (идентификатор), Name (название), Genre (жанр), Author (автор), Dir_ID (внешний ключ - идентификатор режиссёра), PremiereDate (дата премьеры), LastDate (дата окончания). Требуется определить самый популярный жанр театра, то есть жанр, в котором поставлено наибольшее количество постановок.

Используя операторы SQL HAVING и GROUP BY, пишем первую часть запроса к таблице Play, которая задаёт сравнение числа строк, сгруппированных по жанру:

SELECT Genre FROM Play GROUP BY Genre HAVING COUNT (*) >=

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

ALL (SELECT COUNT (*) FROM PLAY GROUP BY Genre)

Весь запрос для определения самого популярного жанра в театре будет следующим:

SELECT Genre FROM Play GROUP BY Genre HAVING COUNT (*) >= ALL (SELECT COUNT (*) FROM PLAY GROUP BY Genre)

Оператор SQL HAVING в соединениях таблиц

Вернёмся к запросам с оператором SQL HAVING, в которых сравнение проводится с заданным числом, как в первом параграфе. Но усложним задачу. На практике часто число строк в результате запроса определяется внешним ключом, ссылающимся на другую таблицу..

Пример 8. Продолжаем работать с базой данных "Театр". Нам понадобятся таблицы Play, содержащая данные о постановках в театре и Team, содержащая данные о ролях актёров. Требуется вывести список моноспектаклей (спектаклей с одним актёром). Ниже приведена схема базы данных "Театр" (для увеличения рисунка нажать на него левой кнопкой мыши).

Если ещё точнее, нам нужно выбрать спектакли, в которых лишь одна роль. Среди полей в таблице Team есть PlayID - внешний ключ, ссылающийся на таблицу Play. В каждой записи таблицы Team этот внешний ключ определяет постановку, в которой исполняется данная роль. Если мы соединим таблицы Play и Team по ключу PlayID, то мы сможем определить количество ролей в постановках. Так как мы соединяем две таблицы, а не больше, то для простоты можем использовать соединение без оператора JOIN, перечисляя таблицы через запятую, а для обозначения условия соединения используя слово WHERE.

C оператором HAVING используем агрегатную функцию COUNT - для подсчёта числа ролей в каждой постановке. Весь запрос для определения спектаклей с одной ролью, а следовательно, с одним актёром будет следующим:

Написать запросы с оператором SQL HAVING самостоятельно, а затем посмотреть решения

Пример 9. Продолжаем работать с базой данных "Театр". Вывести список актеров, которые в одном спектакле играют более одной роли, и количество их ролей.

Использовать оператор JOIN. Естественно, использовать HAVING, GROUP BY.

Реляционные базы данных и язык SQL

Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций . Стандартом предусмотрены следующие агрегатные функции:

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(<имя поля>) состоит в том, что вторая при подсчете не учитывает NULL-значения.

Пример. Найти минимальную и максимальную цену на персональные компьютеры:

Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:

Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):

Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).

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

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

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

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM) . Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT , должны быть включены в агрегатные функции , и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY . В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После этого к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.
Если при наличии предложения GROUP BY , в предложении SELECT отсутствуют агрегатные функции , то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:

SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model;

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

model	Qty_model	Avg_price
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).

Существует несколько определенных правил выполнения агрегатных функций :

• Если в результате выполнения запроса не получено ни одной строки (или не одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других функций - NULL.
• Аргумент агрегатной функции не может сам содержать агрегатные функции (функция от функции). Т.е. в одном запросе нельзя, скажем, получить максимум средних значений.
• Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений.
• Если при выполнении функции SUM был получен результат, превышающий максимальное значение используемого типа данных, возникает ошибка .

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

Предложение HAVING

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