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

Функции в SQL позволяют выполнять различные вычислительные операции, которые не возможно выразить стандартными средствами языка. Они позволяют существенно увеличить возможности по манипулированию дан­ны­ми. Стандарт ANSI определяет набор функций, однако в конкретных СУБД этот список, как правило, сущест­вен­но расширяется. Например, SQL Oracle содержит более 120 функций.

Из множества имеющихся функций мы рассмотрим только, так называемые, агрегатные функции, которые включены в стандарт ANSI. К ним относятся следующие:

· COUNT - возвращает количество значений указанного столбца;.

· SUM - возвращает сумму всех значений столбца;

· AVG - возвращает среднее значение всех значений столбца;

· MAX - возвращает наибольшее значение в столбце;

· MIN - возвращает наименьшее значение в столбце.

Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение.

Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения.

Следует отметить, что здесь столбец - это столбец виртуальной таблицы, в которой могут содержаться данные не только из столбца базовой таблицы, но и данные, полученные путем функционального преобразования и (или) связывания символами арифметических операций значений из одного или нескольких столбцов. При этом выражение, определяющее столбец такой таблицы, может быть сколь угодно сложным, но не должно содержать SQL-функций (вложенность SQL-функций не допускается). Например, выражение типа AVG(Ставка+ Надбавка/2) является вполне допустимым. Из SQL-функций можно составлять любые выражения, например, SUM().

Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Специальная же функция COUNT(*) служит для подсчета всех без исключения строк в таблице (включая дубликаты).

Эти функции можно применять только во фразе SELECT. Если в запросе нет фразы GROUP BY (см. подраздел ниже), то область действия агрегатной функции распространяется на все результирующее отношение, а если эта фраза присутствует – то на создаваемые ею группы. Рассмотрим на примерах использование этих функций.

3) Какова вместимость всех аудиторий в корпусе 5.

SELECT SUM(Seats) Total_Number-Of-Seats-In-Building-5

FROM ROOM

WHERE Building = 5

4) Сколько студентов на факультете информатики.

SELECT SUM(GROUP. Quantity) Number-Of-Students-In-IT-Faculty

FROM FACULTY, DEPARTMENT, GROUP

WHERE FACULTY.#F = DEPARTMENT.#F AND

DEPARTMENT.#D = GROUP.#D AND

FACULTY.Name = "IT"

5) Какой самый большой фонд финансирования среди кафедр факультета информатики.

SELECT MAX(DEPARTMENT.Fund) MAX-Department-Fund-In-IT-Faculty

FROM FACULTY, DEPARTMENT

WHERE FACULTY.#F = DEPARTMENT.#F AND

FACULTY.Name = "IT"

Можно задавать несколько агрегатных функций. Эти функции могут использоваться в выражениях. Например:

6) Какова вместимость всех аудиторий корпуса 5, количество мест в самой маленькой и самой большой аудитории, средняя вместимость.

SELECT SUM(Seats) Total_Number-Of-Seats-In-Building-5,

MIN(Seats) Seats-In-The-Smallest-Room,

MAX(Seats) Seats-In-The-Largest-Room

SUM(Seats) / Count(*) Average-Number-Of-Seats

FROM ROOM

WHERE Building = 5

Внимание! Если в запросе нет фразы GROUP BY, то использование имен столбцов совместно с агрегатными функциями не допустимо. Например, попытка выполнить следующий запрос:

SELECT Building, SUM(Seats) Total_Number-Of-Seats-In-Building,

FROM ROOM

завершается выдачей сообщения об ошибке типа следующего:

Dynamic SQL Error

-SQL error code = -104

-invalid column reference

Внимание! Агрегатные функции не применимы во фразе WHERE. Например, при попытке выполнить запрос:

SELECT Name

FROM DEPARTMENT

WHERE Fund = MAX(Fund)

может быть выдано сообщение типа следующего:

ERROR at line 3:

ORA-00934: group function is not allowed here

Неопределенные значения в агрегатных функциях. Отметим, что в столбце-аргументе агрегатной функции перед применени­ем любой функции, кроме COUNT(*), исключаются все неопределенные значения. Например, при формулировке следующего запроса:

SELECT COUNT(Building), COUNT(DISTINCT Building), COUNT(*)

FROM FACULTY

можно получить следующий ответ:

COUNT(Building) COUNT(DISTINCT Building) COUNT(*)

-------------- ------------------------ --------

15 11 17

Его следует интерпретировать следующим образом: Всего имеется 17 факультетов, для 15 факультетов заданы значения корпуса (два факультета в поле корпуса содержат NULL), наконец, среди 15-ти значений корпусов только 11 являются различными, то есть не повторяющимися.

Если оказывается, что аргумент - пустое множество, функция COUNT принимает значение 0, а остальные – NULL.

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