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

=============== SQL Execution Log ==============

| |

| SELECT snum, MAX (amt) |

| FROM Orders |

| GROUP BY snum; |

| =============================================== |

| snum |

| ------ -------- |

| 1001 767.19 |

| 1002 1713.23 |

| 1003 75.75 |

| 1014 1309.95 |

| 1007 1098.16 |

| |

================================================

Рисунок 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 Orders

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

WHERE odate = 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 IN(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.

РЕЗЮМЕ

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

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

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

Теперь , когда вы стали знатоком большого количества того как запрос производит значения, мы покажем вам, в Главе 7, некоторые вещи которые вы можете делать со значениями которые он производит.

РАБОТА С SQL

1. Напишите запрос который сосчитал бы все суммы приобретений на 3 Октября.

2. Напишите запрос который сосчитал бы число различных не-NULL значений поля city в таблице Заказчиков.

3. Напишите запрос который выбрал бы наименьшую сумму для каждого заказчика.

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

5. Напишите запрос который выбрал бы высшую оценку в каждом городе.

6. Напишите запрос который сосчитал бы число заказчиков регистрирующих каждый день свои порядки. (Если продавец имел более одного порядка в данный день, он должен учитываться только один раз.)

(См. Приложение A для ответов. )

7. ФОРМИРОВАНИЕ ВЫВОДОВ ЗАПРОСОВ

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

СТРОКИ И ВЫРАЖЕНИЯ

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

СКАЛЯРНОЕ ВЫРАЖЕНИЕ С ПОМОЩЬЮ ВЫБРАННЫХ ПОЛЕЙ

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

SELECT snum, sname, city, comm * 100

FROM Salespeople;

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

СТОЛБЦЫ ВЫВОДА

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

=============== SQL Execution Log ============

| |

| SELECT snum, sname, city, comm * 100 |

| FROM Salespeople; |

| ==============================================|

| snum sname city |

| ------ --------- ----------- --------- |

| 1001 Peel London 12.000000 |

| 1002 Serres San Jose 13.000000 |

| 1004 Motika London 11.000000 |

| 1007 Rifkin Barcelona 15.000000 |

| 1003 Axelrod New York 10.000000 |

| |

===============================================

Рисунок 7.1: Помещение выражения в вашем запросе

константы, или выражения в предложении SELECT запроса.

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

ПОМЕЩЕНИЕ ТЕКСТА В ВАШЕМ ВЫВОДЕ ЗАПРОСА