агрегатні функції

Для підведення підсумків за інформацією, що міститься в БД, в SQL передбачені агрегатні функції. Агрегатна функція приймає в якості аргументу будь-якої стовпець даних цілком, а повертає одне значення, яке певним чином підсумовує цей стовпець.

Наприклад, агрегатна функція AVG () приймає в якості аргументу стовпець чисел і обчислює їх середнє значення.

Щоб обчислити середній дохід жителя Зеленограда, потрібен такий запит:

SELECT 'середньодушовий дохід =', AVG (SUMD)

В SQL є шість агрегатних функцій, які дозволяють отримувати різні види підсумкової інформації (рис. 1):

- SUM () обчислює суму всіх значень, що містяться в стовпці;

- AVG () обчислює середнє серед значень, що містяться в стовпці;

- MIN () знаходить найменше серед всіх значень, що містяться в стовпці;

- MAX () знаходить найбільше серед усіх значень, що містяться в стовпці;

- COUNT () підраховує кількість значень, що містяться в стовпці;

- COUNT (*) підраховує кількість рядків у таблиці результатів запиту.

Аргументом агрегатної функції може бути просте ім'я стовпця, як в попередньому прикладі, або вираз, як у наступному запиті, що задає обчислення середньодушового податку:

При виконанні цього запиту створюється тимчасовий стовпець, що містить значення (SUMD * 0.13) для кожного рядка таблиці PERSON, а потім обчислюється середнє значення тимчасового стовпчика.

Суму доходів у всіх жителів Зеленограда можна обчислити за допомогою агрегатної функції SUM:

SELECT SUM (SUMD) FROM PERSON

Агрегатна функція може бути використана і для обчислення підсумків по таблиці результатів, отриманої з'єднанням декількох вихідних таблиць. Наприклад, можна обчислити загальну суму доходу, яка отримана жителями від джерела з назвою «Стипендія»:

FROM PROFIT, HAVE_D

Where profit.Id = have_d.Id

Агрегатні функції MIN () і MAX () дозволяють знайти відповідно найменше і найбільше значення в таблиці. При цьому стовпець може містити числові або строкові значення або значення дати або часу.

Наприклад, можна визначити:

(А) найменший спільний дохід, отриманий жителями, і найбільший податок, який підлягає сплаті:

SELECT MIN (SUMD), MAX (SUMD * 0.13)

(Б) дати народження найстарішого і наймолодшого жителя:

SELECT MIN (RDATE), MAX (RDATE)

(В) прізвища, імена та по батькові самого першого і останнього жителів в списку, що згруповані за алфавітом:

SELECT MIN (FIO), MAX (FIO)

Застосовуючи ці агрегатні функції, потрібно пам'ятати, що числові дані порівнюються з арифметичним правилам, порівняння дат відбувається послідовно (більш ранні значення дат вважаються меншими, ніж більш пізні), порівняння інтервалів часу виконується на підставі їх тривалості.

При використанні функції MIN () і MAX () із строковими даними результат порівняння двох рядків залежить від використовуваної таблиці кодування символів.

Агрегатна функція COUNT () підраховує кількість значень в стовпці будь-якого типу:

(А) скільки квартир в 1-му мікрорайоні?

SELECT COUNT (ADR) FROM FLAT WHERE ADR LIKE '%, 1_ _-%'

(Б) скільки жителів мають джерела доходу?

SELECT COUNT (DISTINCT NOM) FROM HAVE_D

(В) скільки джерел доходу використовуються жителями?

SELECT COUNT (DISTINCT ID) FROM HAVE_D (ключовий слово DISTINCT вказує, що підраховуються неповторювані значення в стовпці).

Спеціальна агрегатна функція COUNT (*) підраховує рядки в таблиці результатів, а не значення даних:

(А) скільки квартир у 2-му мікрорайоні?

SELECT COUNT (*) FROM FLAT WHERE ADR LIKE '%, 2 __-%'

(Б) скільки джерел доходу у Іванова Івана Івановича?

SELECT COUNT (*) FROM PERSON, HAVE_D WHERE FIO = 'Іванов Іван Іванович' AND PERSON.NOM = HAVE_D.NOM

SELECT COUNT (*) FROM PERSON WHERE ADR = 'Зеленоград, 1001-45'

Один із способів зрозуміти, як виконуються підсумкові запити з агрегатними функціями, це уявити виконання запиту розбитим на дві частини. Спочатку визначається, як би запит працював без агрегатних функцій, повертаючи кілька рядків результатів. Потім застосовуються агрегатні функції до результатів запиту, повертаючи одну підсумкову рядок.

Наприклад, розглянемо наступний складний запит: знайти середньодушовий загальний дохід, суму загальних доходів жителів, а також середню прибутковість джерела в процентах від загального доходу жителя. Відповідь дає оператор

SELECT AVG (SUMD), SUM (SUMD), (100 * AVG (MONEY / SUMD)) FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID

Без агрегатних функцій запит виглядав би так:

SELECT SUMD, SUMD, MONEY / SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID

і повертав би один рядок результатів для кожного жителя і конкретного джерела доходу. Агрегатні функції використовують стовпчики таблиці результатів цього запиту для отримання однорядковою таблиці з підсумковими результатами.

У рядку повертаються стовпців замість імені будь-якого стовпця можна вказати агрегатную функцію. Наприклад, вона може входити в вираз, в якому підсумовуються або віднімаються значення двох агрегатних функцій:

SELECT MAX (SUMD) -MIN (SUMD) FROM PERSON

Однак агрегатна функція не може бути аргументом для іншої агрегатної функції, тобто заборонені вкладені агрегатні функції.

Крім того, в списку повертаються стовпців можна одночасно використовувати агрегатні функції і звичайні імена стовпців, оскільки в цьому немає сенсу, наприклад:

SELECT FIO, SUM (SUMD) FROM PERSON

Тут перший елемент списку вказує, щоб СУБД створила таблицю, яка буде складатися з декількох рядків і містити по одному рядку для кожного жителя. Другий елемент списку просить СУБД отримати одне результуюче значення, що є сумою значень стовпця SUMD. Ці два вказівки суперечать один одному, що призводить до помилки.

З цієї причини або всі посилання на стовпці в списку повертаються стовпців повинні бути аргументами агрегатних функцій, або в списку не повинно бути жодної агрегатної функції.

Сказане не стосується випадків обробки підзапитів і запитів з угрупованням.