Главная | Обратная связь | Поможем написать вашу работу!

Использование агрегирующих функций языка SQL

⇐ ПредыдущаяСтр 7 из 13Следующая ⇒

Стандарт ISO содержит определение следующих пяти агрегирующих функций:

• COUNT — возвращает количество значений в указанном столбце;

• SUM — возвращает сумму значений в указанном столбце;

• AVG — возвращает усредненное значение в указанном столбце;

• MIN — возвращает минимальное значение в указанном столбце;

• МАХ — возвращает максимальное значение в указанном столбце.

.Все эти функции оперируют со значениями в единственном столбце таблицы и возвращают единственное значение. Функции COUNT, MIN и МАХ применимы как к числовым, так и к нечисловым полям, тогда как функции SUM и AVG могут использоваться только в случае числовых полей. За исключением COUNT (*), при вычислении результатов любых функций сначала исключаются все пустые значения, после чего требуемая операция применяется только к оставшимся непустым значениям столбца. Вариант COUNT (*} является особым случаем использования функции COUNT — его назначение состоит в подсчете всех строк в таблице, независимо от того, содержатся там пустые, повторяющиеся или любые другие значения.

Если до применения агрегирующей функции необходимо исключить повторяющиеся значения, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT. Стандарт ISO допускает использование ключевого слова ALL с целью явного указания того, что исключение повторяющихся значений не требуется, хотя это ключевое слово подразумевается по умолчанию, если никакие иные определители не заданы. Ключевое слово DISTINCT не имеет смысла для функций MIN и МАХ. Однако его использование может оказывать влияние на результаты выполнения функций SUM и AVG, поэтому следует заранее обдумать, должно ли оно присутствовать в каждом конкретном случае. Кроме того, ключевое слово DISTINCT в каждом запросе может быть указано не более одного раза.

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

SELECT содержит агрегирующую функцию, а в тексте запроса отсутствует конструкция GROUP BY, обеспечивающая объединение данных в группы (см. раздел 5.3.4), то ни один из элементов списка выборки SELECT не может включать каких-либо ссылок на столбцы, за исключением случая, когда этот столбец используется как параметр агрегирующей функции. Например, следующий запрос является некорректным:

SELECT staffNo, COUNT(salary)

FROM Staff;

Ошибка состоит в том, что в данном запросе отсутствует конструкция GROUP BY, а обращение к столбцу staffNo в списке выборки SELECT выполняется без применения агрегирующей функции.

Пример 5.13. Использование функции COUNT(*)

Определите, сколько сдаваемых в аренду объектов имеют ставку арендной платы более 350 фунтов стерлингов в месяц,

SELECT COUNT(*) AS count

FROM PropertyForRent

WHERE rent > 350;

Ограничение на подсчет только тех сдаваемых в аренду объектов, арендная плата которых составляет более 350 фунтов стерлингов в месяц, реализуется посредством использования конструкции WHERE. Общее количество сдаваемых в аренду объектов, отвечающих указанному условию, может быть определено с помощью агрегирующей функции COUNT. Результаты выполнения запроса представлены в табл. 5.16.

Таблица 5.16. Результат выполнения запроса из примера 5.13

Count

Пример 5.14. Использование функции COUNT(DISTINCT)

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

SELECT COUNT(DISTINCT propertyNo) AS count

FROM Viewing

WHERE date BETWEEN 'l-May-011 AND '31-May-Ol1;

И в этом случае ограничение результатов запроса анализом только тех сдаваемых в аренду объектов, которые были осмотрены в мае 2001 года, достигается посредством использования конструкции WHERE. Общее количество осмотренных объектов, удовлетворяющих указанному условию, может быть определено с помощью агрегирующей функции COUNT. Однако, поскольку один и тот же объект может быть осмотрен различными клиентами несколько раз, необходимо в определении функции указать ключевое слово DISTINCT — это позволит исключить из расчета повторяющиеся значения. Результаты выполнения запроса представлены в табл. 5.17.

