Группировка записей. Предложение 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.
Пример. Получить количество ПК и среднюю цену для каждой модели при условии, что средняя цена менее $800:
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price
FROM PC
GROUP BY model
HAVING AVG(price) < 800;
В результате выполнения запроса получим:
|
model |
Qty_model |
Avg_price |
|---|---|---|
|
1232 |
4 |
425.0 |
|
1260 |
1 |
350.0 |
Заметим, что в предложении HAVING нельзя использовать псевдоним (Avg_price), используемый для именования значений агрегатной функции. Дело в том, что предложение SELECT, формирующее выходной набор запроса, выполняется предпоследним перед предложением ORDER BY.
Ниже приведен порядок обработки предложений в операторе SELECT:
* FROM
* WHERE
* GROUP BY
* HAVING
* SELECT
* ORDER BY
Этот порядок не соответствует синтаксическому порядку общего формата оператора SELECT, представленному ниже:
SELECT [DISTINCT | ALL]{*
| [ [[AS] ]] [,…]}
FROM [[AS] ] [,…]
[WHERE ]
[[GROUP BY ]
[HAVING ] ]
[ORDER BY ]
Использование в запросе нескольких таблиц. Предложение FROM
Как видно из приведенного в конце предыдущего раздела синтаксиса оператора SELECT, в предложении FROM допускается указание нескольких таблиц. Простое перечисление таблиц практически не используется, поскольку оно соответствует реляционной операции декартова произведения. Т.е. в результирующем наборе каждая запись из одной таблицы будет сочетаться с каждой записью в другой. Например, для таблиц
|
A |
|
B |
||
|---|---|---|---|---|
|
a |
b |
|
c |
d |
|
1 |
2 |
|
2 |
4 |
|
2 |
1 |
|
3 |
3 |
Результат запроса
SELECT * FROM A, B;
будет выглядеть следующим образом:
|
a |
b |
c |
d |
|---|---|---|---|
|
1 |
2 |
2 |
4 |
|
1 |