SQLГлава. 6 Обобщение данных с помощью агрегатных функцийВ этой главе вы перейдёте о простого использования запросов к извлечению значений из базы данных (БД) и определите, как можно использовать эти значения, чтобы получить из них информацию. Это делается с помощью агрегатных (обобщающих) функций, которые берут группы значений из поля и сводят их до одиночного значения. Вы узнаете, как использовать эти функции, как определить группы значений, к которым они будут применяться, и как определить, какие группы выбираются для вывода. Вы увидите также, при каких условиях вы сможете объединить значения поля с этой полученной информацией в одном запросе. Что такое агрегатные функции?Запросы могут производить обобщённое групповое значение полей точно так же, как и значение одного поля. Это делается с помощью агрегатных функций. Агрегатные функции производят одиночное значение для всей группы таблицы. Вот список этих функций:
Как использовать агругатные функции?
Агрегатные функции используются, подобно именам полей в предложении
Только числовые поля могут использоваться с С функциями COUNT, MAX и MIN могут использоваться и числовые, и символьные поля.
При использовании с символьными полями,
Чтобы найти SELECT SUM (amt) FROM Orders; =============== SQL Execution Log ============ | | | SELECT SUM (amt) | | FROM Orders; | | ==============================================| | | | ------- | | 26658.4 | | =============================================== Рисунок 6.1 Определение суммы
Это, конечно, отличается от выбора поля, при котором возвращается одиночное значение, независимо от того, сколько строк находится в таблице. Из-за этого агрегатные функции и поля не могут выбираться одновременно, если не будет использовано предложение Нахождение усреднённой суммы — похожая операция (вывод следующего запроса показан на Рисунке 6.2): SELECT AVG (amt) FROM Orders; =============== SQL Execution Log ============ | | | SELECT AVG (amt) | | FROM Orders; | | ==============================================| | | | ------- | | 2665.84 | | =============================================== Рисунок 6.2 Выбор средней суммы Специальный атрибут COUNT
Функция SELECT COUNT (DISTINCT snum) FROM Orders; Использование DISTINCT
Обратите внимание в вышеупомянутом примере, что =============== SQL Execution Log ============ | | | SELECT COUNT (DISTINCT snum) | | FROM Orders; | | ==============================================| | | | ------- | | 5 | | =============================================== Рисунок 6.3 Подсчет значений поля
Вы можете выполнять несколько подсчётов (
Использование COUNT со строками, а не значениями
Чтобы подсчитать общее число строк в таблице, используйте функцию SELECT COUNT (*) FROM Customers
=============== SQL Execution Log ============ | | | SELECT COUNT (*) | | FROM Customers; | | ==============================================| | | | ------- | | 7 | =============================================== Рисунок 6.4 Подсчет строк вместо значений Включение дубликатов в агрегатные функции
Агрегатные функции могут также (в большинстве реализаций) использовать аргумент Различия между
Пока
Следующая команда подсчитает ( SELECT COUNT (ALL rating) FROM Customers; Агрегаты, построенные на скалярном выражении
До этого вы использовали агрегатные функции с одиночными полями как аргументами. Вы можете также использовать агрегатные функции с аргументами, которые состоят из скалярных выражений, включающих одно или более полей. (Если вы это делаете,
Предположим, что таблица Заказов имеет ещё один столбец, который хранит предыдущий неуплаченный баланс (поле Вы можете найти наибольший неуплаченный баланс следующим образом: SELECT MAX (blnc + amt) FROM Orders;
Для каждой строки таблицы этот запрос будет складывать Фактически имеется большое количество ситуаций в SQL, где можно использовать скалярные выражения с полями или вместо полей, как вы увидите в Главе 7. Предложение GROUP BY
Предложение
Например, предположим, что вы хотите найти наибольшую сумму продажи, полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав SELECT snum, MAX (amt) FROM Orders GROUP BY snum; Вывод для этого запроса показан на Рисунке 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 Нахождение максимальной суммы продажи у каждого продавца
Вы можете также использовать 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. Вы не сможете использовать агрегатную функцию в предложении SELECT snum, odate, MAX (amt) FROM Orders WHERE MAX (amt) > 3000.00 GROUP BY snum, odate;
Это будет отклонением от строгой интерпретации ANSI. Чтобы увидеть максимальную стоимость приобретений свыше $3000.00, вы можете использовать предложение
Предложение Правильной командой будет следующая: 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 Удаление групп агрегатных значений
Аргументы в предложении Следующая команда будет запрещена: SELECT snum, MAX (amt) FROM Orders GROUP BY snum HAVING odate = 10/03/1988;
Поле 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 октября
Поскольку поля
Как говорилось ранее, 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;
то ваша команда будет, вероятно, отклонена. (Некоторые реализации не предписывают этого ограничения,
что выгодно, потому что вложенные агрегаты могут быть очень полезны, даже если они и несколько проблематичны.) В вышеупомянутой команде, например, РезюмеТеперь вы используете запросы несколько по-иному. Способность получать, а не просто размещать значения, очень мощна. Это означает, что вы не обязательно должны следить за определённой информацией, если вы можете сформулировать запрос так, чтобы её получить. Запрос будет давать вам поминутные результаты, в то время как таблица общего или среднего значений будет хороша только некоторое время после её модификации. Это не должно наводить на мысль, что агрегатные функции могут полностью вытеснить потребность в отслеживании информации, такой, например, как эта.
Вы можете применять эти агрегаты для групп значений, определённых предложением
Объединенные вместе, эти особенности делают возможным производить агрегаты, основанные на чётко определённых подмножествах значений в поле. Затем вы можете определять другое условие для исключения определенных результатов групп с предложением Теперь, когда вы стали знатоком того, как запрос производит значения, мы покажем вам, в Главе 7, что вы можете делать со значениями, которые он производит. Работа с SQL
(См. ответы в Приложении A.) |
||