Використання операторів Group by та Having при створенні запитів

Лекція № 16 (2 години)

Тема «Використання операторів Group by та Having при створенні запитів»

Мета: навчитися створювати запити з використанням операторів мови SQL Group By та Having.

Література

«Бази даних. Проектування, реалізація та супровід. Теорія та практика» -Т. Конолі, К. Бегг, А. Строчан Москва, СПб., Київ. 2000 р.

«Бази даних: основи, проектування, використання» - Малихіна М. П. СПб. 2004р.

«Організація баз даних та знань» - Пасічник В. В., Резніченко В. А., Київ, 2006 р.

«Системы баз данных. Полный курс» - Г. Гарсия Молина, Москва: Вильямс, 2003р.

«Бази даних. Моделі, розробка, реалізація» - Карпов Т., СПб. 2001 р.

Хід заняття

І. Організаційний момент

а) готовність групи до заняття;

б) перевірка присутніх.

ІІ. Актуалізація опорних знань студентів

а) повідомлення теми та мети заняття;

б) повідомлення девізу, під яким будете працювати;

в) відповіді на запитання.

ІІІ. Виклад нового матеріалу

План

Оператор Group by.
Оператор Having.

ІV. Узагальнення та систематизація знань.

V. Підведення підсумків заняття.

VІ. Домашнє завдання: вивчити матеріал лекції, знати відповіді на такі питання лекції:

Який оператор мови SQL дозволяє застосовувати агрегатні функції до отриманної підмножини значень?

Для чого призначений оператор мови SQL Having?

В чому різниця між операторами Where та Having?

Чому оператор Having треба використовувати спільно з оператором Group By?

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

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

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

Стандартом ISO визначено, що при проведенні групування всі відсутні значення розглядаються як рівні. Якщо два рядка таблиці в одному й тому ж стовпці, який групується, містять значення Null та ідентичні значення у всіх інших непустих стовпцях, які групуються, вони розміщуються в одну й ту ж групу.

Приклад 15. Використання оператора GROUP BY.

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

Select [Номер відділення], Count([Номер співробітника]) AS Кількість, Sum(ЗП) AS [Загальна ЗП]

From Співробітники

Group By [Номер відділення]

Order By [Номер відділення];

Немає потреби розміщувати імена стовgців «Номер співробітника» та «ЗП» у список оператора Group By, так як в списку оператора Select вони використовуються лише в агрегатних функціях. В той самий час стовпець «Номер відділення» в списку Select не пов’язаний з жодною агрегатною функцією та за цією причиною обов’язково повинен бути вказаний в операторі Group By.

Номер відділення	Кількість	Загальна ЗП
3	3	10050
5	2	7250
7	1	4300

При обробці цього запиту будуть виконані такі дії:

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

Номер відділення	Номер співробітника	ЗП
3	37	2400
3	14	4500
3	5	3150
5	21	3150
5	41	4100
7	9	4300
Номер відділення	Count (Номер співробітника)	Sum(З_П)
3	3	10050
5	2	7250
7	1	4300

Для кожної з груп обчислюється загальна кількість рядків, яка дорівнює кількості співробітников відділення, а також сума значень в стовпці «ЗП», яка є сумою заробітної плати всіх співробітників відділення компанії, яка нас цікавить. Потім генерується єдиний підсумковий рядок для всієї групи початкових рядків.
Наприкінці, отримані рядки підсумкової таблиці пересортовуються в порядку збільшення номера відділення, який вказаний в стовпці «Номер відділення».

Оператор Having призначений для використання спільно з оператором Group By для завдання обмежень, які вказані з метою відбору тих груп, які будуть розміщені в підсумковій таблиці запиту.

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

Стандарт ISO вимагає, щоб імена стовпців, які використовуються в операторі Having, обов’язково були присутніми в списку оператора Group By або використовувались в агрегатних функціях.

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

Треба пам’ятати, що агрегатні функції не можуть використовуватися в операторі Where. Оператор Having не є необхідною частиною SQL – будь-який запит, написаний з використанням оператору Having, може бути зображений в іншому вигляді без його використання.

Приклад 16. Використання оператора Having.

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

Select [Номер відділення], Count([Номер співробітника]) AS Кількість, Sum(ЗП) AS [Загальна ЗП]

From Співробітники

Group By [Номер відділення]

Having Count([Номер співробітника])>1

Order By [Номер відділення];

Цей приклад є аналогом попереднього, але тут використовуються додаткові обмеження, які вказують на те, що нас цікавлять відомості лише про ті відділення компанії, в яких працює більш ніж 1 особа. Така вимога накладається на групи, тому в запиті треба використовувати оператор Having.

Номер відділення

Кількість

Загальна ЗП

10050

7250

Використання операторів Group by та Having при створенні запитів