SQL-. Групування даних (GROUP BY)

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

1. Створення груп (GROUP BY)

Групи створюються за допомогою інструкції GROUP BY оператора SELECT. Розглянемо на прикладі.

Run SQLSELECT Product, SUM(Quantity) AS Product_num 
FROM Sumproduct 
GROUP BY Product

Даним запитом ми витягнули інформацію щодо кількості реалізованої продукції в кожному місяці. Оператор SELECT наказує вивести два стовпця Product - назва продукту та Product_num - розрахункове поле, яке ми створили для відображення кількості реалізованої продукції (формула поля SUM(Quantity)). Речення GROUP BY вказує СУБД згрупувати дані по стовпцю Product.

Варто також зазначити, що GROUP BY має іти після речення WHERE та перед ORDER BY.

2. Фільтруючі групи (HAVING)

Так само, як ми фільтрували рядки в таблиці, ми можемо здійснювати фільтрацію по згрупованим даним. Для цього в SQL існуює оператор HAVING. Візьмемо попередній приклад та додамо фільтрацію по групам.

Run SQLSELECT Product, SUM(Quantity) AS Product_num 
FROM Sumproduct 
GROUP BY Product 
HAVING SUM(Quantity) > 4000

Бачимо, що після того, як була порахована кількість реалізованого товару в розрізі кожного продукту, СУБД "відсікла" ті продукти, яких було реалізовано менше 4000 шт.

Як бачимо, оператор HAVING дуже подібний до оператора WHERE, проте між собою вони мають суттєву відмінність: WHERE фільтрує дані до того, як вони будуть згруповані, а HAVING - здійснює фільтрацію після групування. Таким чином, рядки, які були вилучені реченям WHERE не будуть включені в групу. Отож, оператори WHERE та HAVING можуть використовуватись в одному реченні. Розглянемо приклад:

Run SQLSELECT Product, SUM(Quantity) AS Product_num 
FROM Sumproduct 
WHERE Product <> 'Skis Long'GROUP BY Product 
HAVING SUM(Quantity) > 4000

Ми до попереднього прикладу добавили оператор WHERE, де вказали товар "Skis Long", що в свою чергу вплинуло на групування оператором HAVING. Як результат бачимо, що товар "Skis Long" не попав в перелік груп з кількістю реалізованої продукції більше 4000 шт.

3. Групування та сортування

Як і при звичайній вибірці даних, ми можемо посортувати групи після групування оператором HAVING. Для цього ми можемо використати вже знайомий нам оператор ORDER BY. В даній ситуації його застосування аналогічне попереднім прикладам. Наприклад:

Run SQLSELECT Product, SUM(Quantity) AS Product_num 
FROM Sumproduct 
GROUP BY Product 
HAVING SUM(Quantity) > 3000ORDER BY SUM(Quantity)

або просто вкажемо номер поля по порядку, по якому хочемо посортувати:

Run SQLSELECT Product, SUM(Quantity) AS Product_num 
FROM Sumproduct 
GROUP BY Product 
HAVING SUM(Quantity) > 3000ORDER BY 2

Фільтрування за допомогою підзапитів

Таблиці баз даних, які використовуються в СУБД Access являються реляційними таблицями, тобто всі таблиці можна пов'язати між собою по спільним полям. Припустимо в нас зберігаються дані в двох різних таблицях і нам потрібно вибрати дані в одній із них, в залежності від того, які дані в іншій. Для цього створимо ще одну таблицю в нашій базі даних. Це буде, наприклад, таблиця Sellers з інформацією про постачальників:

Тепер ми маємо дві таблиці - Sumproduct та Sellers, які мають однакове поле City. Припустимо, нам потрібно порахувати скільки товарів було продано лише в Канаді. Зробити це нам допоможуть підзапити. Отже, спочатку напишемо запит для вибірки міст, які знаходяться в Канаді:

Run SQLSELECT City 
FROM Sellers 
WHERE Country = 'Canada'

Тепер передамо ці дані в наступний запит, який вибиратиме дані з таблиці Sumproduct:

Run SQLSELECT SUM(Quantity) AS Qty_Canada 
FROM Sumproduct 
WHERE City IN ('Montreal','Toronto')

Також ми можемо об'єднати ці два запити в один. Таким чином, один запит, який виводить дані буде головним, а другий запит, який передає вхідні дані, буде допоміжним (підзапитом). Для вкладення підзапиту використаємо конструкцію WHERE ... IN (...), про яку говорилось в розділі Розширене фільтрування:

Run SQLSELECT SUM(Quantity) AS Qty_Canada 
FROM Sumproduct 
WHERE City IN (SELECT City 
 FROM Sellers 
 WHERE Country = 'Canada')

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

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

Run SQLSELECT Seller_name, (SELECT SUM(Quantity) 
 FROM Sumproduct 
 WHERE Sellers.City = Sumproduct.City) AS Qty 
FROM Sellers

Перший оператор SELECT відображає два стовпця - Seller_name та Qty. Поле Qty являється розрахунковим, воно формується в результаті виконання підзапиту, який взятий в круглі дужки. Цей підзапит виконується по одному разу для кожного запису в полі Seller_name та взагальному буде виконаний чотири рази, оскільки вибрано імена чотирьох продавців.

Також, в підзапиті, речення WHERE виконує функцію поєднання, оскільки за допомогою WHERE ми з'єднали дві таблиці по полю City, використавши повні назви стовпців (Таблиця.Поле).

Бачимо, що для сортування зведених результатів нам потрібно просто прописати речення з ORDER BY після оператора HAVING.

MS Access не підтримує сортування груп за псевдонімами колонок, тобто в нашому прикладі, щоб посортувати значення, ми не зможемо в кінці запиту прописати ORDER BY Product_num.

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

Підзапит – це оператор SELECT, вкладений у інший SELECT, SELECT...INTO, INSERT...INTO, DELETE, UPDATE або всередині іншого підзапиту.

Текст підзапиту має бути розміщений у дужках. Часто підзапити використовують замість поєднання таблиць (JOIN).