Понимание SQL

Таблица 6.5: Нахождение максимальной суммы продажи у каждого продавца

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

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

SELECT snum, odate, MAX ((amt))

FROM Orders

GROUP BY snum, odate;

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

SQL Execution Log

SELECT snum, odate, MAX (amt)

FROM Orders GROUP BY snum, odate;

snum	odate
1001	10/03/1990	767.19
1001	10/05/1990	4723.00
1001	10/06/1990	9891.88
1002	10/03/1990	5160.45
1002	10/04/1990	75.75
1002	10/06/1990	1309.95
1003	10/04/1990	1713.23
1014	10/03/1990	1900.10
1007	10/03/1990	1098.16

Таблица 6.6: Нахождение наибольшей суммы приобретений на каждый день

Конечно же, пустые группы, в дни когда текущий продавец не имел порядков, не будут показаны в выводе.

ПРЕДЛОЖЕНИЕ HAVING

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

SELECT snum, odate, MAX (amt)

FROM Oreders

WHERE MAX ((amt)) > 3000.00

GROUP BY snum, odate;

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

Предложение HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк.

Правильной командой будет следующяя:

SELECT snum, odate, MAX ((amt))

FROM Orders

GROUP BY snum, odate

HAVING MAX ((amt)) > 3000.00;

Вывод для этого запроса показывается в Таблице 6. 7.

SQL Execution Log

SELECT snum, odate, MAX (amt) FROM Orders

GROUP BY snum, odate HAVING MAX (amt) > 3000.00;

snum	odate
1001	10/05/1990	4723.00
1001	10/06/1990	9891.88
1002	10/03/1990	5160.45

Таблица 6. 7: Удаление групп агрегатных значений

Аргументы в предложении HAVING следуют тем же самым правилам что и в предложении SELECT, состоящей из команд использующих GROUP BY. Они должны иметь одно значение на группу вывода. Следующая команда будет запрещена:

SELECT snum, MAX (amt)

FROM Orders

GROUP BY snum

HAVING odate=10/03/1988;

Поле оdate не может быть вызвано предложением HAVING, потому что оно может иметь (и действительно имеет ) больше чем одно значение на группу вывода. Чтобы избегать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос( вывод показывается в Таблице 6.8 ):

SELECT snum, MAX (amt)

FROM Orders

WHEREodate=10/03/1990

GROUP BY snum;

SQL Execution Log

SELECT snum, odate, MAX (amt)

FROM Orders GROUP BY snum, odate;

snum
1001	767.19
1002	5160.45
1014	1900.10
1007	1098.16

Таблица 6.8: Максимальное значение суммы приобретений у каждого

продавца на 3 Октября

Поскольку поля odate нет, не может быть и выбраных полей, значение этих данных меньше чем в некоторых других примерах. Вывод должен вероятно включать что-нибудь такое что говорит - " это - самые большие порядки на 3 Октября." В Главе 7, мы покажем как вставлять текст в ваш вывод.

Как и говорилось ранее, HAVING может использовать только аргументы которые имеют одно значение на группу вывода. Практически, ссылки на агрегатные функции - наиболее общие, но и поля выбранные с помощью GROUP BY также допустимы. Например, мы хотим увидеть наибольшие порядки для Serres и Rifkin:

SELECT snum, MAX (amt)

FROM Orders

GROUP BY snum

HAVING snum B (1002,1007);

Вывод для этого запроса показывается в Таблице 6.9.

SQL Execution Log

SELECT snum, MAX (amt)| FROM Orders

GROUP BY snum HAVING snum IN (1002, 1007 );

snum
1002	5160.45
1007	1098.16

Таблица 6. 9: Использование HAVING с GROUP BY полями

НЕ ДЕЛАЙТЕ ВЛОЖЕННЫХ АГРЕГАТОВ

В строгой интерпретации ANSI SQL, вы не можете использовать агрегат агрегата. Предположим что вы хотите выяснять, в какой день имелась наибольшая сумма приобретений. Если вы попробуете сделать это, то ваша

SELECT odate, MAX (SUM (amt) )

FROM Orders

GROUP BY odate;

команда будет вероятно отклонена. (Некоторые реализации не предписывают этого ограничения, которое является выгодным, потому что вложенные агрегаты могут быть очень полезны, даже если они и несколько проблематичны.) В вышеупомянутой команде, например, SUM должен применяться к каждой группе поля odate, а MAX ко всем группам, производящим одиночное значение для всех групп. Однако предложение GROUP BY подразумевает что должна иметься одна строка вывода для каждой группы поля odate.