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

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.10____________________________

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

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

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

Number_Of_Subjects

Запит 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