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

Стандарт 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, обеспечивающая объединение данных в группы, то ни один из элементов списка выборки SELECT не может включать каких-либо ссылок на столбцы, за исключением случая, когда этот столбец используется как параметр агрегирующей функции. Например, следующий запрос является некорректным:

SELECT staffNo, COUNT(salary)

FROM Staff;

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

Пример 13. Использование функции COUNT(*). Определите, сколько сдаваемых в аренду объектов имеют ставку арендной платы более 350 фунтов стерлингов в месяц,

SELECT COUNT(*) AS count

FROM PropertyForRent

WHERE rent > 350;

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

Таблица 23

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 – это позволит исключить из расчета повторяющиеся значения. Результаты выполнения запроса представлены в табл. 24.

Таблица 24

count

2

Пример 15. Использование функций COUNT и SUM. Определите общее количество менеджеров компании и вычислите сумму их годовой зарплаты.

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

FROM Staff

WHERE position = 'Manager';

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

Таблица 25

count sum

2 54000.00

Пример 16. Использование функций MIN, MAXnAVG. Вычислите значение минимальной, максимальной и средней заработной платы.

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

FROM Staff;

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

Таблица 26.

Результат выполнения запроса

min	max	avg
9000.00	30000.00	17000.00

Группирование результатов (конструкция GROUP BY). Приведенные выше примеры сводных данных подобны итоговым строкам, обычно размещаемым в конце отчетов. В итогах все детальные данные отчета сжимаются в одну обобщающую строку. Однако очень часто в отчетах требуется формировать и промежуточные итоги. Для этой цели в операторе 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 и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.

Пример 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. Результаты выполнения запроса представлены в табл. 27.

Таблица 27

Результат выполнения запроса

branchNo	Count	Sum
В003	3	54000.00
В005	2	39000.00
В007	1	9000.00

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

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

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

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

branchNo		staffNo		Salary
В00З		SG37		12000.00
В00З		SG14		1 8000.00
В00З		SG5		24000.00
В005		SL21		30000.00
В005		SL41		9000.00
В007		SA9		9000.00

COUNT(staffNo)	SUM(salary)
3	54000.00
2	39000.00
1	9000.00

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

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

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, создаются два результата вычисления агрегирующих функций, поэтому в некоторых случаях возможно появление строк, содержащих нулевые значения.

Ограничения на выполнение группирования (конструкция HAVING). Конструкция HAVING предназначена для использования совместно с конструкцией GROUP BY для задания ограничений, указываемых с целью отбора тех групп, которые будут помещены в результирующую таблицу запроса. Хотя конструкции HAVING и WHERE имеют сходный синтаксис, их назначение различно. Конструкция WHERE предназначена для отбора отдельных строк, предназначенных для заполнения результирующей таблицы запроса, а конструкция HAVING используется для отбора групп, помещаемых в результирующую таблицу запроса. Стандарт ISO требует, чтобы имена столбцов, применяемые в конструкции HAVING, обязательно присутствовали в списке элементов GROUP BY или применялись в агрегирующих функциях. На практике условия поиска в конструкции HAVING всегда включают, по меньшей мере, одну агрегирующую функцию; в противном случае эти условия поиска должны быть помещены в конструкцию WHERE и применены для отбора отдельных строк. (Помните, что агрегирующие функции не могут использоваться в конструкции WHERE.) Конструкция HAVING не является необходимой частью языка SQL – любой запрос, написанный с использованием конструкции HAVING, может быть представлен в ином виде, без ее применения.

Пример 18. Использование конструкции HAVING. Для каждого отделения компании с численностью персонала более одного человека определите количество работающих и сумму их заработной платы.

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

FROM Staff

GROUP BY branchNo

HAVING COUNT(staffNo) > 1

ORDER BY branchNo;

Этот пример аналогичен предыдущему, но здесь используются дополнительные ограничения, указывающие на то, что нас интересуют сведения только о тех отделениях компании, в которых работает больше одного человека. Подобное требование налагается на группы, поэтому в запросе следует использовать конструкцию HAVING. Результаты выполнения запроса представлены в табл. 28.

Таблица 28

branchNo count sum

В00З 3 54000.00

В005 2 39000.00

Подзапросы. В этом разделе мы обсудим использование законченных операторов SELECT, внедренных в тело другого оператора SELECT. Внешний (второй) оператор SELECT использует результат выполнения внутреннего (первого) оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут находиться в конструкциях WHERE и HAVING внешнего оператора SELECT – в этом случае они получают название подзапросов, или вложенных запросов. Кроме того, внутренние операторы SELECT могут использоваться в операторах INSERT, UPDATE и DELETE. Существуют три типа подзапросов.

