Шаг 7 - Обобщение данных с помощью агрегатных функций
На этом шаге мы перейдем к использованию агрегатных функций, которые берут группы значений и сводят их к одиночному значению.
SQL Server предоставляет несколько агрегатных функций:
Примечание
Функции SUM и AVG применимы только к числовым полям. С COUNT, MAX, MIN
могут использоваться числовые или символьные поля. При использовании с символьными
полями MAX, MIN сравнивают значения в алфавитном порядке. Агрегатные
функции при своей работе игнорируют значения NULL.
Функция SUM
Чтобы найти сумму всех оплаченных заказов в таблице Payments можно ввести запрос:
SELECT SUM( amount )
FROM payments;
Результат:
SUM( amount ) |
8853839.23 |
Функция COUNT
Функция COUNT несколько отличается от остальных. Она подсчитывает число значений в данной колонке или число строк в таблице. Например подсчитаем количество номеров торговых агентов(salesRepEmployeeNumber), вписанных в таблицу Customers, за каждым клиентом стоит торговый агент, который ведет работу с ним.
SELECT COUNT( DISTINCT salesRepEmployeeNumber) FROM customers ;
Результат:
count( DISTINCT salesRepEmployeeNumber) |
15 |
Обратите внимание, что в приведенном выше примере используется ключевое слово
DISTINCT - это означает что подсчитываться будет количество уникальных значений
в колонке salesRepEmployeeNumber таблицы Customers.
Если опустить его результат изменится:
count( salesRepEmployeeNumber) |
100 |
Чтобы подсчитать общее число строк в таблице используйте функцию COUNT
со звездочкой вместо имени поля:
SELECT COUNT(*) FROM customers;
COUNT(*) |
122 |
Использование GROUP BY
Предложение GROUP BY позволяет задавать Вам подмножество значений, для
которых применяется агрегатная функция. Это дает возможность объединять поля
и агрегатные функции в одном предложении SELECT. Предположим, что Вам надо
найти наибольшую сумму заказа, полученного каждым торговым агентом c клиента.
Внашей базе данных за каждым клиентом стоит один торговый агент, следовательно
можно посмотреть таблицу платежей Payments
SELECT customerNumber,MAX(amount) FROM payments
GROUP BY customerNumber LIMIT 10;
Вывод этого запроса:
+----------------+-------------+
| customerNumber | MAX(amount) |
+----------------+-------------+
| 103 | 14571.44 |
| 112 | 33347.88 |
| 114 | 82261.22 |
| 119 | 49523.67 |
| 121 | 50218.95 |
| 124 | 111654.4 |
| 128 | 33820.62 |
| 129 | 26248.78 |
| 131 | 50025.35 |
| 141 | 120166.58 |
+----------------+-------------+
GROUP BY применяет агрегатные функции независимо к группам записей.
Условие формирования группы - одинаковое значение поля (в данном случае snum).
При обработке этого запроса функция MAX вычисляется для каждого из значений customerNumber.
GROUP BY можно использовать с несколькими полями. Усложним предыдущий запрос:
SELECT customerNumber,paymentDate,MAX(amount)
FROM payments GROUP BY customerNumber,paymentDate LIMIT 15;
Т.е. мы выбираем коды клиентов и максимальные суммы оплаченных ими заказов на каждую дату:
+----------------+-------------+-------------+
| customerNumber | paymentDate | MAX(amount) |
+----------------+-------------+-------------+
| 103 | 2003-06-05 | 14571.44 |
| 103 | 2004-10-19 | 6066.78 |
| 103 | 2004-12-18 | 1676.14 |
| 112 | 2003-06-06 | 32641.98 |
| 112 | 2004-08-20 | 33347.88 |
| 112 | 2004-12-17 | 14191.12 |
| 114 | 2003-05-20 | 45864.03 |
| 114 | 2003-05-31 | 7565.08 |
| 114 | 2004-03-10 | 44894.74 |
| 114 | 2004-12-15 | 82261.22 |
| 119 | 2004-08-08 | 47924.19 |
| 119 | 2004-11-14 | 19501.82 |
| 119 | 2005-02-22 | 49523.67 |
| 121 | 2003-02-16 | 50218.95 |
| 121 | 2003-10-28 | 1491.38 |
+----------------+-------------+-------------+
Разумеется дни, в которые не было заказов не будут показаны.
Использование HAVING
Усложним задачу: теперь надо получить максимальную сумму заказов каждого клиента,
которая больше 60000. Для достижения такого эффекта применяется предложение
HAVING, которое определяет критерий, используемый для удаления групп из результата запроса,
как это делает предложение WHERE для отдельных строк. Вот как это делается:
SELECT customerNumber,paymentDate,MAX(amount)
FROM payments group by customerNumber,paymentDate
HAVING max(amount >60000);
+----------------+-------------+-------------+
| customerNumber | paymentDate | MAX(amount) |
+----------------+-------------+-------------+
| 114 | 2004-12-15 | 82261.22 |
| 124 | 2003-08-15 | 111654.4 |
| 124 | 2004-08-28 | 85410.87 |
| 124 | 2005-03-05 | 101244.59 |
| 124 | 2005-04-16 | 83598.04 |
| 141 | 2003-12-09 | 63843.55 |
| 141 | 2004-12-31 | 116208.4 |
| 141 | 2005-03-18 | 120166.58 |
| 141 | 2005-03-25 | 65071.26 |
| 148 | 2003-12-26 | 105743 |
| 157 | 2004-09-07 | 63357.13 |
| 167 | 2003-12-03 | 85024.46 |
| 239 | 2004-03-15 | 80375.24 |
| 259 | 2004-11-06 | 61234.67 |
| 298 | 2004-09-18 | 61402 |
| 321 | 2003-11-03 | 85559.12 |
| 323 | 2005-05-23 | 75020.13 |
+----------------+-------------+-------------+
Агрегатные функции позволяют не просто выбирать определенные значения из БД, но и производить их обобщение и анализ. Далее мы познакомимся с техникой выборки данных из нескольких таблиц.