SELECT
BillingCountry,
BillingCity,
AVG(Total)
FROM
invoices
GROUP BY
BillingCountry, BillingCity
ORDER BY
BillingCountry
На рис. 111 мы видим, что у нас имеется несколько записей для одной страны выставления счета, а отдельные города указаны в соседнем столбце. Группировка по нескольким столбцам может быть очень полезна, когда необходимо получить более детальную информацию.
НАПОМИНАНИЕ
Файлы базы данных могут содержать орфографические и/или ошибки, связанные с регистром.
Рис. 111
Несколько заключительных слов о функциях
Цель этой главы — познакомить вас с функциями. Мы описали возможности некоторых функций, их способность превращать данные в информацию и решать практические задачи. Если бы пришлось рассматривать все функции в SQLite и иллюстрировать работу каждой из них, то эту книгу мне никогда не удалось бы закончить. К счастью, в интернете можно найти массу информации о функциях SQL и их использовании. Я всегда рекомендую студентам поискать информацию о функциях в интернете, чтобы увидеть разные примеры их использования. Надеюсь, что вы продолжите обучение самостоятельно и узнаете еще больше о том, как использовать эти мощные инструменты.
Контрольные вопросы
1. Создайте однострочный список рассылки для всех клиентов из США, включая полные имена, написанные заглавными буквами, и полные адреса с пятизначными почтовыми индексами, в следующем формате:
FRANK HARRIS 1600 Amphitheatre Parkway, Mountain View, CA 94043
2. Каковы средние годовые продажи клиентам из США согласно имеющимся данным за все годы?
3. Каков общий объем продаж компании за все время?
4. Кто входит в десятку лучших клиентов с точки зрения совершенных ими покупок? Подсказка: чтобы ответить на этот вопрос, необходимо использовать соединение (глава 6).
Резюме
• Функции позволяют изменять, форматировать и выполнять вычисления с данными в таблицах.
• Запросы, содержащие числовые данные, можно обрабатывать с помощью различных арифметических операций и агрегатных функций.
• Запросы, содержащие текст, можно разделить, объединить, использовать для них заглавные буквы и т. д.
• После агрегирования данные можно дополнительно отсортировать с помощью условий GROUPBY и HAVING.
• Условие HAVING работает с агрегатными полями так же, как условие WHERE с неагрегатными.
• Условие HAVING можно использовать в запросе, только если имеется условие GROUPBY.
• Условие GROUPBY можно использовать с несколькими полями, чтобы еще больше детализировать агрегатные данные.
Часть III. Расширенные возможности языка SQLГлава 8. Подзапросы
Подзапрос — это просто один запрос, вложенный в другой. Подзапрос обычно добавляется в условия SELECT, FROM или WHERE. Использование подзапросов полезно, когда запрос, который мы хотим создать, требует нескольких дополнительных шагов или вычислений для создания ожидаемого набора данных. Например, подзапросы очень полезны в сценариях, когда мы хотим просмотреть или сравнить запрос по условию, для вычисления которого требуется собственный запрос. Чтобы не писать один запрос, а затем копировать результаты в следующий, мы можем использовать подзапрос, выполняющий обе операции одновременно. Подзапросы также предоставляют нам другой метод одновременного доступа к данным из нескольких таблиц. Хотя использование подзапроса не так эффективно, как соединение (join), он позволяет нам выполнить вычисление в одной таблице, а затем использовать это вычисление в другой таблице. Начнем с использования подзапросов с агрегатными функциями.
Использование агрегатных функций в подзапросах
Рассмотрим простой оператор SELECT, который мы использовали в предыдущей главе для возврата среднего значения суммы счета из таблицы invoices:
SELECT
ROUND(AVG(Total), 2) AS [Average Total]
FROM
invoices
Рис. 112
Напоминание
Чтобы округлить значение функции AVG() до двух значащих цифр, добавим функцию ROUND().
Мы видим, что в примере запроса средняя сумма счета из таблицы invoices составляет $5,65. Предположим, компания sTunes попросила нас собрать данные обо всех счетах, сумма которых меньше этого среднего значения. Прежде всего, необходимо использовать оператор SELECT, который выводит отдельные поля счета (например, InvoiceDate, BillingAddress, BillingCity и, конечно же, Total). Затем отфильтруем полученные результаты, сравнив их с агрегатной функцией. Используем условие WHERE, чтобы сравнить поле Total с полем AVG(Total). В предыдущей главе вы узнали, что попытка использования условия WHERETotal Начнем с базового оператора SELECT, а затем, используя круглые скобки (), вставим весь приведенный выше запрос в условие WHERE, заставляя его функционировать как подзапрос. SELECT InvoiceDate, BillingAddress, BillingCity, Total FROM invoices WHERE Total < (SELECT AVG(Total) from invoices) ORDER BY Total DESC Запрос, заключенный в круглые скобки, называется внутренним запросом, он и станет частью условия WHERE нашего внешнего запроса. Рис. 113 На рис. 113 показано, что оператор SELECT и другие операторы во внешнем запросе написаны прописными, а все операторы и функции во внутреннем запросе — строчными буквами. Не существует универсального правила, каким образом следует писать операторы или функции. Я лично считаю, что указанный способ написания операторов и функций внешних запросов и подзапросов упрощает восприятие, поскольку помогает визуально различать внешние и внутренние операторы запроса. Если в операторе SELECT требуется добавить дополнительное действие (например, агрегатное вычисление), то для его выполнения нам понадобится подзапрос. В предыдущей главе, посвященной функциям, показано, что для отображения средних значений в счетах для разных городов мы использовали условие GROUPBY. Что будет, если для компании sTunes нам надо узнать показатели продаж в каждом отдельном городе и сравнить их со средними мировыми продажами? Один из способов ответить на это — написать запрос, который будет отображать средний объем продаж в каждом городе рядом со среднемировым показателем. Запрос для отображения среднего объема продаж BillingCity идентичен запросу, который мы использовали в предыдущей главе, за одним исключением. Для расчета глобального среднего показателя мы включаем подзапрос в условие SELECT. Таким образом, мы можем сравнить два значения. SELECT BillingCity, AVG(Total) AS [City Average], (SELECT avg(total) from invoices) AS [Global Average] FROM invoices GROUP BY BillingCity ORDER BY BillingCity Результат этого запроса показывает, как продажи в каждом городе соотносятся со среднемировым уровнем. Рис. 114 Из рис. 114 видно, что значение для Global Average в каждой возвращаемой записи остается неизменным, что позволяет нам легко сравнивать средние итоговые суммы счетов по городам с мировым средним значением. • Измените запрос, используя функцию ROUND(), чтобы отображалось только два десятичных знака. Иногда надо получить более подробный запрос в качестве подзапроса. Внешний запрос может содержать условие WHERE, которое, в свою очередь, содержит подзапрос с собственным условием WHERE. Хороший пример того, когда в подзапросе необходимо использовать условие WHERE, если требуется сравнить все поля с отдельным значением. Предположим, нас попросили найти самые большие продажи за весь период сбора данных (2009–2012 гг.) и проверить, имеются ли какие-либо итоговые суммы счетов за последний отчетный год (2013 г.), превышающие это значение. Чтобы ответить, сначала необходимо узнать самые большие продажи до 2013 года. Для этого воспользуемся функцией MAX(). SELECT MAX(Total) FROM invoices WHERE InvoiceDate < '2013-01-01' Рис. 115 Теперь, когда нам известно это значение, мы заключим запрос в круглые скобки (), а затем добавим внешний запрос и вставим необходимые дополнительные поля. SELECT InvoiceDate, BillingCity, Total FROM invoices WHERE InvoiceDate >= '2013-01-01' AND total > (select max(Total) from invoices where InvoiceDate < '2013-01-01') Из запроса видно, что максимальный счет был выставлен 13 ноября 2013 года. Рис. 116 • Сколько счетов, значения которых превышали среднюю сумму счета, было зарегистрировано 1 января 2010 года или ранее? Подзапрос не всегда содержит агрегатную функцию. Следующий запрос отображает дату конкретной транзакции. SELECT InvoiceDate FROM invoices WHERE InvoiceId = 251 Рис. 117 Если необходимо узнать, получены ли какие-либо другие счета после указанного выше счета, мы добавим подзапрос, заключенный в круглые скобки, а затем добавим внешний запрос. SELECT InvoiceDate, BillingAddress, BillingCity FROM invoices WHERE InvoiceDate > (selectИспользование подзапроса в операторе SELECT
Практическое задание
Использование подзапроса с условием WHERE
Практическое задание
Подзапросы без агрегатных функций