W Cat - SQL за 24 часа
• В отличие от выражения ключевого слова 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, чтобы в выводе отобразить только те города, в которых проживает более двух служащих из числа тех, информация о которых имеется в таблице.
11-й час Изменение представления данных при выводе
Из этого урока вы узнаете, как можно менять представление получаемых в запросе данных с помощью целого ряда функций. Некоторые из этих функций определены стандартом ANSI, другие же основаны на стандартных или являются вариациями функций, чаще всего используемых в реализациях SQL.