Таблица 5.17. Результат выполнения запроса из примера 5.14

Count

Пример 5.15. Использование функций COUNT и SUM

Определите общее количество менеджеров компании и вычислите сумму их годовой

зарплаты.

SELECT COUNT(staffNo) AS count, SUM(salary) AS sum

FROM Staff

WHERE position = 'Manager';

Глава 5. Язык SQL: манипулирование данными 183

Ограничение на отбор сведений только о менеджерах компании достигается указанием в запросе соответствующей конструкции WHERE. Общее количество менеджеров и сумма их годовой заработной платы определяются путем применения к результирующей таблице запроса агрегирующих функций COUNT и SUM. Результаты выполнения запроса представлены в табл. 5.18.

Таблица 5.18. Результат выполнения запроса из примера 5.15

count sum

2 54000.00

Пример 5.16. Использование функций MIN, MAXnAVG

Вычислите значение минимальной, максимальной и средней заработной платы.

SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg

FROM Staff;

В этом примере необходимо обработать сведения обо всем персонале компании, поэтому использовать конструкцию WHERE не требуется. Необходимые значения могут быть вычислены с помощью функций MIN, MAX и AVG, применяемых к столбцу salary таблицы Staff. Результаты выполнения запроса представлены в табл. 5.19.

Таблица 5.19. Результат выполнения запроса из примера 5.16

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

формировать и промежуточные итоги. Для этой цели в операторе SELECT может указываться конструкция GROUP BY. Запрос, в котором присутствует конструкция GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная итоговая строка. Столбцы, перечисленные в конструкции GROUP BY, называются группируемыми столбцами. Стандарт ISO требует, чтобы конструкции SELECT и GROUP BY были тесно связаны между собой. При использовании в операторе SELECT конструкции GROUP BY каждый элемент списка в списке выборки SELECT должен иметь единственное значение для всей группы. Более того, конструкция SELECT может включать только следующие типы элементов:

• имена столбцов;

• агрегирующие функции;

• константы;

• выражения, включающие комбинации перечисленных выше элементов.

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

Стандартом ISO определено, что при проведении группирования все отсутствующие значения рассматриваются как равные. Если две строки таблицы в одном и том же группируемом столбце содержат значения NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.

Пример 5.17. Использование конструкции GROUP BY

Определите количество персонала, работающего в каждом из отделений компании, а также их суммарную заработную плату.

SELECT branchNo, COUNT{staffNo} AS count, SUM(salary) AS sum

FROM Staff

GROUP BY branchNo

ORDER BY branchNo;

Нет необходимости включать имена столбцов staffNo и salary в список элементов GROUP BY, поскольку они появляются только в списке выборки SELECT с агрегирующими функциями. В то же время столбец branchNo в списке конструкции SELECT не связан с какой-либо агрегирующей функцией и по этой причине обязательно должен быть указан в конструкции GROUP BY. Результаты выполнения запроса представлены в табл. 5.20.

Таблица 5.20. Результат выполнения запроса из примера 5.17

Концептуально при обработке этого запроса выполняются следующие действия.

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

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

3. Полученные строки результирующей таблицы сортируются в порядке возрастания номера отделения, указанного в столбце branchNo.

Рис. 5.1. Три группы записей, создаваемые при выполнении запроса

Стандарт SQL допускает помещение в список выборки SELECT вложенных запросов (раздел 5.3.5). Поэтому приведенный выше запрос можно также представить следующим образом:

SELECT branchNo, (SELECT COUNT{staffNo) AS count

FROM Staff s

WHERE s.branchNo = b.branchNo),

(SELECT SUM(salary) AS sum

FROM Staff s

WHERE s.branchNo = b.branchNo)

FROM Branch b

ORDER BY branchNo;

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

⇐ Предыдущая 2 3 4 5 678 9 10 11 Следующая ⇒