Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Что такое фу11к14ии агрегирова11ия?

Рис. 6.5. Подсчет максимального количества (amounts) для каждого продавца (salesperson)

GROUP ВУ применяет агрегатные функции отдельно к каждой серии групп, кото­ рые определяются общим значением поля. В данном случае каждая группа состоит из всех тех строк, которые имеют одно и то же значение snum, а функция МАХ применя­ ется отдельно к каждой такой группе. Это означает, что поле, к которому применяется GROUP ВУ по определению имеет на выходе только одно значение на каждую группу, что соответствует применению агрегатных функций. Такая совместимость результа­ тов и позволяет комбинировать агрегаты с полями указанным способом.

Можно также применять GROUP ВУ с многозначными полями. Обращаясь к пре­ дыдущему примеру, можно предположить, что необходимо увидеть наибольший заказ,

сделанный каждому продавцу на каждую дату. Для этого нужно сгруппировать данные

таблицы Orders по дате (date) внутри одного и того же по,1я salesperson и применить функцию МАХ к каждой группе. В результате будет получено:

SELECT snum, odate, MAX(amt)

FROM Orders

GROUP ВУ snum, odate;

Выходные данные для этого запроса представлены на рис. 6.6.

Пустые группы, т.е. даты, когда данный продавец не получал заказов, в результате

не представлены.

57

Глава 6. Суммирование данных с nомощью фу11кций агрегирования

WiiifШM +.1:1,

1001 10/03 1990 767.19 1'!101 10/05/1990 4723.01!1 1001 10/06/1990 9891.88 1002 11!1/03/1990 5160.45 1002 10/04/1990 75.75 1002 10/06/1990 1309.95 HlliJ3 10/'14/1991iJ 1713.23 1004 10/03/1990 1900.10 11!107 11!1/03/1990 1098. 16

Рис. 6.6. Поиск максимальных заявок (orders) для каждого продавца (salesperson) на каждый

день

Предложение НА VING

Обращаясь к предыдущему примеру, можно предположить, что интересны только покупки, превышающие $3000.00. Однако использовать агрегатные функции в пред­ ложении WHERE нельзя (если только не применяется подзапрос, который будет объ­ яснен позднее), поскольку предикаты оцениваются в терминах единственной строки, тогда как агрегатные функции оцениваются в терминах групп строк. Это значит, что нельзя формулировать запрос следующим образом:

SELECT snum, odate, MAX(amt)

FROM Orders

WHERE MAX(amt) > 3000.00

GROUP ВУ snum, odate;

Это неприемлемо с точки зрения точной интерпретации ANSI. Чтобы увидеть мак­ симальную покупку, превышающую $3000.00, следует использовать предложение HAVING. Оно определяет критерий, согласно которому определенные группы исклю­

чаются из числа выходных данных, так же, как предложение WHERE делает это для

отдельных строк. Правильная команда выглядит так:

SELECT snum, odate, MAX(amt)

FROM Orders

GROUP ВУ snum, odate

HAVING MAX(amt) > 3000.00;

Выходные данные для этого запроса представлены на рис. 6.7.

58

Что такое функции агрегирования?

Рис. 6. 7. Поглощение групп агрегатными значениями

Аргументы HAVING подчиняются тем же правилам, что и аргументы SELECT в

команде, использующей GROUP ВУ, и должны иметь единственное значение для каж­

дой выходной группы. Следующая команда некорректна:

SELECT snum, MAX(amt)

FROM Orders

GROUP 8У snum

HAVING odate = 10/03/1988;

В предложении HAVING нельзя указывать поле odate, посколь~--у оно может иметь (и действительно имеет) более одного значения для каждой выходной группы. HAVING должно относиться только к агрегатам и полям, выбранным по GROUP ВУ. Вот корректный способ формулировки приведенного запроса (выходные данные пред­ ставлены на рис. 6.8):

SELECT snum, MAX(amt)

FROM Orders

WHERE odate = 10/03/1990

GROUP 8У snum;

Поскольку odate не является и не может быть выбранным полем, значимость полу­

ченных здесь данных, конечно, менее очевидна, чем в некоторых других примерах.

Выходные данные должны были бы содержать нечто вроде следующего предложения: "Вот наибольшие заявки на 3 октября". В главе 7 будет объяснено, как вставить текст в

выходные данные.

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

59

Глава 6. Суммирование данных с помощью функций агрегироватtя

Рис. 6.8. Максимум для каждого продавца за 3 октября 1990 г.

функции, но можно осуществлять выбор полей и с помощью GROUP ВУ. Например, можно взглянуть на самые большие заказы для Serres и Rifkin:

SELECT snum, MAX(amt)

FROM Orders

GROUP ВУ snum

HAVING snum IN (1002, 1007)

Выходные данные для этого запроса представлены на рис. 6.9.

Не используйте вложенные агрегаты

Вверсии языка SQL, определяемой ANSI, нельзя применять агрегатную функцию

сагрегатом в качестве аргумента. Предположим, нужно определить в какой день было

сделано наибольшее число заявок. Ес.1и ввести команду:

SELECT odate, МАХ ( SUM (amt) )

FROM Orders

GROUP ВУ odate;

то она, вероятно, будет отвергнута. (Существуют реализации, которые не учитывают

такие ограничения, что дает определенные преимущества, поскольку вложенность аг­

регатов может быть полезной, даже если она и вызывает сомнения.) Например, в дан­ ной команде SUM должна быть применена к каждой оdаtе-группе, а МАХ - ко всем

группам, причем она выдает единственное значение для всех этих групп. В то же вре­

мя предложение GROUP ВУ предполагает, что должна быть одна строка выходных данных на каждую группу odate.

60

Итоги

Рис. 6.9. Использование НАVING с GROUP ВУ

Итоги

Теперь вы научились использовать запросы иначе. Возможность выводить, а не

просто локализовать значения очень важна и означает, что не надо отслеживать путь

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

мации.

Агрегатные функции применимы к группам значений, определяемым предложени­ ем GROUP ВУ. Эти группы имеют общее значение поля и могут использоваться внут­ ри других групп, имеющих общее значение поля. Предикаты нужны для определения строк, к которым применяется функция агрегирования. Такое комбинирование позво­

ляет получить агрегаты на основе полностью определенных подмножеств значений

поля. Затем вы можете задать условие исключения определенных результирующих групп с помощью предложения HAVING.

Вы уже знаете, как запросы генерируют значения. В главе 7 будет показано, эти

значения можно применять.

61