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

Запрос для отображения среднего объема продаж 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. Хороший пример того, когда в подзапросе необходимо использовать условие 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

InvoiceDate

from

invoices

where

InvoiceId = 251)

Рис. 118

Возврат нескольких значений из подзапроса

До этого момента мы использовали только подзапросы для вычисления единственного значения, которое затем передается внешнему запросу. Также можно использовать подзапросы, возвращающие несколько записей. Допустим, руководство компании sTunes хочет получить только три конкретных счета. Чтобы их выбрать, нужен следующий запрос:

SELECT

InvoiceDate

FROM

invoices

WHERE

InvoiceId IN (251, 252, 255)

Рис. 119

В предыдущем запросе для возврата трех дат из таблицы invoices используется условие IN: 2012-01-09, 2012-01-22 и 2012-01-24. Теперь предположим, что нам нужна информация о покупках за эти три дня. Если необходимо выбрать все счета за эти три дня, мы можем написать новый запрос или просто использовать предыдущий в качестве подзапроса, например:

SELECT

InvoiceDate,

BillingAddress,

BillingCity

FROM

invoices

WHERE

InvoiceDate IN

(SELECT

InvoiceDate

from

invoices

where

InvoiceId in (251, 252, 255))

Преобразование существующего запроса в подзапрос полезно, когда вы «играете» со своими данными. Чтобы еще больше сузить область поиска, этот метод позволяет повторно использовать существующий запрос и изменять его.

Рис. 120

Подзапросы и условие DISTINCT

Другие примеры в этой главе показывают, что подзапросы очень полезны для сценариев, где надо просмотреть или сравнить запрос по условию, для вычисления которого требуется собственный запрос. Как вы уже знаете из главы 1, в каждой таблице есть одно уникальное поле, первичный ключ, содержащее уникальный номер для каждой записи, но другие поля могут содержать избыточную информацию. Для удобства работы с избыточной информацией стоит отфильтровать данные, чтобы они отображали только уникальные значения. В этом случае полезно условие DISTINCT. Лучше понять подзапросы и использование условия DISTINCT нам помогут таблицы tracks и invoice_items.

Таблица invoice_items показывает, какие треки какому счету соответствуют. Если мы создадим запрос, отображающий поля InvoiceId и TrackId, упорядоченные по TrackId, мы увидим, что определенные номера треков были заказаны несколько раз в разных счетах.

SELECT

InvoiceId,

TrackId

FROM

invoice_items

ORDER BY

TrackId

Так, треки № 2 и № 8 появляются в нескольких счетах, что означает, что они были заказаны несколько раз (рис. 121). Однако для трека № 7 счет отсутствует, поэтому мы можем сделать вывод, что никто не приобретал его. Руководство sTunes хочет знать о треках, которые не продаются. Нам необходимо найти таблицу, связывающую поля TrackId с InvoiceId. Для перечисления всех треков (по композитору и названию), которые не отображаются в таблице invoice_items, мы можем использовать подзапросы.