Агрегатные функции и группировка данных

У SQL існує ряд агрегатних функцій, кожна з яких оперує сукупністю значень стовпця деякої таблиці і створює єдине значення. Стандартом передбачені наступні агрегатні функції. Функція COUNT (*) повертає кількість рядків джерела даних. Функція COUNT (<ім'я поля>) повертає кількість значень у вказаному стовпці. Функція SUM (<ім'я поля>) повертає суму значень у вказаному стовпці. Функція AVG (<ім'я поля>) повертає середнє значення у вказаному стовпці. Функція MIN (<ім'я поля>) повертає мінімальне значення у вказаному стовпці. Функція MAX (<ім'я поля>) повертає максимальне значення у вказаному стовпці. Функції COUNT, MIN і MAX застосовні до будь-яких типів даних, SUM і AVG застосовуються тільки для числових полів. Розглянемо більш докладно використання кожної з цих функцій. Пошук максимального і мінімального значень у вибірці даних здійснюється за допомогою агрегатних функцій MAX і MIN: SELECT MIN (price) AS Min_price, MAX (price) AS Max_price FROM PC. Для отримання кількості рядків в горизонтальній вибірці даних використовується функція COUNT: SELECT COUNT (*) AS Qty FROM PC WHERE cd IN ('50x', '52x') Для того, щоб при отриманні результату агрегатних функцій використовувалися тільки унікальні значення, при їх аргументі можна також використовувати параметр DISTINCT: SELECT COUNT (DISTINCT cd) AS Qty FROM PC WHERE cd IN ('50x', '52x') Пошук середнього значення у вибірці даних здійснюється за допомогою агрегатної функції AVG: SELECT AVG (hd) AS Avg_hd FROM PC WHERE cd = '50x' Результати відповідних запитів наведено на рис. 2.3

Рис. 2.3 - Результати виконання агрегатних функцій: а) пошук мінімуму і максимуму, б) знаходження кількості записів, в) пошук кількості унікальних записів, г) знаходження середнього значення. Існує кілька загальних правил виконання агрегатних функцій, дотримання яких дозволить уникнути багатьох помилок при розробці запитів: 1) якщо в результаті виконання запиту не отримано жодного рядка, то вихідні дані для обчислення будь-який з агрегатних функцій відсутні. У цьому випадку результатом виконання функцій COUNT буде нуль, а результатом усіх інших функцій - NULL; 2) аргумент агрегатної функції не може сам утримувати агрегатні функції; 3) результатом виконання функції COUNT є ціле число. Інші агрегатні функції успадковують типи даних оброблюваних значень; 4) якщо при виконанні функції SUM був отриманий результат, що перевищує максимальне значення використовуваного типу даних, виникає помилка. Значно збільшує цінність запитів можливість виконання операції угруповання даних, яка дозволяє виконувати зрізи інформації в різних аспектах, це тим самим надає додатковий сервіс користувачам комерційних баз даних. Пропозиція GROUP BY використовується для визначення груп вихідних рядків, до яких можуть застосовуватися агрегатні функції. Якщо ця пропозиція відсутня, і використовуються агрегатні функції, то всі стовпці з іменами, згаданими в SELECT, повинні бути включені в агрегатні функції. Інакше всі стовпці списку SELECT, що не увійшли до агрегатні функції, повинні бути зазначені в пропозиції GROUP BY. Наведемо приклад використання угруповання даних:

SELECT ram, COUNT (ram) AS Qty_ram, AVG (price) AS Avg_price FROM PC GROUP BY ram Результат виконання даного запиту наведено на рис. 2.4

рис. 2.4- Результат виконання даного запиту

Функціональність угруповання даних розширюється за рахунок застосування пропозиції HAVING, яке використовується після угруповання для визначення предиката, фільтруючого групи за значеннями агрегатних функцій. Ця пропозиція необхідно для перевірки значень, які отримані за допомогою агрегатної функції: SELECT ram, AVG (price) AS Avg_price FROM PC GROUP BY ram HAVING ram IN (256, 512)

У пропозиції HAVING не можна застосовувати псевдонім (Avg_price), використовуваний для іменування значень агрегатної функції: SELECT ram, COUNT (ram) AS Qty_ram, AVG (price) AS Avg_price FROM PC GROUP BY ram HAVING AVG (price)> 700 Результати виконання групування даних з визначенням предиката представлені на рис. 2.5

Рис. 2.5 Результати виконання групування даних з визначенням предиката: а) простий предикат, б) складний предикат [2;стр.59-61,66-68]

Розділ 3: БД Архітектурна компанія