Название: Базы данных. Концепция баз данных, реляционная модель данных, языки SQL и XML (Токмаков Г. П.)

Жанр: Информационные системы и технологии

Просмотров: 1417


4.3. запросы с группировкой

 

4.3.1. П РЕДЛОЖЕНИЕ  GROUP BY

 

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

Эту возможность предоставляет предложение GROUP BY инструкции SELECT.

Назначение предложения GROUP BY лучше всего можно понять на примере.

Пример. Какова средняя стоимость заказа для каждого сотрудника ком-

пании?

 

SELECT ID_SLZH, AVG(PRICE) FROM ZAKAZY

GROUP BY ID_SLZH

 

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

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

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

 

4.3.2. П РЕДЛОЖЕНИЕ  HAVING

 

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

HAVING <агрегатная функция> <отношение> <значение>

агрегатная функция − одна из функций MIN, MAX, AVG, SUM;

отношение − одна из операций отношения =, <>, <, >, <=, >=;

значение −  константа, результат вычисления выражения или  единичное значение, возвращаемое вложенным оператором SELECT.

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

как в предложении WHERE такие функции указывать нельзя.

Агрегатные функции могут применяться как в выражении вывода резуль-

татов строки SELECT, так и в выражении условия обработки сформированных групп HAVING. В этом случае каждая агрегатная функция вычисляется для каж-

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

или для условия отбора групп.

Пример. Построить запрос, который выводит среднюю стоимость заказа для каждого служащего из числа тех, у кого общая стоимость заказа превы- шает $30 000?

 

SELECT ID_SLZH, AVG(PRICE) FROM ZAKAZY

GROUP BY ID_SLZH

HAVING SUM(PRICE) > 30 000.00

 

4.3.3. П РЕДЛОЖЕНИЕ  ORDER  BY − ОПРЕДЕЛЕНИЕ  СОРТИР О ВКИ

 

Строки результатов запроса, как и строки таблицы базы данных, не имеют определенного порядка. Но включив в инструкцию SELECT предложение OR‐ DER BY, можно отсортировать результаты запроса.

Результирующий набор данных можно отсортировать с помощью предло-

жения

 

ORDER BY <список_столбцов>

 

список_столбцов − содержит имена столбцов, по которым будет произво- диться сортировка. Если указаны два и более столбцов, первый столбец будет использован для глобальной сортировки, второй столбец для сортировки внут- ри группы, определяемой единым значением первого столбца, и т. д. Например, результаты следующего запроса отсортированы по двум столбцам, REGION, CITY.

Пример. Показать физические объемы продаж для каждого офиса, от-

сортированные в алфавитном порядке по названиям регионов и в каждом ре-

гионе  по названиям городов.

 

SELECT          CITY, REGION, SALES FROM OFFISY

ORDER BY REGION, CITY

В предложении ORDER BY можно выбрать возрастающий или убывающий порядок сортировки. По умолчанию данные сортируются в порядке возраста- ния. Чтобы сортировать их по убыванию, следует включить в предложение сор-

тировки ключевое слово DESC, как это сделано в следующем примере.

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

 

SELECT CITY, REGION, SALES FROM OFFISY

ORDER BY SALES DESC

 

4.3.4.  П РАВИЛА ВЫПОЛ НЕНИЯ З АПРОС ОВ С ГРУППИРОВК О Й

 

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

– константа;

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

щих в группу;

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

ние во всех строках группы;

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

Запросы с группировкой и с условиями отбора групп выполняются сле-

дующим образом.

1. Взять таблицу, указанную в предложении FROM.

2. Если имеется предложение WHERE, применить заданное в нем условие от- бора к каждой строке таблицы. Если при этом получается значение TRUE, то те- кущая строка добавляется в результирующую таблицу, если получается значе-

ние FALSE, то строка отбрасывается.

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

группировки (т. е. в столбцах, указанных за ключевым словом GROUP BY).

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

это условие выполняется.

5. Для каждой из оставшихся групп строк вычисляются значения агрегат-

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

ния агрегатных функций, указанных в предложении SELECT.