W Cat - SQL за 24 часа
Группирование данных - это размещение данных в столбцах с повторяющимися значениями в определенном логичном порядке. Например, в базе данных содержится информация о служащих. Служащие могут жить в разных городах, но многие из них живут в одном городе. Вполне вероятно, что вам может понадобиться информация по каждому конкретному городу и живущих там служащих. Для этого вы группируете информацию о служащих по городам - и соответствующий отчет готов!
Предположим, что вам необходимо найти среднюю зарплату служащих по каждому из городов. Это можно сделать, применив к столбцу SALARY итоговую функцию AVG, а затем использовав GROUP BY для группирования выводимых данных по городам.
Группирование данных осуществляется с помощью выражения GROUP BY в операторе SELECT. На предыдущем уроке были рассмотрены итоговые функции. В ходе данного урока мы с вами научимся использовать итоговые функции в совокупности с выражением GROUP BY, чтобы лучше организовать представляемые данные.
Ключевое слово GROUP BY
Ключевое слово GROUP BY используется в операторе SELECT для того, чтобы объединять повторяющиеся значения в группы. Ключевое слово GROUP BY должно следовать за выражением WHERE и предшествовать ключевому слову ORDER BY.
Вот какая должна быть последовательность ключевых слов в операторе, выполняющем запрос:
SELECT FROM WHERE GROUP BY ORDER BY
Ключевое слово GROUP BY должно следовать за условиями в выражении ключевого слова WHERE и предшествовать ключевому слову ORDER BY, если последнее имеется.
SELECT столбец1, столбец2
FROM таблица1, таблица2 WHERE условия
GROUP BY столбец1, столбец2
ORDER BY столбец1, столбец2
В следующих разделах рассматривается множество примеров использования ключевого слова GROUP BY в самых разных ситуациях.
Группирование выбранных данных
Группировать данные просто. В выражении ключевого слова GROUP BY могут использоваться только выбранные столбцы (т. е. столбцы из списка ключевого слова SELECT в операторе запроса). Если имя столбца не указано в списке ключевого слова SELECT, то имя этого столбца в выражении ключевого слова GROUP BY использовать нельзя. Это логично - как группировать в отчете данные, которых в нем нет?
Но если столбец выбран, то его имя должно быть включено в выражение ключевого слова GROUP BY. Имя столбца можно представить и его номером, о чем мы поговорим немного позже. При группировании данных порядок группирования столбцов не обязан совпадать с порядком, заданным в выражении ключевого слова SELECT.
К функциям группирования - функциям, используемым в выражении ключевого слова GROUP BY для объединения данных в группы, - относятся AVG, MAX, MIN, зим и COUNT. Это итоговые функции, о которых вы узнали из урока 9, "Подведение итогов по данным запроса" В ходе урока 9 итоговые функции использовались по отношению ко всем данным столбца, а здесь мы рассмотрим использование итоговых функций для группирования повторяющихся значений.
Создание групп и использование итоговых функций
При использовании в операторе SELECT ключевого слова GROUP BY должны соблюдаться некоторые правила. В частности, имена выбранных для отображения столбцов должны присутствовать и в выражении ключевого слова GROUP BY, за исключением тех, к которым применены итоговые функции. Столбцы в выражении ключевого слова GROUP BY не обязательно должны быть представлены в том же порядке, что и в выражении ключевого слова SELECT. Но если имя столбца указано в выражении ключевого слова SELECT, имя этого столбца должно присутствовать и в выражении ключевого слова GROUP BY. Вот несколько примеров использования оператора SELECT с ключевым словом GROUP BY.
Пример
SELECT EMP_ID, CITY
FROM EMPLOYEEJTBL
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 EMPLOYEEJTBL ;
CITY
GREENWOOD
INDIANAPOLIS
WHITELAND
INDIANAPOLIS
INDIANAPOLIS
INDIANAPOLIS
6 строк выбраны.
В следующем примере подсчитывается число записей по каждому городу. Именно из-за того, что используется ключевое слово GROUP BY, вы здесь видите результаты по каждому из городов в отдельности.
SELECT CITY, COUNT(*)
FROM EMPLOYEE_TBL
GROUP BY CITY;
CITY COUNT(*)
GREENWOOD 1
INDIANAPOLIS 4
WHITELAND 1
3 строки выбраны.
Следующий запрос осуществляет выборку из временной таблицы, созданной на основе таблиц 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
6 строк выбраны.
В следующем примере с помощью функции 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
3 строки выбраны.
В следующем примере для группирования данных комбинируется использование нескольких компонентов запроса. Необходимо получить опять же средние значения для почасовой оплаты и зарплаты, но только для городов Индианаполис и Уайтленд. Для этого данные группируются по полю 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.
В завершение раздела рассмотрим использование в выражении ключевого слова ORDER 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
3 строки выбраны.
Представление имен столбцов числами
В отличие от выражения ключевого слова ORDER BY, в выражении ключевого слова GROUP BY указать порядок столбцов с помощью их номеров нельзя - за исключением того случая, когда используется ключевое слово UNION и имена всех столбцов разные. Вот пример использования номеров вместо имен столбцов.
SELECT EMP_ID, SUM(SALARY)
FROM EMPLOYEE_PAY_TBL
UNION
SELECT EMP_ID, SUM(PAY_RATE)
FROM EMPLOYEE_PAY_TBL
GROUP BY 2, 1;
Этот оператор SQL возвращает табельный номер служащего (EMP_ID) и группирует суммы по значениям зарплаты. При использовании ключевого слова UNION результаты двух операторов SELECT объединяются. Группирование выполняется сначала по столбцу 2Б, представляющем зарплату (SALARY), а затем по столбцу 1, представляющем табельный номер служащего (EMP_ID).
GROUP BY И ORDER BY
Обратите внимание на то, что GROUP BY и ORDER BY работают одинаково в том смысле, что оба эти ключевые слова задают сортировку данных. В выражении ключевого слова ORDER BY задается сортировка данных запроса, а в выражении ключевого слова GROUP BY - сортировка этих данных по группам. Поэтому ключевое слово GROUP BY можно использовать для сортировки точно так же, как и ORDER BY.
Вот несколько особенностей использования ключевого слова GROUP BY для сортировки.
• Все выбранные столбцы, к которым не применяются итоговые функции, должны быть указаны в списке ключевого слова GROUP BY.
• В отличие от выражения ключевого слова ORDER BY, в выражении ключевого слова GROUP BY имена столбцов нельзя заменить числами.
