Групування даних. Опція GROUP BY

ОпціяGROUP BYневід’ємно пов’язана з агрегатними функціями, без них вона практично не використовується. GROUP BY поділяє таблицю на набори, а агрегатні функції обраховують для кожного з них підсумкові значення. Це значення називається агрегатним вектором.

Для ілюстрації застосування опції GROUP BY на прикладі таблиць VYKLADACH і РOST внесемо зміни до таблиці VYKLADACH, оскільки вміст таблиці не відповідає вимогам посилальної цілісності – для викладача „Турчак А.Д.” у підпорядкованій таблиціVYKLADACH поле KodPostмає значення „05”, тоді як такого значення немає в батьківській таблиці POSADA. Це може привести до ускладнення інтерпретації результатів запитів, що будуть продемонстровані нижче. Тому замінимо значення „05” на „03”.

UPDATE VYKLADACH SET VYKLADACH.KodPost = '03'

WHERE (VYKLADACH.KodVykl=17);

Додамо до таблиці VYKLADACH ще п’ять рядків, щоб зробити майбутні приклади більш виразними.

INSERT INTO VYKLADACH VALUES (18, "Батурин О.Й.", "02",6);

INSERT INTO VYKLADACH VALUES (19, "Балагура А.М.", "04",2);

INSERT INTO VYKLADACH VALUES (21, "Махно Н.Є.", "01",7);

INSERT INTO VYKLADACH VALUES (22, "Собчак А.О.", "01",12);

INSERT INTO VYKLADACH VALUES (24, "Черно Ф.М.", "04",2);

Примітка. Як було показано вище, замість п’яти запитів можна зробити один параметричний і п’ять разів увести значення полів рядків..

Новий вміст таблиці VYKLADACH наведено нижче.

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

Приклади:

Результати запитів

Груповання у середині груп. Шляхом сортування одночасно по декількох елементах можна створювати групи усередині груп. Розділяючи елементи, по яких буде проводитися групування, за допомогою якого можна розбити великі групи на підгрупи.. Покажемо це на прикладі запиту : визначити кількість викладачів за посадами по кафедрах, тобто групування спочатку будемо робити по кафедрах, а внутрі кафедр – по посадах.

SELECT VYKLADACH.NomKaf, POSADA.PostVykl, COUNT( VYKLADACH.KodVykl) AS [QtyTh]

FROM VYKLADACH INNER JOIN POSADA ON VYKLADACH.KodPost = POSADA.KodPost

GROUP BY VYKLADACH.NomKaf, POSADA.PostVykl;

Опція GROUP BY без агрегатних функцій.

Без агрегатних функцій GROUP BY нагадує DISTINCT. Воно розділяє таблицю на групи і для кожної з них повертає по одному рядку. Слід пам’ятати, що при використанні GROUP BY для кожного елемента зі списку вибору буде генеруватися по одному значенню на набір.

Приклади:

Запит

SELECT DISTINCT POSADA.PostVykl

FROM VYKLADACH INNER JOIN POSADA ON

VYKLADACH.KodPost = POSADA.KodPost;

та запит

SELECT POSADA.PostVykl

FROM VYKLADACH INNER JOIN POSADA ON

VYKLADACH.KodPost = POSADA.KodPost

GROUP BY POSADA.PostVykl;

дають один і той же результат.

GROUP BY з опцією WHERE.

При відсутності в запиті GROUP BY агрегатної функції застосовуються до всієї таблиці цілком. У цьому випадку для вибору рядків, що беруть участь в обчисленні, можна використовувати опцію WHERE. Це ж вірно й при наявності груп. Спільна робота опцій WHERE і GROUP BY відбувається наступним чином. Спочатку знаходяться всі рядки, що задовольняють умовам WHERE. Потім опція GROUP BY поділяє відібрані рядки на групи. Рядки, що не задовольняють умовам WHERE, не включаються в жодну групу.

Приклад: визначимо кількість викладачів на усіх кафедрах, крім №2, та відберемо серед них ті, що мають у своєму складі три і більше викладачів.

SELECT VYKLADACH.NomKaf, COUNT( VYKLADACH.KodVykl) AS QtyTh

FROM VYKLADACH WHERE ((( VYKLADACH.NomKaf)<>2))

GROUP BY NomKaf HAVING (COUNT( VYKLADACH.KodVykl)>=3);

Примітка. На перший погляд, в опції HAVING замість функції COUNT( VYKLADACH.KodVykl) можна було задати ім’я визначеного в опції SELECT нового поля QtyTh, але, як показує експеримент, ім’я нового поля в опцію HAVING не переходе.

Замітимо також, що той же самий результат, що у попередньому запиті, можна отримати, задав фільтр із опції WHERE в опцію HAVING :

SELECT VYKLADACH.NomKaf, COUNT( VYKLADACH.KodVykl) AS QtyTh

FROM VYKLADACH

GROUP BY VYKLADACH.NomKaf

HAVING ((( VYKLADACH.NomKaf)<>2) AND ((COUNT(VYKLADACH.KodVykl))>=3));

Це пояснюється тим, що в даному випадку і груповання, і фільтр у WHERE задаються по одному й тому ж полю.