• Скалярный подзапрос возвращает значение, выбираемое из пересечения одного столбца с одной строкой, т.е. единственное значение. В принципе скалярный подзапрос может использоваться везде, где требуется указать единственное значение. Варианты скалярных подзапросов приведены в примерах 13 и 14.

• Строковый подзапрос возвращает значения нескольких столбцов таблицы, но в виде единственной строки. Строковый подзапрос может использоваться везде, где применяется конструктор строковых значений, – обычно это предикаты. Вариант строкового подзапроса приведен в примере 15.

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

Пример 19. Использование подзапроса с проверкой на равенство. Составьте список персонала, работающею в отделении компании, расположенном по адресу 463 Main St1.

SELECT staffNo, fName, IName, position

FROM Staff

WHERE branchNo = (SELECT branchNo

FROM Branch

WHERE street = '163 Main S t ' } ;

Внутренний оператор SELECT (SELECT branchNo FROM Branch ...) предназначен для определения номера отделения компании, расположенного по адресу '163 Main St'. (Существует только одно такое отделение компании, поэтому данный пример является примером скалярного подзапроса.) После получения номера требуемого отделения выполняется внешний подзапрос, предназначенный для выборки подробных сведений о работниках этого отделения. Иначе говоря, внутренний оператор SELECT возвращает таблицу, состоящую из единственного значения 'BOOV. Оно представляет собой номер того отделения компании, которое находится по адресу '163 Main St1. Б результате внешний оператор SELECT приобретает следующий вид:

SELECT staffNo, fName, IName, position

FROM Staff

WHERE branchNo = 'B0031;

Результаты выполнения этого запроса представлены в табл. 29.

Таблица 29

Результат выполнения запроса

staffNo	fName	IName	position
SG37	Ann	Beech	Assistant
SG14	David	Ford	Supervisor
SG5	Susan	Brand	Manager

Подзапрос представляет собой инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Подзапрос, можно указывать непосредственно после операторов сравнения (т.е. операторов =, <, >, <=, >=, <>) в конструкции WHERE или HAVING. Текст подзапроса должен быть заключен в круглые скобки.

Пример 20. Использование подзапросов с агрегирующими функциями. Составьте список всех сотрудников, имеющих зарплату выше средней, указав, насколько их зарплата превышает среднюю зарплату по предприятию.

SELECT staffNo, fName, IName, position, salary - (SELECT AVG(salary) FROM Staff) AS salDiff

FROM Staff

WHERE salary > (SELECT AVG(salary) FROM S t a f f ) ;

Необходимо отметить, что нельзя непосредственно включить в запрос выражение 'WHERE salary > AVG (salary)', поскольку применять агрегирующие функции в конструкции WHERE запрещено. Для достижения желаемого результата следует создать подзапрос, вычисляющий среднее значение годовой заработной платы, а затем использовать его во внешнем операторе SELECT, предназначенном для выборки сведений о тех работниках компании, чья зарплата превышает это среднее значение. Иначе говоря, подзапрос возвращает значение средней зарплаты по компании в год, равное 17 000 фунтов стерлингов.

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

SELECT staffNo, fName, IName, position, salary - 17000 As salDiff

FROM Staff

WHERE salary > 17000;

Результаты выполнения запроса представлены в табл. 30.

Таблица 30.

Результат выполнения запроса

staffNo	fName	IName	position	salDiff
SL21	John	White	Manager	13000.00
SG14	David	Ford	Supervisor	1000.00
SG5	Susan	Brand	Manager	7000.00

К подзапросам применяются следующие правила и ограничения.

1. В подзапросах не должна использоваться конструкция ORDER BY, хотя она может присутствовать во внешнем операторе SELECT.

2. Список выборки SELECT подзапроса должен состоять из имен отдельных столбцов или составленных из них выражений, за исключением случая, когда в подзапросе используется ключевое слово EXISTS.

3. По умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в конструкции FROM подзапроса. Однако разрешается ссылаться и на столбцы таблицы, указанной в конструкции FROM внешнего запроса, для чего используются уточненные имена столбцов (как описано ниже).

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

SELECT staffNo, fName, IName, position, salary

FROM Staff

WHERE (SELECT AVG(salary) FROM Staff) < salary;

Пример 21. Вложенные подзапросы и использование предиката IN. Составьте перечень сдаваемых в аренду объектов, за которые отвечают работникиотделения компании, расположенного по адресу'163 Main st1.

SELECT propertyNo, street, city, postcode, type, rooms, rent

FROM PropertyForRent