COUNT(ORD_NUM) SUM(QTY) AVG_QTY
------------- ------- -------
7 160 22.85743
Здесь подсчитано число заказов, указана общая сумма стоимости всех заказов и с помощью деления второй величины на первую вычислена средняя стоимость заказа. Для представления последней создан псевдоним столбца - AVG_QTY.
Итоговые функции несложно использовать и они могут оказаться весьма полезными. Вы теперь знаете, как подсчитать число значений в столбце, число строк в таблице, как найти максимальное или минимальное из всех значений в столбце. Помните о том, что при использовании итоговых функций значение NULL не учитывается - исключением является функция COUNT в формате COUNT (*).
Итоговые функции являются первыми из рассмотренных нами функций SQL, но они не единственные и существует множество других. Итоговые функции используются также для группирования значений, что предполагается рассмотреть в ходе следующего урока. По мере изучения других функций, вы обнаружите, что в основном они имеют похожий синтаксис и что лежащие в их основе концепции достаточно просты.
Почему при использовании функций MIN и МАХ значение NULL игнорируется?
Значение NULL означает, что в поле ничего нет.
Почему при использовании функции COUNT тип данных не играет значения?
Функция COUNT просто считает строки.
Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".
1. Верно ли следующее утверждение: "Функция AVG возвращает среднее для значений всех строк, включая значения NULL?"
2. Верно ли следующее утверждение: "Функция зим используется для суммирования итоговых значений по столбцам?"
3. Верно ли следующее утверждение: ''Функция COUNT (* ) подсчитывает число всех строк в таблице?"
4. Будут ли работать следующие операторы SELECT? Если нет, то что в них следует исправить?
а. SELECT COUNT *
FROM EMPLOYEE_PAY_TBL;
6. SELECT COUNT(EMPLOYEE_ID), SALARY
FROM EMPLOYEE_PAYJFBL;
B. SELECT MIN(BONUS), MAX(SALARY)
FROM EMPLOYEE_PAY_TBL
WHERE SALARY > 20000;
1. Рассмотрите следующую таблицу PRODUCTS_TBL.
EMP_ID POSITION DATE_HIRE PAV_RATE DATE_LAST SALARY BONUS
311549902 МАРКЕТИНГ 23-МАЙ-89 01-МАЙ-99 30000 2000
442346889 РУК.ГРУППЫ 17-ИЮН-90 14.75 01-ИЮН-99
213764555 МЕНЕДЖЕР 14-АВГ-94 01-АВГ-99 40000 3000
313782439 ПРОДАВЕЦ 28-ИЮН-97 20000 1000
220984332 ДОСТАВКА 22-ИЮЛ-96 11 01-ИЮЛ-99
443679012 ДОСТАВКА 14-ЯНВ-91 15 01-ЯНВ-99
6 строк выбраны.
Постройте операторы SQL, позволяющие найти следующее.
а. Среднюю зарплату (SALARY).
б. Максимальную премию (BONUS).
в. Сумму всех выплат по зарплате.
г. Минимальную почасовую оплату (PAY_RATE).
д. Общее число строк в таблице.
10-й час Сортировка и группирование данных
Вы уже знаете, как построить запрос к базе данных и получить от нее определенным образом организованные данные. Вы уже знаете, как отсортировать данные запроса. В ходе этого урока мы с вами узнаем как можно разделять полученные данные на группы так, чтобы их легко было воспринимать.
Основными на этом уроке будут следующие темы.
• Зачем группировать данные?
• Выражение GROUP BY
• Функции группирования значений
• Использование итоговых функций
• Группирование по столбцам
• GROUP BY в сравнении с ORDER BY
• Выражение HAVING
Группирование данных - это размещение данных в столбцах с повторяющимися значениями в определенном логичном порядке. Например, в базе данных содержится информация о служащих. Служащие могут жить в разных городах, но многие из них живут в одном городе. Вполне вероятно, что вам может понадобиться информация по каждому конкретному городу и живущих там служащих. Для этого вы группируете информацию о служащих по городам - и соответствующий отчет готов!
Предположим, что вам необходимо найти среднюю зарплату служащих по каждому из городов. Это можно сделать, применив к столбцу SALARY итоговую функцию AVG, а затем использовав GROUP BY для группирования выводимых данных по городам.
Группирование данных осуществляется с помощью выражения GROUP BY в операторе SELECT. На предыдущем уроке были рассмотрены итоговые функции. В ходе данного урока мы с вами научимся использовать итоговые функции в совокупности с выражением 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.