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

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

Основными на этом уроке будут следующие темы.

• Зачем группировать данные?
• Выражение 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

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 имена столбцов нельзя заменить числами.
• Использовать ключевое слово GROUP BY вообще нет необходимости, если не используются итоговые функции.

Вот пример использования для сортировки данных ключевого слова GROUP BY вместо ключевого слова ORDER BY:

SELECT LAST_NAME, FIRST_NAME, CITY 

FROM EMPLOYEE_TBL 

GROUP BY LAST_NAME;

SELECT LAST_NAME, FIRST_NAME, CITY

                       *

ERROR at line 1:

ORA-00979: not a GROUP BY expression

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

В следующем примере проблема предыдущего оператора решена путем добавления в список ключевого слова GROUP BY недостающих имен из списка ключевого слова SELECT.

SELECT IAST_NAME, FIRST_NAME, CITY

FROM EMPLOYEE_TBL

GROUP BY LAST_NAME, FIRST_NAME, CITY;

LAST_NAME       FIRST_NAME        CITY

GLASS             BRANDON         WHITELAND

GLASS             JACOB           INDIANAPOLIS

PLEW              LINDA           INDIANAPOLIS

SPURGEON          TIFFANY         INDIANAPOLIS

STEPHENS          TINA            GREENWOOD

WALLACE           MARIAH          INDIANAPOLIS

6 строк выбраны.

В этом примере выбираются те же данные из той же таблицы, но уже все столбцы перечислены в выражении ключевого слова GROUP BY в том же порядке, в каком они указаны в списке ключевого слова SELECT. В результате данные показаны отсортированными сначала по столбцу LAST_NAME, затем по столбцу FIRST_NAME и наконец, по столбцу CITY. С помощью ключевого слова ORDER BY то же самое получить легче, но для правильного использования ключевого слова GROUP BY, наверное, будет полезно разобраться, как с его помощью сортируются данные, перед тем, как выполнить группирование результатов.

В следующем примере получается выборка из таблицы EMPLOYEE_TBL и используется ключевое слово GROUP BY для упорядочения данных по значениям столбца CITY.

SELECT CITY, LAST_HAME

FROM EMPLOYEEJTBL

GROUP BY CITY, LAST_NAME;

CITY                 LAST_NAME

GREENWOOD            STEPHENS

INDIANAPOLIS         GLASS

INDIANAPOLIS         PLEW

INDIANAPOLIS         SPURGEON

INDIANAPOLIS         WALLACE

WHITELAND            GLASS

6 строк выбраны.

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

SELECT CITY, COUNT(*) 

FROM EMPLOYEEJTBL 

GROUP BY CITY 

ORDER BY 2,1;

CITY           COUNT(*)

GREENWOOD        1

WHITELAND        1

INDIANAPOLIS     4

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

Хотя ключевые слова GROUP BY и ORDER BY и функционируют подобным образом, между ними имеется существенное отличие. Ключевое слово GROUP BY предназначено для группирования одинаковых значений, а задачей ORDER BY является представление данных просто в определенном порядке. Ключевые слова GROUP BY и ORDER BY можно использовать в одном операторе SELECT, но каждое из них должно выполнять свою задачу. В одном операторе SELECT ключевое слово GROUP BY должно предшествовать ключевому слову ORDER BY.

Ключевое слово GROUP BY можно использовать для сортировки данных в операторе CREATE VIEW, а вот ключевое слово ORDER BY использовать в операторе CREATE VIEW нельзя. Оператор CREATE VIEW будет подробно рассматриваться в ходе урока 20, "Создание и использование представлений и синонимов".

  Ключевое слово HAVING

Ключевое слово HAVING используется в операторе SELECT вместе с ключевым словом GROUP BY, чтобы указать какие из групп должны быть представлены в выводе. Для GROUP BY ключевое слово HAVING играет ту же роль, что и WHERE для ORDER BY. Другими словами, WHERE задает условия для значений из выбранных столбцов, а HAVING задает условия для групп, создаваемых с помощью GROUP BY.

Ключевое слово HAVING в операторе SELECT должно следовать за выражением ключевого слова GROUP BY и тоже предшествовать ключевому слову ORDER BY, если последнее используется.

