Главная | Обратная связь | Поможем написать вашу работу!

Вибірка даних з угрупованням

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

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

Таблица 14.1. Агрегатні функції

Функція	Результат
COUNT	Кількість рядків не порожніх значень чи нулів, що вибрав запит
SUM	Сума всіх обраних значень даного поля
AVG	Середньо арифметичне значення всіх обраних значень даного поля
MIN	Найменше з всіх обраних значень даного поля
MAX	Найбільше з всіх обраних значень даного поля

Агрегатні функції використовуються подібно іменам полів в операторі SELECT, але з одним виключенням: вони беруть ім'я поля як аргумент. З функціями SUM і AVG можуть використовуватися тільки числові поля. З функціями COUNT, MAX і MІN можуть використовуватися як числові, так і символьні поля. При використанні із символьними полями МАХ і MІN будуть транслювати їх в еквівалент ASCІІ коду й обробляти за абеткою. Неможливе угруповання по полях вибраних за допомогою символу *. В операторі GROUP BY треба перелічувати поля указані в SELECT, але в порядку угруповання.

Запит: Згрупувати дані таблиці ТаблСтудент по дисциплінах
SELECT ТаблСтудент.ПІБ, ТаблСтудент.Дисципліна, ТаблСтудент.Оцінка FROM ТаблСтудент GROUP BY ТаблСтудент.Дисципліна, ТаблСтудент.ПІБ, ТаблСтудент.Оцінка;	Результат	ПІБ	Дисципліна	Оцінка
Группа 1	Владиміров В. А.	Англ. мова
Крилова Т. С.	Англ. мова
Міронов А. В.	Англ. мова
Петров Ф. І.	Англ. мова
Сідоров К. А	Англ. мова
Стєпанова К. Е.	Англ. мова
Группа 2	Владиміров В. А.	Бази даних
Крилова Т. С.	Бази даних
Міронов А. В.	Бази даних
Петров Ф. І.	Бази даних
Сідоров К. А	Бази даних
Стєпанова К. Е.	Бази даних
Группа 3	Владиміров В. А.	Інформатика
Крилова Т. С.	Інформатика
Міронов А. В.	Інформатика
Петров Ф. І.	Інформатика
Сідоров К. А	Інформатика
Стєпанова К. Е.	Інформатика
Группа 4	Іванова Е. А.	Мережі
Трофімов П. А.	Мережі
Уткіна Н. В.	Мережі

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

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

SELECT ТаблСтудент.Дисципліна, COUNT(*) AS [Кількість студентів] FROM ТаблСтудент GROUP BY ТаблСтудент.Дисципліна;

Дисципліна	Кількість студентів
Англійська мова
Бази даних
Інформатика
Мережі

Результат:

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

Запит: підрахувати кількість студентів, що одержали оцінки по кожній дисципліні

SELECT [ТаблСтудент].[Дисципліна], COUNT(*) AS [Кількість студентів] FROM ТаблСтудент WHERE [ТаблСтудент].[Оцінка] Is Not Null GROUP BY [ТаблСтудент].[Дисципліна];

Результат:

Дисциплина	Кількість студентів
Англійська мова
Бази даних
Інформатика
Мережі

Сідоров К. А	Англійськамова
Міронов А. В.	Інформатика

У цьому випадку рядки зі студентами що мають невизначені оцінкине потрапить у набір кортежів перед угрупованням.

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

Запит: знайти кількість позитивних оцінок по всіх предметах.
SELECT COUNT(*) FROM ТаблСтудент WHERE Оцінка > 2;	Результат:

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

Запит: визначити для кожної дисципліни кількість студентів що здали іспит успішно і середній бал по дисципліні.

SELECT ТаблСтудент.Дисципліна, Count(*) AS [Кількість оцінок], Avg(ТаблСтудент.Оцінка) AS [Средній бал] FROM ТаблСтудент WHERE ((ТаблСтудент.Оцінка) Is Not Null And (ТаблСтудент.Оцінка)>2) GROUP BY ТаблСтудент.Дисципліна;

Результат:

Дисципліна	Кількість оцінок оценок	Средній бал
Англійська мова		3,8
Бази даних		4,75
Інформатика		4,50
Мережі		4,67

Тут в операторі в квадратних дужках зазначені імена нових стовпців, котрі є у відповідній таблиці, але немає у вихідній таблиці. У запиті відбувається вибірка записів, якщо в поле Оцінка значення нерівне Null чи >2, потім усі записи групуються по полю дисципліна, для кожної групи обчислюється кількість записів і середнє значення поля Оцінка для групи.

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

SELECT ТаблСтудент.ПІБ, Count(*) AS [Кількість оцінок], AVG(ТаблСтудент.Оцінка) AS [Ср бал] FROM ТаблСтудент WHERE ТаблСтудент.Оцінка IS NOT NULL GROUP BY ТаблСтудент.ПІБ;

Результат:

ПІБ	Кількість оцінок	Ср бал
Владіміров В. А.		4,33
Іванова Е. А.
Крилова Т. С.		4,67
Міронов А. В.		2,5
Петров Ф. І.
Сідоров К. А.
Стєпанова К. Е.		2,33
Трофімов П. А.
Уткіна Н. В.

Ще приклад на угруповання й обчислення середнього значення.

OEDER BY може використовуватися з GROUP BY для упорядкування груп, при цьому ORDER BY повинний бути останнім.

Запит: знайти кількість різних оцінок для кожного студента.

SELECT DISTINCT ПІБ, COUNT (*) AS [Кількість], Оцінка FROM ТаблСтудент GROUP BY Оцінка, ПІБ ORDER BY ПІБ DESC;

Результат:

ПІБ	Кількість	Оцінка
Уткіна Н. В.
Трофімов П. А.
Стєпанова К. Е.
Стєпанова К. Е.
Сідоров К. А.
Сідоров К. А.
Петров Ф. І.
Міронов А. В.
Міронов А. В.
Міронов А. В.
Крилова Т. С.
Крилова Т. С.
Іванова Е. А.
Владіміров В. А.
Владіміров В. А.

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

⇐ Предыдущая 123 Следующая ⇒