Использование агрегатных функций

  • 3 мин

T-SQL предоставляет агрегатные функции, такие как SUM, MAX и AVG для выполнения вычислений, которые принимают несколько значений и возвращают один результат.

Использование агрегатных функций

Большинство рассматриваемых запросов работают с одной строкой за раз, используя предложение WHERE для фильтрации строк. Каждая возвращенная строка соответствует одной строке в исходном наборе данных.

Многие агрегатные функции предоставляются в SQL Server. В этом разделе мы рассмотрим наиболее распространенные функции, такие как SUM, MIN, MAX, AVG и COUNT.

При работе с агрегатными функциями необходимо учитывать следующие моменты:

  • Агрегатные функции возвращают одно (скалярное) значение и могут использоваться в инструкциях SELECT почти в любом расположении, где можно использовать одно значение. Например, эти функции можно использовать в предложениях SELECT, HAVING и ORDER BY. Однако их нельзя использовать в предложении WHERE.
  • Агрегатные функции пропускают значения NULL, за исключением случаев использования предложения COUNT(*).
  • Агрегатные функции в списке SELECT не содержат заголовок столбца, если только вы не указали псевдоним, используя автономную систему.
  • Агрегатные функции в списке выбора работают со всеми строками, переданными в операцию SELECT. Если предложение GROUP BY отсутствует, будут суммироваться все строки, соответствующие любому фильтру в предложении WHERE. Дополнительные сведения о предложении GROUP BY приведены в следующем разделе.
  • Если вы не используете предложение GROUP BY, не следует объединять агрегатные функции со столбцами, которые не входят в функции, в одном списке SELECT.

Чтобы расширить функциональность встроенных функций, SQL Server предоставляет механизм для пользовательских агрегатных функций с помощью среды выполнения (среда CLR) .NET. Эта тема выходит за рамки данного модуля.

Встроенные агрегатные функции

Как уже упоминалось, Transact-SQL предоставляет множество встроенных агрегатных функций. К наиболее распространенным функциям относятся:

Имя функции

Синтаксис

Description

SUM

SUM(выражение)

Суммирует все числовые значения в столбце, отличные от NULL.

СРЕДН.

AVG(выражение)

Определяет среднее значение всех числовых значений в столбце, отличных от NULL (сумма или число).

MIN

MIN(выражение)

Возвращает наименьшее число, самое раннее значение даты и времени или начальную строку (в соответствии с правилами сортировки параметров сортировки).

MAX

MAX(выражение)

Возвращает наибольшее число, самое последнее значение даты и времени или последнюю строку (в соответствии с правилами сортировки параметров сортировки).

COUNT или COUNT_BIG

COUNT(*) или COUNT(выражение)

Параметр (*) подсчитывает все строки, включая строки со значениями NULL. Если столбец указан как выражение, возвращает количество строк для этого столбца, отличных от NULL. Функция COUNT возвращает тип int, а COUNT_BIG — big_int.

Чтобы использовать встроенное статистическое выражение в предложении SELECT, рассмотрим следующий пример в образце базы данных MyStore:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Результаты этого запроса могут выглядеть примерно так:

AveragePrice

MinimumPrice

MaximumPrice

744,5952

2,2900

3578,2700

Обратите внимание, что в приведенном выше примере суммированы все строки из таблицы Production.Product. Вы можете легко изменить запрос, чтобы вернуть среднюю, минимальную и максимальную цену на продукты в определенной категории, добавив предложение WHERE следующим образом:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

При использовании агрегатов в предложении SELECT все столбцы, упоминаемые в списке SELECT, необходимо использовать в качестве входных данных агрегатной функции или ссылаться на них в предложении GROUP BY.

Рассмотрим следующий запрос, который пытается включить поле ProductCategoryID в агрегированные результаты:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

Выполнение этого запроса приведет к следующей ошибке:

Сообщение 8120, уровень 16, состояние 1, строка 1

Столбец Production.ProductCategoryID является недопустимым в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.

