Сортировка и группирование данных

Литература

• Введение в SQL
• Введение в стандарты языка баз данных SQL
• Структуризированный язык запросов (SQL)
• Реализация языка SQL в СУБД MySQL
• PL/SQL в Oracle
• Двадцать пять заповедей SQL - Сергей Кузнецов
• Лекции по SQL" Карпукова - 227 стр.
• Краткое пособие по языку SQL

Содержание

• Зачем группировать данные?
• Ключевое слово GROUP BY
• Группирование выбранных данных
• Создание групп и использование итоговых функций
• Представление имен столбцов числами
• GROUP BY И ORDER BY
• Ключевое слово HAVING

Зачем группировать данные?

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

Предположим, что вам необходимо найти среднюю зарплату служащих по каждому из городов. Это можно сделать, применив к столбцу 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