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

Группирование выбранных данных

Группировать данные просто. В выражении ключевого слова GROUP BY могут использоваться только выбранные столбцы (т. е. столбцы из списка ключевого слова SELECT в операторе запроса). Если имя столбца не указано в списке ключевого слова SELECT, то имя этого столбца в выражении ключевого слова GROUP BY использовать нельзя. Это логично – как группировать в отчете данные, которых в нем нет?

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

К функциям группирования – функциям, используемым в выражении ключевого слова GROUP BY для объединения данных в группы, – относятся AVG, MAX, MIN, SUM и COUNT. Это итоговые функции, о которых вы узнали из «Подведение итогов по данным запроса». При этом итоговые функции использовались по отношению ко всем данным столбца, а здесь мы рассмотрим использование итоговых функций для группирования повторяющихся значений.

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

Пример

SELECT EMP_ID, CITY FROM EMPLOYEE_TBL GROUP BY CITY, EMP_ID;

В этом операторе SQL из таблицы EMPLOYEE_TBL выбираются столбцы EMP_ID и CITY, а данные последних выводятся сгруппированными сначала по CITY, а затем по EMP_ID.

Обратите внимание на порядок выбора столбцов и на порядок столбцов в выражении ключевого слова GROUP BY

Пример

SELECT EMP_ID, SUM (SALARY) FROM EMPLOYEE_PAY_TBL GROUP BY SALARY, EMP_ID;

Этот оператор SQL возвращает данные столбца EMP_ID и сумму по группам зарплат, созданным по величине зарплаты (SALARY) и табельному номеру (EMP_ID).

Пример

SELECT SUM (SALAPY) FROM EMPLOYEE_PAY_TBL;

Здесь оператор SQL возвращает сумму всех выплат по зарплате из таблицы ЕМPLOYEE_PAY_TBL.

Пример

SELECT SUM (SALARY) FROM EMPLOYEE_PAY_TBL GROUP BY SALARY;

Здесь оператор SQL возвращает суммы по группам, созданным по всем уровням зарплаты.

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

SELECT CITY FROM EMPLOYEE_TBL;

CITY

GREENWOOD

INDIANAPOLIS

WHITELAND

INDIANAPOLIS

INDIANAPOLIS

INDIANAPOLIS

В следующем примере подсчитывается число записей по каждому городу. Именно из-за того, что используется ключевое слово GROUP BY, вы здесь видите результаты по каждому из городов в отдельности.

SELECT CITY, COUNT (*) FROM EMPLOYEE_TBL GROUP BY CITY;

CITY COUNT(*)

GREENWOOD 1

INDIANAPOLIS 4

WHITELAND 1

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

SELECT * FROM EMP_PAY_TMP;

CITY LAST_NAME FIRST_NAME PAY_RATE SALARY

GREENWOOD STEPHENS TINA 30000

INDIANAPOLIS PLEW LINDA 14.75

WHITELAND GLASS BRANDON 40000

INDIANAPOLIS GLASS JACOB 20000

INDIANAPOLIS WALLACE MARIAH 11

INDIANAPOLIS SPURGEON TIFFANY 15

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

SELECT CITY, AVG (PAY_RATE), AVG (SALARY) FROM EMP_PAY_TMP GROUP BY CITY;

CITY AVG (PAY_RATE) AVG (SALARY}

GREENWOOD 30000

INDIANAPOLIS 13.5833333 20000

WHITELAND 40000

В следующем примере для группирования данных комбинируется использование нескольких компонентов запроса. Необходимо получить средние значения для почасовой оплаты и зарплаты, но только для городов Индианаполис и Уайтленд. Для этого данные группируются по полю CITY – другого выбора здесь нет, поскольку иначе из выбранных столбцов используется итоговая функция. Наконец, отчет упорядочивается сначала по столбцу 2, а затем по столбцу 3, т. е. по средней почасовой оплате и средней зарплате. Попытайтесь до конца разобраться в показанном ниже операторе и выведенных данных.

SELECT CITY, AVG (PAY_RATE), AVG (SALARY)

FROM EMP_PAY_TMP

WHERE CITY IN ('INDIANAPOLIS', 'WHITELAND')

GROUP BY CITY

ORDER BY 2, 3;

CITY AVG (PAY_RATE) AVG (SALARY)

INDIANAPOLIS 13.5833333 20000

WHITELAND 40000

Значения сортируются так, что значения NULL оказываются в конце. Поэтому запись для города Индианаполис представлена первой. Город Гринвуд не был выбран, но если бы был, то соответствующая ему запись была бы представлена перед записью для Уайтленда, поскольку для Гринвуда средняя зарплата (SALARY) равна 30000, а средняя зарплата является вторым параметром сортировки в выражении ключевого слова ORDER BY.

В завершение раздела рассмотрим использование в выражении ключевого слова GROUP BY итоговых функций МАХ и MIN.

SELECT CITY, MAX (PAY_RATE), MIN (SALARY) FROM EMP_PAY_TMP GROUP BY CITY;

CITY MAX (PAY_RATE) MIN (SALARY)

GREENWOOD 30000

INDIANAPOLIS 15 20000

WHITELAND 40000