Запрос обрабатывает все строки как единую агрегированную группу. Поэтому все столбцы необходимо использовать в качестве входных данных для агрегатных функций.

В предыдущих примерах мы агрегировали числовые данные, такие как цена и количество в предыдущем примере. Некоторые агрегатные функции также можно использовать для суммирования даты, времени и символьных данных. В следующих примерах показано использование агрегатов с датами и символами:

Этот запрос возвращает первую и последнюю компанию по имени, используя MIN и MAX:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

Этот запрос возвращает первое и последнее значения для CompanyName в порядке сортировки базы данных (в нашем случае она отсортирована в алфавитном порядке):

MinCustomer

MaxCustomer

A Bike Store

Yellow Bicycle Company

Другие функции можно вложить в агрегатные функции.

Например, скалярная функция YEAR используется в следующем примере для возврата только части даты заказа, соответствующей году, перед вычислением MIN и MAX:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

Earliest

Latest

2008

2021

Функции MIN и MAX можно также использовать с данными даты, чтобы вернуть самые ранние и последние хронологические значения. Однако функции AVG и SUM можно использовать только для числовых данных, к которым относятся такие типы данных, как целые числа, денежные единицы, число с плавающей точкой или десятичное число.

Использование оператора DISTINCT с агрегатными функциями

Будьте внимательны при использовании оператора DISTINCT в предложении SELECT для удаления повторяющихся строк. При использовании с агрегатной функцией оператор DISTINCT удаляет повторяющиеся значения из входного столбца перед вычислением сводного значения. Оператор DISTINCT полезен при суммировании уникальных вхождений значений, таких как клиенты в таблице заказов.

Следующий пример возвращает количество клиентов, которые разместили заказы, независимо от количества их заказов:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<некоторый_столбец>) просто подсчитывает, сколько строк имеют некоторые значения в столбце. Если значения NULL отсутствуют, функция COUNT(<некоторый_столбец>) ничем не будет отличаться от функции COUNT(*). COUNT (DISTINCT <некоторый_столбец>) подсчитывает количество различных значений в столбце.

Использование агрегатных функций со значением NULL

Важно помнить о возможном наличии значений NULL в данных, а также о том, как эти значения взаимодействуют с компонентами запроса T-SQL, в том числе с агрегатной функцией. Существует ряд аспектов, которые нужно помнить:

  • За исключением оператора COUNT, используемого с параметром (*), агрегатные функции T-SQL игнорируют значения NULL. Например, функция SUM будет добавлять только значения, отличные от NULL. Значения NULL не равны нулю. Оператор COUNT(*) учитывает все строки, независимо от значения или типов, не являющихся значениями, в каком-либо столбце.
  • Наличие значений NULL в столбце может привести к неточным вычислениям AVG. Будут суммироваться только заполненные строки, и эта сумма будет поделена на число строк, отличных от NULL. Результаты AVG(<столбец>) и (SUM(<столбец>)/COUNT(*)) могут отличаться.

Например, рассмотрим следующую таблицу с именем t1:

C1

C2

1

NULL

2

10

3

20

4

30

5

40

6

50

Этот запрос показывает различие между тем, как оператор AVG обрабатывает значение NULL и способом вычисления среднего значения с помощью вычисляемого столбца SUM/COUNT(*):

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

Результат должен быть таким:

sum_nonnulls

count_all_rows

count_nonnulls

average

arith_average

150

6

5

30

25

В этом результирующем наборе столбец с именем average является агрегатом, который внутренним образом получает сумму 150 и делит на число значений, отличных от NULL, в столбце c2. Расчет буде выглядеть так: 150/5 или 30. Столбец с именем arith_average явным образом делит сумму на количество всех строк, поэтому расчет буде выглядеть так: 150/6 или 25.

Если необходимо свести все строки со значением NULL или без, попробуйте заменить значения NULL на другие значения, которые не будет игнорировать агрегатная функция. Для этого можно использовать функцию COALESCE.