Синтаксис оператора SELECT, в котором используется ключевое слово HAVING, следующий.

SELECT столбец1, столбец2

FROM таблица1, таблица2

WHERE условия

GROUP BY столбец1, столбец2

HAVING условия

ORDER BY столбец1, столбец2

В следующем примере выбираются средние значения для почасовой нормы оплаты и для зарплаты по всем городам, кроме Гринвуда. Данные группируются по значениям столбца CITY, но отображаются только те группы (города), для которых средняя зарплата превышает 20000. Результат отсортирован по значению средней зарплаты для городов.

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

FROM EMP_PAY_TMP

WHERE CITY <> 'GREENWOOD'

GROUP BY CITY

HAVING AVG(SALARY) > 20000

ORDER BY 3;

CITY          AVG(PAY_RATE)       AVG(SALARY)

WHITELAND                           40000

 1 строка выбрана. 

Почему здесь оказалась выбрана только одна строка? По следующим причинам.

• Город Гринвуд исключен выражением ключевого слова WHERE.
• Данные по городу Индианаполис не представлены ввиду того, что средняя зарплата для этого города равна 20000, что не превышает необходимые 20000.

  Резюме

Итак, вы теперь знаете, как группировать результаты запроса с помощью ключевого слова GROUP BY. Ключевое слово GROUP BY используется, главным образом, с итоговыми функциями SQL типа SUM, AVG, MAX, MIN и COUNT. Ключевое слово GROUP BY подобно ключевому слову ORDER BY в том смысле, что оба они сортируют выводимые данные. Ключевое слово GROUP BY предназначено для сортировки данных по группам, но может использоваться и для обычной сортировки данных, хотя последнее проще сделать с помощью ключевого слова ORDER BY.

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

  Вопросы и ответы

При использовании ключевого слова ORDER BY в операторе SELECT обязательно ли использовать ключевое слово GROUP BY?

Нет. Ключевое слово GROUP BY в операторе SELECT является необязательным, но оно может оказаться очень полезным при использовании ORDER BY.

Что такое групповое значение?

Рассмотрим столбец CITY таблицы EMPLOYEE_TBL. Если выбрать имена служащих (LAST_NAME) и города (CITY) и эти данные сгруппировать по городам, строки для одинаковых названий городов будут отображены вместе.

Чтобы сгруппировать данные запроса по некоторому столбцу в выражении ключевого слова GROUP BY, должен ли этот столбец быть указан в списке ключевого слова SELECT?

Обязательно. Для того чтобы группировать данные по столбцу, его имя должно быть указано в списке ключевого слова SELECT.

  Практикум

Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".

  Тесты

1. Будут ли работать следующие операторы SELECT? Если нет, то что в них следует исправить?

_а. SELECT SUM(SALARY), EMP_ID FROM EMPLOYEE_PAY_TBL GROUP BY 1 AND 2;

6. SELECT EMP_ID, MAX(SALARY) FROM EMPLOYEE_PAY_TBL GROUP BY SALARY, EMP_ID;

B. SELECT EMP_ID, COUNT(SALARY) FROM EMPLOYEE_PAY_TBL ORDER BY EMP_ID GROUP BY SALARY; -

2. Верно ли следующее утверждение: "При использовании ключевого слова HAVING необходимо использовать также и ключевое слово GROUP BY?"

3. Верно ли следующее утверждение: "Следующий оператор SQL возвратит суммы зарплат по группам"

SELECT SUM(SALARY) FROM EMPLOYEE_PAY_TBL

4. Верно ли следующее утверждение' "Выбранные в запросе столбцы должны присутствовать в списке ключевого слова GROUP BY в том же порядке?"

5. Верно ли следующее утверждение: "Выражение ключевого слова HAVING говорит GROUP BY о том, какие группы следует включить в вывод?"

  Упражнения

1. Запишите оператор SQL, возвращающий табельный номер служащего (EMP_ID), имя служащего (LAST_NAME) и название города (CITY) из таблицы EMPLOYEE_TBL, сгруппированные по значениям столбца CITY.

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