SQL: быстрое погружение - [31]

Контрольные вопросы

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

Если в операторе SELECT требуется добавить дополнительное действие (например, агрегатное вычисление), то для его выполнения нам понадобится подзапрос. В предыдущей главе, посвященной функциям, показано, что для отображения средних значений в счетах для разных городов мы использовали условие GROUPBY. Что будет, если для компании sTunes нам надо узнать показатели продаж в каждом отдельном городе и сравнить их со средними мировыми продажами? Один из способов ответить на это — написать запрос, который будет отображать средний объем продаж в каждом городе рядом со среднемировым показателем.