www.ПЕРВЫЕ ШАГИ.ru :: Шаг 7 - Обобщение данных с помощью агрегатных функций

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

SQL Server предоставляет несколько агрегатных функций:

COUNT - производит подсчет строк, удовлетворяющих условию запроса
SUM - вычисляет арифметическую сумму всех значений колонки
AVG - вычисляет среднее арифметическое всех значений
MAX - определяет наибольшее из всех выбранных значений
MIN - определяет наименьшее из всех выбранных значений

Функции SUM и AVG применимы только к числовым полям. С COUNT, MAX, MIN могут использоваться числовые или символьные поля. При использовании с символьными полями MAX, MIN сравнивают значения в алфавитном порядке. Агрегатные функции при своей работе игнорируют значения NULL.

Чтобы найти сумму всех заказов в таблице Orders можно ввести запрос:

SELECT SUM( amt )
FROM Orders

Результат:

---------------------
26658.4000

Функция COUNT несколько отличается от остальных. Она подсчитывает число значений в данной колонке или число строк в таблице. Например подсчитаем количество номеров торговых агентов, вписанных в таблицу Orders

SELECT COUNT( DISTINCT snum )
FROM Orders

Результат:

----------- 
5

Обратите внимание, что в приведенном выше примере используется ключевое слово DISTINCT - это означает что подсчитываться будет количество уникальных значений в колонке snum таблицы Orders. Если опустить его результат изменится:

-----------
10

Чтобы подсчитать общее число строк в таблице используйте функцию COUNT со звездочкой вместо имени поля:

SELECT COUNT(*)
FROM Customers

Результат:

-----------
7

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

SELECT snum, MAX( amt )
FROM Orders
GROUP BY snum

Вывод этого запроса:

snum             
----- ---------- 
1001  9891.8800
1002  5160.4500
1003  1713.2300
1004  1900.1000
1007  1098.1600

GROUP BY применяет агрегатные функции независимо к группам записей. Условие формирования группы - одинаковое значение поля (в данном случае snum). При обработке этого запроса функция MAX вычисляется для каждого из значений snum.

GROUP BY можно использовать с несколькими полями. Усложним предыдущий запрос:

SELECT snum, odate, MAX( amt )
FROM Orders
GROUP BY snum, odate

Т.е. мы выбираем коды агентов и максимальные суммы полученных ими заказов на каждую дату:

snum  odate
----- ------------------------ ----------
1001  1999-10-03 00:00:00.000  767.1900
1001  1999-10-05 00:00:00.000  4723.0000
1001  1999-10-06 00:00:00.000  9891.8800
1002  1999-10-03 00:00:00.000  5160.4500
1002  1999-10-04 00:00:00.000  75.7500
1002  1999-10-06 00:00:00.000  1309.9500
1003  1999-10-04 00:00:00.000  1713.2300
1004  1999-10-03 00:00:00.000  1900.1000
1007  1999-10-03 00:00:00.000  1098.1600

Разумеется дни, в которые не было заказов не будут показаны.

Усложним задачу: теперь надо получить максимальную сумму заказов каждого агента, которая больше 3000. Для достижения такого эффекта применяется предложение HAVING, которое определяет критерий, используемый для удаления групп из результата запроса, как это делает предложение WHERE для отдельных строк. Вот как это делается:

SELECT snum, odate, MAX( amt )
FROM ORDERS
GROUP BY snum, odate
HAVING MAX( amt ) > 3000

snum  odate
----- ------------------------ ----------
1002  1999-10-03 00:00:00.000  5160.4500
1001  1999-10-05 00:00:00.000  4723.0000
1001  1999-10-06 00:00:00.000  9891.8800

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