Решение. Если мы просто ищем агрегатную функцию для одной страны, то можем выбрать страну, где был выставлен счет, и среднее значение от общей суммы, используя условие WHERE, чтобы ограничить наши результаты для одной страны — США.
SELECT
BillingCountry,
AVG(Total)
FROM
invoices
WHERE
BillingCountry = 'USA'
Рис. 140
Напоминание
Чтобы сократить количество возвращаемых знаков после запятой, мы можем использовать функцию ROUND() вне функции AVG().
Вопрос 3. Каков общий объем продаж компании за все время?
Решение. Поскольку в данном запросе задается общая сумма счетов, условие SELECT выглядит довольно просто:
SELECT
SUM(Total)
FROM
invoices
Рис. 141
Вопрос 4. Кто входит в десятку лучших клиентов с точки зрения совершенных ими покупок?
Решение. Значение общей суммы найдено. Теперь необходимо отобразить первую десятку клиентов, приносящих наибольшую прибыль. Поскольку мы ищем данные из одной таблицы, которые соответствуют данным из другой таблицы во взаимно однозначном отношении, мы используем внутреннее соединение.
SELECT
SUM(Total)AS [Revenue Total],
c. FirstName,
c. LastName
FROM
invoices i
INNER JOIN
customers c
ON
i. CustomerId = c.CustomerId
GROUP BY c.CustomerId
ORDER BY SUM(Total) DESC
Вопрос 1. Сколько счетов превышает среднюю сумму счетов, выставленных в 2010 году?
Решение. Чтобы ответить на этот вопрос, необходимо решить две задачи. Во-первых, следует найти среднюю сумму счета-фактуры, сгенерированную в 2010 году. Во-вторых, необходимо сравнить это значение с каждым счетом в таблице, чтобы увидеть, сколько из них превышает среднюю стоимость счета-фактуры за 2010 год.
Сначала напишем следующий подзапрос:
select
avg(total)
from
invoices
where
InvoiceDate between '2010-01-01' and '2010-12-31'
В результате выполнения данного запроса мы получим среднее значение $5,80. Теперь необходимо написать внешний запрос для выбора счетов, превышающих средний показатель за 2010 год.
SELECT
InvoiceDate,
Total
FROM
invoices
WHERE
Total >
(SELECT
avg(total)
from
invoices
where
InvoiceDate between '2010-01-01' and '2010-12-31')
ORDER BY
Total DESC
Рис. 142
В результате выполнения данного запроса получено 179 строк.
Примечание
Если бы требовалось получить только фактическое количество возвращенных счетов-фактур, во внешнем запросе можно было бы изменить поле Total, указав COUNT(Total).
Вопрос 2. Какие клиенты получили эти счета?
Решение. Чтобы связать данные о клиентах из таблицы customers с таблицей invoices, необходимо использовать повторное объединение. Сам вопрос подразумевает однозначную связь между таблицей customers и таблицей invoices. Мы уже выбрали интересующие нас счета, поэтому теперь нам необходимо получить информацию о клиентах, которым были выставлены эти счета. При решении данного вопроса воспользуемся внутренним соединением. Это решение очень похоже на решение вопроса 1. Все, что мы добавили, — это раздел внутреннего соединения, поэтому у нас также имеется доступ к именам клиентов.
SELECT
i. InvoiceDate,
i. Total,
c. FirstName,
c. LastName
FROM
invoices i
INNER JOIN
customers c
ON
i. CustomerId = c.CustomerId
WHERE
Total >
(SELECT
avg(total)
from
invoices
where
InvoiceDate between '2010-01-01' and '2010-12-31')
ORDER BY
Total DESC
Вопрос 3. Сколько клиентов живут в США?
Решение. Мы можем изменить решение вопроса 2, включив оператор AND в конец условия WHERE внешнего запроса.
SELECT
InvoiceDate,
Total,
BillingCountry
FROM
invoices
WHERE
Total >
(SELECT
avg(total)
from
invoices
where
InvoiceDate between '2010-01-01' and '2010-12-31')
AND BillingCountry = 'USA'
ORDER BY
Total DESC
Рис. 143
В результате выполнения данного запроса получено 40 строк.
Примечание
При необходимости получения точного количества результатов можно использовать функцию SUM().
В данном разделе необходимо преобразовать запрос, в котором сравнивается средняя сумма счетов по городу со средним глобальным показателем, в представление.
SELECT
BillingCity,
AVG(Total) AS [City Average],
(select
avg(total)
from
invoices) AS [Global Average]
FROM
invoices
GROUP BY
BillingCity
ORDER BY
BillingCity
Вопрос 1. Из запроса SELECT возьмите внутренний запрос и создайте из него представление. Сохраните его с именем V_GlobalAverage.
Если вы выполняли примеры из главы, возможно, вы уже сохранили функцию, рассчитывающую среднее значение, в качестве представления. Убедитесь, что новому представлению присвоено новое имя.
Решение. Во внутреннем запросе в первую строку добавим синтаксис представления.
CREATE VIEW V_GlobalAverage AS
select
avg(total)
from
invoices AS [Global Average]
Вопрос 2. Удалите подзапрос из приведенного выше кода и замените его вновь созданным представлением V_GlobalAverage.
Решение. При использовании представления в условии SELECT мы используем символ *.
SELECT
BillingCity,
AVG(Total) AS [City Average],
(select
*
from
V_GlobalAverage) AS [Global Average]
FROM
