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

• Отдел кадров компании sTunes сообщил нам, что возраст сотрудников — это тема весьма деликатная. Перепишите этот запрос, чтобы в результате получить количество лет работы каждого сотрудника в компании.

• Какой сотрудник проработал в компании дольше всех?

Агрегатные функции

Агрегатные функции воздействуют на значения столбца, чтобы получить единое результирующее значение с помощью различных математических операций. В начале этой главы, чтобы вычислить количество клиентов с фамилией, начинающейся с буквы 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), мы видим, что все города, где были выставлены счета, в нашем наборе результатов теперь появляются один раз и для каждого города отображается среднее значение счета.