Пошук уроків, статей та іншого контенту
Групуватимете рядки для агрегування даних і фільтруватимете сформовані групи через HAVING.
GROUP BY об’єднує рядки з однаковими значеннями в одну групу. Зазвичай його використовують разом з агрегатними функціями:
COUNT() — кількість рядків;
SUM() — сума значень;
AVG() — середнє значення;
MIN() — мінімальне значення;
MAX() — максимальне значення.
Наприклад, уявімо таблицю orders:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
status text NOT NULL,
amount numeric(10, 2) NOT NULL,
created_at date NOT NULL
);
INSERT INTO orders (customer_id, status, amount, created_at)
VALUES
(1, 'paid', 120.00, '2025-01-10'),
(1, 'paid', 80.00, '2025-01-15'),
(1, 'pending', 50.00, '2025-01-20'),
(2, 'paid', 200.00, '2025-01-11'),
(2, 'cancelled', 30.00, '2025-01-12'),
(3, 'paid', 75.00, '2025-01-13');Щоб отримати загальну суму замовлень для кожного клієнта, використаємо GROUP BY:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;Результат:
customer_id | total_amount
-------------+--------------
1 | 250.00
2 | 230.00
3 | 75.00У цьому запиті:
PostgreSQL розподіляє рядки за значенням customer_id.
Для кожної групи обчислює SUM(amount).
Повертає один рядок на кожного клієнта.
До кожної групи можна застосувати одну або кілька агрегатних функцій:
SELECT
customer_id,
COUNT(*) AS orders_count,
SUM(amount) AS total_amount,
AVG(amount) AS average_amount,
MIN(amount) AS minimum_amount,
MAX(amount) AS maximum_amount
FROM orders
GROUP BY customer_id;COUNT(*) рахує всі рядки групи. На відміну від COUNT(column), він не ігнорує рядки через значення NULL.
Для конкретного клієнта результат може виглядати так:
customer_id | orders_count | total_amount | average_amount | minimum_amount | maximum_amount
-------------+--------------+--------------+----------------+----------------+---------------
1 | 3 | 250.00 | 83.33 | 50.00 | 120.00COUNT(*) і COUNT(column)Різниця помітна, коли стовпець містить NULL:
SELECT
COUNT(*) AS all_rows,
COUNT(status) AS rows_with_status
FROM orders;COUNT(*) рахує всі рядки;
COUNT(status) рахує лише рядки, де status не дорівнює NULL.
Якщо запит використовує GROUP BY, кожен стовпець у SELECT, який не є аргументом агрегатної функції, має бути вказаний у GROUP BY.
Коректний запит:
SELECT
customer_id,
status,
COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id, status;Тут PostgreSQL створює групи за комбінацією customer_id і status.
Наприклад:
customer_id | status | orders_count
-------------+-----------+--------------
1 | paid | 2
1 | pending | 1
2 | paid | 1
2 | cancelled | 1
3 | paid | 1Некоректний варіант:
SELECT
customer_id,
status,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;Стовпець status не входить до GROUP BY і не обчислюється агрегатною функцією. Для одного клієнта можуть існувати різні статуси, тому PostgreSQL не знає, яке значення status повернути.
Правильний варіант:
SELECT
customer_id,
status,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, status;Коли в GROUP BY вказано кілька стовпців, група формується для кожної унікальної комбінації їхніх значень:
SELECT
status,
created_at,
COUNT(*) AS orders_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY status, created_at
ORDER BY created_at, status;Цей запит окремо підраховує замовлення для кожної пари:
status;
created_at.
Порядок стовпців у GROUP BY не змінює самі групи, але зрозумілий порядок часто покращує читабельність запиту.
HAVING фільтрує вже сформовані групи. Його використовують для умов на агреговані значення.
Наприклад, знайдемо клієнтів, у яких загальна сума замовлень перевищує 100:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;Результат:
customer_id | total_amount
-------------+--------------
1 | 250.00
2 | 230.00Клієнт із customer_id = 3 не потрапляє до результату, оскільки його загальна сума дорівнює 75.
Умова в HAVING застосовується до кожної групи після обчислення агрегатної функції.
Можна залишити лише групи, у яких є щонайменше два рядки:
SELECT
customer_id,
COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;Результат міститиме клієнтів 1 і 2, оскільки в кожного з них по два або більше замовлень.
Умова HAVING може містити логічні оператори:
SELECT
customer_id,
COUNT(*) AS orders_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
AND SUM(amount) > 200;Тут залишаться лише групи, які одночасно:
містять щонайменше два замовлення;
мають загальну суму понад 200.
WHERE та HAVING фільтрують дані на різних етапах.
WHERE фільтрує окремі рядки до групування.
HAVING фільтрує групи після групування.
Наприклад, знайдемо клієнтів, у яких сума лише оплачених замовлень перевищує 100:
SELECT
customer_id,
COUNT(*) AS paid_orders_count,
SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 100;Послідовність роботи запиту:
WHERE status = 'paid' залишає лише оплачені замовлення.
GROUP BY customer_id формує групи клієнтів.
COUNT(*) і SUM(amount) обчислюють значення для кожної групи.
HAVING SUM(amount) > 100 залишає потрібні групи.
Не слід використовувати HAVING для звичайного фільтрування рядків, якщо для цього підходить WHERE:
-- Краще так
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;Замість:
-- У цьому випадку HAVING використано не за призначенням
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING status = 'paid';Останній варіант також є некоректним: status не входить до GROUP BY і не є агрегованим значенням. Навіть якби умову було сформульовано через агрегатну функцію, фільтрування відбувалося б після групування, а не до нього.
У PostgreSQL псевдонім, оголошений у SELECT, зазвичай не можна використати в HAVING того самого рівня:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING total_amount > 100;Такий запит завершується помилкою, оскільки total_amount є псевдонімом результату SELECT, а не стовпцем, доступним у виразі HAVING.
Потрібно повторити агрегатний вираз:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;Для сортування згрупованого результату використовуйте ORDER BY:
SELECT
customer_id,
COUNT(*) AS orders_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100
ORDER BY total_amount DESC;Тут:
GROUP BY формує групи;
HAVING залишає клієнтів із сумою понад 100;
ORDER BY сортує результат за загальною сумою у спадаючому порядку.
У ORDER BY можна використовувати псевдонім total_amount, оголошений у SELECT.
GROUP BY об’єднує всі рядки зі значенням NULL в одну групу.
Розглянемо таблицю платежів:
CREATE TABLE payments (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
method text,
amount numeric(10, 2) NOT NULL
);
INSERT INTO payments (method, amount)
VALUES
('card', 100.00),
('card', 50.00),
('cash', 40.00),
(NULL, 25.00);Запит:
SELECT
method,
SUM(amount) AS total_amount
FROM payments
GROUP BY method
ORDER BY method NULLS LAST;Створить окрему групу для рядків, у яких method дорівнює NULL.
За потреби таку групу можна замінити зрозумілим текстом за допомогою COALESCE:
SELECT
COALESCE(method, 'unknown') AS payment_method,
SUM(amount) AS total_amount
FROM payments
GROUP BY COALESCE(method, 'unknown');Наступний запит знаходить статуси замовлень, для яких:
є щонайменше два замовлення;
загальна сума перевищує 100;
результат відсортовано за сумою:
SELECT
status,
COUNT(*) AS orders_count,
SUM(amount) AS total_amount,
ROUND(AVG(amount), 2) AS average_amount
FROM orders
GROUP BY status
HAVING COUNT(*) >= 2
AND SUM(amount) > 100
ORDER BY total_amount DESC;Для наведених даних статус paid має чотири замовлення та загальну суму 475, тому він пройде обидві умови. Інші статуси не відповідатимуть одночасно вимогам.
Некоректно:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
WHERE SUM(amount) > 100
GROUP BY customer_id;WHERE працює до групування, тому не може фільтрувати результат SUM(amount).
Правильно:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;Некоректно:
SELECT
customer_id,
status,
COUNT(*)
FROM orders
GROUP BY customer_id;Якщо status потрібно повернути в результаті, додайте його до GROUP BY або застосуйте агрегатну функцію.
Запит:
SELECT
customer_id,
SUM(amount)
FROM orders
WHERE amount > 100
GROUP BY customer_id;прибирає окремі замовлення дешевші за 100, а потім підсумовує ті, що залишилися.
Запит:
SELECT
customer_id,
SUM(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;спочатку підсумовує всі замовлення кожного клієнта, а потім залишає клієнтів із підсумком понад 100. Це різні операції.
GROUP BY не гарантує порядок результату. Якщо порядок важливий, використовуйте ORDER BY:
SELECT
customer_id,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
ORDER BY customer_id;GROUP BY об’єднує рядки з однаковими значеннями.
Агрегатні функції обчислюють значення для кожної групи.
Усі неагреговані стовпці з SELECT мають бути в GROUP BY.
WHERE фільтрує рядки до групування.
HAVING фільтрує сформовані групи.
Умови для COUNT(), SUM(), AVG() та інших агрегатів потрібно задавати через HAVING.
ORDER BY сортує готовий результат групування.