4.2.4. Використання агрегатних функцій

SQL містить функції, що дають змогу обчислювати різні статистичні характери­стики. Такі функції називаються агрегатними. Стандарт ANSI визначає певний набір функцій, однак у СКБД він зазвичай значно розширюється. Наприклад, діалект SQL, що використовується в СКБД Oracle, містить понад 120 функцій.

З усієї множини функцій SQL ми розглянемо лише ті, що перелічені в стан­дарті ANSI. А саме:

• COUNT — повертає кількість рядків у таблиці;

• SUM — повертає суму всіх значень у стовпці;

• AVG — повертає середнє арифметичне всіх значень у стовпці;

• МАХ — повертає найбільше значення у стовпці;

• MIN - повертає найменше значення у стовпці.

Окрім C0UNT(*), кожна з цих функцій оперує (як аргументом) сукупністю зна­чень стовпця певної таблиці та повертає єдине значення. Для функцій SUM і AVG стовпець-аргумент повинен містити числові значення.

Слід зазначити, що у даному випадку стовпець-аргумент — це стовпець вірту­альної таблиці, в якій можуть міститися дані не лише зі стовпця базової таблиці, але й отримані шляхом функціонального перетворення та/або зв'язування сим­волами арифметичних операцій значень з одного або кількох стовпців. Вирази, що визначають стовпець такої таблиці, мають різний рівень складності, але не можуть містити інших SQL-функцій. Наприклад, вираз АУЄССтавка + Надбавка/2) є припустимим, а вираз AVG(SUM(...)) — ні. SQL-функції можуть входити до складу виразів, наприклад SUM((DoHfl)/Count(*).

Аргументам усіх функцій, окрім C0UNT(*), може передувати модифікатор DIS­TINCT (різний), що вказує на необхідність видалення дублікатів перед застосуван­ням функції. Функція C0UNT(*) використовується для обчислення кількості всіх рядків таблиці з урахуванням дублікатів.

Агрегатні функції можна застосовувати лише у фразах SELECT та HAVING. Якщо в запиті немає фрази GROUP BY, то область дії агрегатної функції поширюється на все кінцеве реляційне відношення, а в разі її наявності — на створювані нею гру­пи. Розглянемо використання цих функцій на прикладах.

Запит 4.9

Визначити кількість кафедр на факультеті інформатики.

SELECT C0UNT(*) FROM ФАКУЛЬТЕТ. КАФЕДРА

WHERE ФАКУЛЬТЕТ.#F = КАФЕДРА.#F AND ФАКУЛЬТЕТ.Назва = "інформатики"

Результат цього запиту може виглядати так: C0UNT(*)

4

Запит 4.10

Визначити кількість предметів, що читаються.

SELECT C0UNT(*) Number_Of_Subjects FROM ПРЕДМЕТ

Тепер результат виглядає так:

Number_Of_Subjects

217

Запит 4.11 —.

Визначити місткість усіх аудиторій у корпусі 5.

SELECT SUM(MicTKicTb) Total_Number_0f_Seats_In_Building_5 FROM АУДИТОРІЯ

WHERE Корпус = 5

Запит 4.12

Визначити кількість студентів факультету інформатики.

SELECT БиМСГРУПА.Кількість) Number_Of_IT_Faculty_Students FROM ФАКУЛЬТЕТ, КАФЕДРА, ГРУПА

WHERE ФАКУЛЬТЕТ.#F = КАФЕДРА.#F AND

КАФЕДРА.#D = ГРУПА.#D AND

ФАКУЛЬТЕТ.Назва = "інформатики"

Запит 4.13

Визначити найбільший фонд із фінансування серед кафедр факультету інформа­тики.

SELECT MAX(КАФЕДРА.Фонд) MAX_Department_Fund_In_IT_Faculty FROM ФАКУЛЬТЕТ. КАФЕДРА

WHERE ФАКУЛЬТЕТ.#F = КАФЕДРА.#F AND ФАКУЛЬТЕТ.Назва = "інформатики"

Можна задавати кілька агрегатних функцій, що використовуються у виразах.

Запит 4.14

Визначити місткість усіх аудиторій корпусу 5, кількість місць у найменшій та найбільшій аудиторіях, середню місткість.

SELECT SUM(MicTKicTb) Total_Number_Of_Seats_In_Building_5.

MINCMiсткість) Seats_In_The_Smal1est_Room.

МАХ(Місткість) Seats_In_The_Largest_Room.

SUM(Mi сткі сть)/C0UNT(*) Average_Number_Of_Seats FROM АУДИТОРІЯ

WHERE Корпус = 5

Якщо в запиті немає фрази GROUP BY, то використання імен стовпців разом із аг­регатними функціями у фразі SELECT є неприпустимим. Наприклад, у результаті виконання запиту

SELECT Корпус. SUM(Miсткість) Tota1_Number_0f_Seats_In_Building FROM АУДИТОРІЯ

може бути виведене повідомлення про помилку:

Dynamic SQL Error -SQL error code = -104 -invalid column reference

Агрегатні функції не використовуються у фразі WHERE. В результаті виконання запиту

SELECT Назва

FROM КАФЕДРА

WHERE Фонд = МАХ(Фонд)

може бути виведене повідомлення про помилку:

ERROR at line 3:

ORA-00934: group function is not allowed here

Невизначені значення в агрегатних функціях

У стовпці-аргументі агрегатної функції перед застосуванням будь-якої функції, окрім C0UNT(*), виключаються всі невизначені значення. Сформулювавши запит

SELECT C0UNT(Корпус). C0UNT(DISTINCT Корпус). C0UNT(*) FROM ФАКУЛЬТЕТ

ви можете отримати, наприклад, таку відповідь:

COUNT(Kopnyc) COUNTCDISTINCT Корпус) C0UNT(*)

15 11 17

Результат виконання запиту слід інтерпретувати так: усього є 17 факультетів; для 15 з них задані значення корпусу (два факультети в полі корпусу містять NULL), і серед цих 15 значень корпусів лише 11 є різними, тобто неповторюваними.

Якщо виявляється, що аргумент — порожня множина, функція COUNT набуває значення 0, а інші — NULL.