Практична робота № 6

Тема: Побудова запитів із застосуванням групових функцій

Мета: Навчитися будувати запити різних типів на мові SQL, застосовувати групові функції для обчислення різних значень полів.

1 Короткі теоретичні відомості

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

COUNT() – визначення кількості рядків у таблиці;

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

AVG() –обчислення середнього арифметичного всіх значень у стовпці;

MAX() –обчислення найбільшого значення у стовпці;

MIN() –обчислення найменшого значення у стовпці COUNT();

Усі функції, крім визначення кількості рядків у таблиці COUNT(), виконується над значеннями певного поля підсумків. Поле підсумків – це не поле групування, а інше поле числового, грошового або типу дата.

Розглянемо запит для таблиці oz з використанням функції avg(), що обчислює середню оцінку для кожного студента з кодом ks і результат запиту:

Таблиця oz	Запит	Результат

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

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

Приклад запиту для таблиці grypa з одним і кількома полями групування наведено нижче:

Аргументом функції COUNT() є ”*”. Вона повертає кількість записів в таблиці. Коли підсумкова операція полягає у визначенні кількості записів таблиці, то аргументом функції COUNT() є ”*”, бо кількість записів в таблиці не залежить від того, з яким стовпцем ми її підраховуємо..

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

Групування даних за допомогою виразу GROUP BY. Можливість групування результатів запитів має важливе значення. Група – це набір записів з однаковими значеннями певного поля. Під групуванням розуміють процес об’єднання групи записів в один. Поле, значення якого однакові, називається полем групування. Для групування записів в таблиці використовується параметр GROUP BY в операторі SELECT.

При використанні параметра GROUP BY всі записи в заданому полі, які мають ідентичні значення, об'єднуються в один елемент. Наприклад, однакові імена в наборі даних будуть занесені лише один раз. Параметр GROUP BY є уточнюючим і не обов'язковим. Цей параметр не є обов'язковим. Наприклад, для таблиці oz наведений запит для одержання вибірки наявних оцінок дасть результат:

Таблиця oz	Запит	Результат

Використання групування є корисним, якщо до груп застосувати певні підсумкові операції. З групуванням зв’язані і групові функції.

Функція для підрахунку рядків. Процедура підрахунку кількості рядків в таблиці здійснюється функцією COUNT(). Вона порахує кількість непустих значень. Запит для визначення числа записів має вигляд:

mysql> SELECT COUNT(*) FROM ім’я таблиці;

При допомозі функції COUNT() можна взнати, по скільки є однакових значень в заданому полі:

mysql> SELECT name, COUNT(*) FROM А GROUP BY name;

+--------+----------+

| name | COUNT(*) |

+--------+----------+

| Benny | 2 |

| Diane | 2 |

+--------+----------+

Команди COUNT() і GROUP BY допомагають характеризувати дані різними способами.

Функція для знаходження максимального і мінімального значень стовпця. Для знаходження максимального значення стовпця використовується функція MAX(), аргументом якої є числове поле. Приклад використання функції MAX():

SELECT MAX(числове поле) AS max FROM А

Для визначення номеру рядка, в якому досягається максимум заданого поля, використовуються запити в два етапи.

Одержати максимальне значення з таблиці при допомозі оператора SELECT.

Запит	Результат

- Використовуючи це значення, необхідно скласти запит:

Запит	Результат

Можна відсортувати всі рядки по спаданню числового поля і після цього одержати перший рядок, використовуючи оператор LIMIT:

Якщо існує декілька максимальних значень, то запит, що використовує LIMIT, повертає лише одно з них.

Запити з використанням агрегатних функцій є умовні та безумовні.

Приклад безумовного запиту: Знайти кількість рядків таблиці Дисципліна

SELECT COUNT(*)

FROM ^: Дисципліна

Приклад умовного запиту: Знайти кількість рядків таблиці Дисципліна, код дисципліна яких менший 5

SELECT COUNT(*) FROM Дисципліна WHERE Код_дисципліни< 5

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

AVG(SUM(…)) - помилковий запис;

AVG(а+в) - дозволений запис;

SUM(фонд)/COUNT(*)- дозволений запис;

Запит. Визначити кількість студентів груп ІТ-220 і ІТ-210

SELECT Sum(Група.ЧислоСтудентів) AS skmrscnm

FROM Група

where НазваГрупи=" ТО-220" OR НазваГрупи=" ТО-21"

Запит. Визначити середнє арифметичне кількості студентів груп ІТ-220 і ІТ-210

SELECT AVG (Група.ЧислоСтудентів) AS skmrscnm

FROM Група

where НазваГрупи=" ТО-220" OR НазваГрупи=" ТО-21"

Тут MIN() і MAX()можуть приймати рядок або число як аргумент. Ці функції не можуть використовуватися у виразі, хоча їхній параметр може бути виразом:

ПРИКЛАД: " SUM(value/10)" нормально, але від " SUM(value)/10" уже немає!