Групування даних

При аналізі даних часто виникає потреба підведення підсумків, тобто необхідний відбір даних і виконання арифметичних і статистичних операцій для групи записів. Слід одразу зазначити, що така обробка інформації, хоча і є звичною при економічних розрахунках, формуванні звітів, отриманні статистичної інформації, в той же час не є природною для операцій над відношеннями і виходить за рамки РА і РЧ. SQL підтримує наступні агрегатні (їх також називають підсумовуючими або груповими) функції:

• SUM ([ALL | DISTINCT] expression) обчислює суму поля в наборі записів;

• AVG ([ALL | DISTINCT] expression) обчислює середнє значення поля в наборі записів;

• MIN (expression) обчислює мінімальне значення поля в наборі записів;

• MAX (expression) обчислює максимальне значення поля в наборі записів;

• COUNT (*) повертає кількість записів в наборі.

• COUNT ([ALL | DISTINCT] expression) повертає кількість значень expression в наборі. Ключ DISTINCT вказує на те, що враховуються тільки різні значення expression. Ключ ALL на відміну від (*) не підраховує записи зі значенням expression NULL.

Запит: Підрахувати кількість студентів, зареєстрованих в БД.

Рішення: SELECT COUNT (*) FROM СТУДЕНТ.

Запит: Підрахувати кількість студентів, які здавали іспити.

Рішення: SELECT COUNT (DISTINCT ID_Stud) FROM успішності.

Запит: Підрахувати кількість студентів, які здавали курс K2 хоча б один раз.

Рішення: SELECT COUNT (DISTINCT ID_Stud) FROM УСПІШНІСТЬ WHERE ID_Subj = 'К2'.

Запит: Підрахувати кількість студентів, які здавали курс K2.

Рішення: SELECT COUNT (ALL ID_Stud) FROM УСПІШНІСТЬ WHERE ID_Subj = 'К2'

Запит: Підрахувати кількість студентів, що мають консультантів.

Рішення: SELECT COUNT (ALL Консультант) FROM СТУДЕНТ.

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

У групу збираються записи по однаковому значенню деякого поля (декількох полів). Для цих цілей існує інструкція SQL "групувати" - GROUP BY. При цьому можна користуватися і інструкцією WHERE.

Запит: Підрахувати середній бал кожного студента за перші три семестри.

Рішення:SELECT ID_Stud, AVG (Оцінка) FROM УСПІШНІСТЬ WHERE Семестр> = 1 AND Семестр <= 3GROUP BY ID_Stud.

Додатково SQL допускає відбір значень (вибірку) в уже отриманих групах. Для цього використовується конструкція HAVING, яка аналогічна WHERE для відбору за значеннями агрегатних функцій.

Запит: Вивести ідентифікаційні номери і середній бал студентів, середній бал яких більше 4,5.

Рішення:SELECT ID_Stud, AVG (Оцінка) FROM УСПІШНІСТЬ GROUP BY ID_Stud HAVING AVG (Оцінка)> 4,5.

Запит: Відібрати предмети, за якими кількість невстигаючих студентів більше десяти.

Рішення:SELECT ID_Subj, COUNT (ID_Stud) FROM УСПІШНІСТЬ

WHERE Оцінка = 2 GROUP BY ID_Subj HAVING COUNT (ID_Stud)> 10.

Загальний синтаксис оператора SELECT з угрупованням виглядає таким чином.

SELECT {column_name | aggregate_expression

[, Column_name | aggregate_expression] ...} FROM tablejname [WHERE seachconditionsl] GROUP BY [ALL] {aggregate_free_expression

[, Aggregate_free_expression] ...} HAVING seachjconditions2

Існує ряд правил, яких слід дотримуватися при використанні угрупувань і агрегатних функцій.

• Якщо в "цільовому списку" містяться неагрегатні вирази (імена полів, неагрегатні обчислювані поля і т.п.), то по ним повинне проводиться угруповання, тобто вони повинні бути в обов'язковому порядку бути згадані в GROUP BY.

• Оскільки інструкція HAVING виконується після формування підсумків, то умова в HAVING, навпаки, повинна містити обмеження на стовпці тільки з агрегатними функціями.

• Інструкція HAVING ніколи не вживається без GROUP BY.

• Угрупування може відбуватися по обчислюваним полям, тобто інструкція GROUP BY може містити обчислювані поля.

• GROUP BY ALL ігнорує інструкцію WHERE