Агрегатные функции воздействуют на значения столбца, чтобы получить единое результирующее значение с помощью различных математических операций. В начале этой главы, чтобы вычислить количество клиентов с фамилией, начинающейся с буквы B, мы использовали функцию COUNT(). Существует множество практических способов использования агрегатных функций. Так, используем функцию SUM() в таблице invoices для вычисления итоговой суммы всех счетов:
SELECT
SUM(Total) AS [Total Sales]
FROM
invoices
Существует множество агрегатных функций [11], но здесь мы рассмотрим пять основных функций в SQL: SUM(), AVG(), MIN(), MAX() и COUNT().
Рис. 102
Пример
Напишем следующий запрос:
SELECT
SUM(Total) AS TotalSales,
AVG(Total) AS AverageSales,
MAX(Total) AS MaximumSale,
MIN(Total) AS MinSale,
COUNT(*) AS SalesCount
FROM
invoices
И получим следующий результат:
Рис. 103
Примечание
По умолчанию функция COUNT() возвращает только ненулевые значения. Однако если необходимо подсчитать все записи, даже записи с ошибками или нулевыми значениями, рекомендуется использовать символ звездочки * или поле первичного ключа. Символ звездочки * обозначает «вернуть все записи». Поэтому, используя ее с агрегатной функцией COUNT(), мы получим количество всех записей в таблице invoices.
Практические задания
• Сколько счетов содержится в таблице invoices?
• Какова средняя сумма счета?
• Какова сумма самого большого счета в таблице invoices?
Вложенные функции на примере ROUND()
Вложенная функция — это та, которая содержится в другой функции. Одна из целей использования вложенных функций — модифицировать формат внутренней функции. Если мы проанализируем предыдущий пример, в котором мы использовали функцию AVG(), то увидим, что Average Sales (средний объем продаж) содержит слишком много десятичных знаков. Такой формат обычно не используется для денежных единиц. Функция ROUND(), хотя и не агрегатная, очень полезна при выполнении каких-либо математических операций или если требуется привести в порядок результаты. Для этой цели функцию AVG() можно поместить в функцию ROUND() (это и есть вложение) и указать количество десятичных знаков, до которого мы хотим округлить результат.
Рис. 104
SELECT
AVG(Total) AS [Average Sales],
ROUND(AVG(Total), 2) AS [Rounded Average Sales]
FROM
invoices
Рис. 105
Внимание
При использовании функции ROUND() с денежными единицами будьте внимательны при округлении и изменении значений в промежуточных вычислениях. Обычно округление выполняется только на последнем шаге. Также вы можете добавить комментарии, чтобы указать, что результаты округляются до двух знаков после запятой.
Использование агрегатных функций и условия GROUP BY
Полезной особенностью агрегатных функций считается их способность вычислять промежуточные значения, или агрегаты, для различных групп данных. Для таблицы invoices в базе данных sTunes мы можем легко получить среднюю сумму счета с помощью функции AVG(). Предположим, компании sTunes необходимо рассчитать среднюю сумму счета для каждого города, где его выставили.
Внимание
Следующий запрос написан неправильно, чтобы показать, что происходит при сочетании в операторе SELECT агрегатных функций с неагрегатными полями. Этот запрос не вызывает ошибок, но он неправильно отображает запрашиваемую информацию.
SELECT
BillingCity,
AVG(Total)
FROM
invoices
ORDER BY
BillingCity
Запустите этот запрос и проанализируйте результаты.
Рис. 106
Нам требовалось получить среднюю сумму счета из таблицы invoices для каждого города. Несмотря на то что мы включили город в оператор SELECT, запрос по-прежнему дает нам только глобальное среднее значение всех счетов. Почему наш запрос не возвращает среднюю сумму для каждого города из таблицы invoices?
Чтобы решить эту задачу, давайте проанализируем запрос. Нам задали вопрос: какова средняя сумма счетов по городам?
НАПОМИНАНИЕ
Мы уже упоминали, что полезно разбивать запрос на компоненты, а также поразмыслить, какая таблица содержит нужную информацию и как ее отобразить. Ответ на эти два вопроса поможет вам устранить недочеты, связанные с запросом, который возвращает некорректную информацию.
В предыдущем (некорректном) запросе мы запросили у браузера SQL два вида информации из таблицы invoices. Сначала — перечислить все города в поле BillingCity. Затем — вычислить среднее значение поля Total. Результат выполнения первого запроса — многострочный ответ, а результат второго — однострочный ответ. Другими словами, мы указываем браузеру отображать одновременно как агрегатные, так и неагрегатные поля. Мы не получили необходимую информацию, так как неправильно сформулировали вопрос.
Исправить эту проблему можно, добавив в запрос условие GROUPBY следующим образом:
SELECT
BillingCity,
AVG(Total)
FROM
invoices
GROUP BY
BillingCity
ORDER BY
BillingCity
Рис. 107
Анализируя выполнение запроса (рис. 107), мы видим, что все города, где были выставлены счета, в нашем наборе результатов теперь появляются один раз и для каждого города отображается среднее значение счета.
Практическое задание
• В данный запрос добавьте функцию ROUND(), чтобы округлить средние значения до двух десятичных знаков.
Использование условий WHERE и HAVING со сгруппированными запросами
Добавление критериев в сгруппированный запрос работает так же, как и с другими, уже знакомыми нам запросами. Использование условия WHERE позволяет нам добавлять новые критерии. В примере ниже критерии добавляются для неагрегатного поля BillingCity.
SELECT
BillingCity,
AVG(Total)
FROM
invoices
WHERE
BillingCity LIKE 'L%'
GROUP BY
BillingCity
ORDER BY
BillingCity
Рис. 108
НАПОМИНАНИЕ
Неагрегатное поле — это просто поле в условии SELECT, которое вызывается без агрегатной функции.
Практическое задание
• Сколько городов, где были выставлены счета, начинаются с буквы L?
В последнем примере мы добавили критерии в неагрегатное поле. Может возникнуть необходимость использовать критерии для агрегированных полей, например AVG(Total). Скажем, когда нам надо найти все средние значения, меньшие 20. Мы могли бы попытаться ответить на этот вопрос с помощью условия WHERE, но существует одна проблема.
Внимание
Следующий оператор SQL содержит ошибку. Но важно видеть, что критерии, созданные в условии WHERE, не работают с агрегатными данными.
SELECT
BillingCity,
AVG(Total)
FROM
Invoices
WHERE
AVG(Total) > 5
GROUP BY
BillingCity
ORDER BY
BillingCity
При выполнении запроса возникнет следующее сообщение об ошибке:
Misuse of aggregate: AVG():
(Неправильное использование агрегата: AVG())
Это сообщение об ошибке информирует нас, что для создания условия на основе агрегатной функции (по крайней мере, в данном случае) мы не можем использовать условие WHERE. В данном случае условие WHERE может указывать только, какую информацию извлекать из полей, указанных в условии SELECT. Если необходима дополнительная фильтрация на основе агрегатных функций, необходимо включить вторичную фильтрацию, известную как условие HAVING.
Условие HAVING всегда следует после условия GROUPBY. Измененный запрос теперь выглядит следующим образом:
SELECT
BillingCity,
AVG(Total)
FROM
invoices
GROUP BY
BillingCity
HAVING
AVG(Total) > 5
ORDER BY
BillingCity
Рис. 109
Примечание
Условие HAVING позволяет фильтровать результат группировки, сделанной с помощью команды GROUP BY. Условие HAVING фильтрует агрегированные данные. Если вы попытаетесь использовать HAVING без условия GROUP BY, то получите сообщение об ошибке.
Условия WHERE и HAVING
Если кратко, то разница между условиями WHERE и HAVING заключается в том, что WHERE предназначено для фильтрации неагрегатных данных, а HAVING — для фильтрации результатов, содержащих агрегаты. Если более подробно, то два типа фильтрации возникают, когда в запрос включены как условие WHERE, так и условие HAVING. Условие WHERE указывает запросу, какую информацию следует исключить из таблицы, а затем, после фильтрации данных и применения к полям агрегатных функций, условие HAVING действует как дополнительный фильтр для агрегатных данных. Давайте повторим предыдущий запрос, но на этот раз выберем только города, начинающиеся с буквы B, а затем из этого списка отфильтруем счета, среднее значение которых больше пяти (рис. 110).
SELECT
BillingCity,
AVG(Total)
FROM
invoices
WHERE
BillingCity LIKE 'B%'
GROUP BY
BillingCity
HAVING
AVG(Total) > 5
ORDER BY
BillingCity
В запросе мы выполнили ту же задачу, но на этот раз добавили условие WHERE для фильтрации результатов только по городам, начинающимся с буквы B. Данный этап фильтрации выполняется до обработки условий HAVING и ORDERBY. Так как нам необходимо выполнить фильтрацию, прежде чем мы сможем группировать, то порядок условий фильтрации важен, а условие WHERE всегда предшествует HAVING.
Рис. 110
Группировка по нескольким столбцам
В условиях GROUPBY можно одновременно указывать столько столбцов, сколько вам требуется. Предположим, необходимо получить более подробную разбивку средних значений счетов. Мы можем написать запрос так, чтобы агрегированные данные были сначала сгруппированы по странам, а затем по городам. В приведенном ниже примере мы добавим в условие GROUPBY еще одно поле, BillingCountry. Давайте посмотрим, как работает запрос.