Пошук уроків, статей та іншого контенту
Закріпите навички, складаючи комплексні запити з фільтрацією, сортуванням, умовами, CASE та агрегуванням.
У цьому уроці ви потренуєтеся складати запити, які поєднують:
фільтрацію рядків через WHERE;
логічні умови AND, OR, NOT;
перевірку діапазонів, списків і тексту;
сортування через ORDER BY;
умовне обчислення за допомогою CASE;
агрегатні функції COUNT, SUM, AVG, MIN, MAX;
групування через GROUP BY;
фільтрацію груп через HAVING.
Для прикладів використаємо дані про клієнтів та їхні замовлення.
Виконайте цей скрипт у PostgreSQL:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
ordered_at DATE NOT NULL,
status TEXT NOT NULL CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0)
);
INSERT INTO customers (full_name, email, city)
VALUES
('Анна Коваль', 'anna@example.com', 'Київ'),
('Богдан Мельник', 'bohdan@example.com', 'Львів'),
('Олена Шевченко', 'olena@example.com', 'Одеса'),
('Дмитро Бондар', 'dmytro@example.com', 'Київ'),
('Марія Ткаченко', 'maria@example.com', 'Харків'),
('Ігор Романюк', 'ihor@example.com', 'Львів');
INSERT INTO orders (customer_id, ordered_at, status, total_amount)
VALUES
(1, '2025-01-05', 'paid', 1250.00),
(1, '2025-01-18', 'shipped', 3400.00),
(2, '2025-01-10', 'cancelled', 800.00),
(2, '2025-02-02', 'paid', 2100.00),
(3, '2025-02-11', 'new', 560.00),
(3, '2025-02-20', 'paid', 4800.00),
(4, '2025-03-01', 'shipped', 1750.00),
(4, '2025-03-15', 'cancelled', 900.00),
(5, '2025-03-22', 'paid', 3200.00),
(6, '2025-04-03', 'new', 450.00);У таблиці orders кожен рядок — це окреме замовлення. Поле customer_id посилається на клієнта, який створив замовлення.
Щоб отримати замовлення зі статусом paid, використовуйте WHERE:
SELECT id, customer_id, ordered_at, total_amount
FROM orders
WHERE status = 'paid';Умова після WHERE має повертати логічне значення: TRUE, FALSE або NULL.
Можна порівнювати числа та дати:
SELECT id, ordered_at, status, total_amount
FROM orders
WHERE total_amount >= 2000
AND ordered_at >= DATE '2025-02-01';Цей запит вибирає замовлення вартістю від 2000 і створені не раніше 1 лютого 2025 року.
Оператор AND вимагає виконання всіх умов:
SELECT *
FROM orders
WHERE status = 'paid'
AND total_amount > 3000;Оператор OR достатньо виконання хоча б однієї умови:
SELECT *
FROM orders
WHERE status = 'new'
OR status = 'shipped';Оператор NOT заперечує умову:
SELECT *
FROM orders
WHERE NOT status = 'cancelled';Для перевірки кількох варіантів одного поля зручніше використовувати IN:
SELECT *
FROM orders
WHERE status IN ('new', 'paid', 'shipped');Такий запис еквівалентний кільком перевіркам через OR.
У SQL оператор AND має вищий пріоритет, ніж OR. Тому складні умови потрібно групувати дужками.
Наприклад, знайдемо оплачені або відправлені замовлення вартістю понад 2000:
SELECT id, status, total_amount
FROM orders
WHERE (status = 'paid' OR status = 'shipped')
AND total_amount > 2000;Без дужок запит міг би інтерпретуватися інакше:
-- Це інша логіка:
SELECT id, status, total_amount
FROM orders
WHERE status = 'paid'
OR status = 'shipped'
AND total_amount > 2000;Фактично цей варіант означає:
status = 'paid'
АБО
(status = 'shipped' і total_amount > 2000)BETWEENОператор BETWEEN перевіряє, чи входить значення до діапазону. Межі включаються:
SELECT id, ordered_at, total_amount
FROM orders
WHERE total_amount BETWEEN 1000 AND 3000;Цей запит поверне замовлення від 1000 до 3000 включно.
Для дат:
SELECT id, ordered_at, status
FROM orders
WHERE ordered_at BETWEEN DATE '2025-01-01' AND DATE '2025-02-28';Для пошуку за шаблоном використовують LIKE. Символ % означає будь-яку кількість символів:
SELECT id, full_name, city
FROM customers
WHERE full_name LIKE 'А%';Цей запит знайде імена, які починаються з літери А.
У PostgreSQL оператор ILIKE виконує пошук без врахування регістру:
SELECT id, full_name, city
FROM customers
WHERE full_name ILIKE '%енко%';Запит знайде імена, у яких є фрагмент енко.
За замовчуванням PostgreSQL не гарантує порядок рядків без ORDER BY.
Сортування за зростанням:
SELECT id, ordered_at, total_amount
FROM orders
ORDER BY total_amount ASC;ASC можна не вказувати, оскільки це порядок за замовчуванням:
SELECT id, ordered_at, total_amount
FROM orders
ORDER BY total_amount;Сортування за спаданням:
SELECT id, ordered_at, total_amount
FROM orders
ORDER BY total_amount DESC;Можна сортувати за кількома полями:
SELECT id, status, ordered_at, total_amount
FROM orders
ORDER BY status ASC, total_amount DESC;Спочатку рядки сортуються за статусом, а всередині кожного статусу — за сумою від найбільшої до найменшої.
Також можна сортувати за позицією стовпця у SELECT, але краще використовувати назву стовпця:
SELECT id, status, total_amount
FROM orders
ORDER BY total_amount DESC;CASECASE дає змогу створити значення залежно від умови. Наприклад, класифікуємо замовлення за сумою:
SELECT
id,
total_amount,
CASE
WHEN total_amount >= 3000 THEN 'велике'
WHEN total_amount >= 1000 THEN 'середнє'
ELSE 'маленьке'
END AS order_size
FROM orders;Умови перевіряються зверху вниз. Після першої умови, яка є істинною, PostgreSQL повертає відповідний результат.
CASE можна використовувати в сортуванні:
SELECT id, status, total_amount
FROM orders
ORDER BY
CASE
WHEN status = 'paid' THEN 1
WHEN status = 'shipped' THEN 2
WHEN status = 'new' THEN 3
ELSE 4
END,
ordered_at DESC;У цьому прикладі оплачені замовлення будуть першими, потім відправлені, нові та скасовані. Додаткове сортування за датою застосовується всередині кожної групи.
CASE також можна поєднати з арифметичними операціями:
SELECT
id,
total_amount,
CASE
WHEN status = 'cancelled' THEN 0
ELSE total_amount * 0.9
END AS amount_after_discount
FROM orders;Для скасованих замовлень результатом буде 0, для інших — сума зі знижкою 10%.
Агрегатні функції обробляють кілька рядків і повертають одне значення.
Основні агрегатні функції:
COUNT — кількість рядків або значень;
SUM — сума;
AVG — середнє значення;
MIN — мінімальне значення;
MAX — максимальне значення.
Отримаємо загальну статистику за всіма замовленнями:
SELECT
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS average_order,
MIN(total_amount) AS minimum_order,
MAX(total_amount) AS maximum_order
FROM orders;Агрегатні функції можна застосовувати після фільтрації:
SELECT
COUNT(*) AS paid_order_count,
SUM(total_amount) AS paid_revenue
FROM orders
WHERE status = 'paid';Спочатку PostgreSQL залишить лише оплачені замовлення, а потім порахує статистику для них.
GROUP BYЯкщо потрібно отримати статистику окремо для кожної категорії, використовуйте GROUP BY.
Кількість і загальна сума замовлень для кожного статусу:
SELECT
status,
COUNT(*) AS order_count,
SUM(total_amount) AS total_amount
FROM orders
GROUP BY status
ORDER BY total_amount DESC;Усі стовпці в SELECT, які не є агрегатними функціями, мають бути вказані в GROUP BY.
Статистика за містами клієнтів потребує з'єднання таблиць:
SELECT
c.city,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.city
ORDER BY total_amount DESC;JOIN поєднує клієнтів із їхніми замовленнями через однакові значення customer_id.
Статистика для кожного клієнта:
SELECT
c.full_name,
c.city,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent,
AVG(o.total_amount) AS average_order
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.full_name, c.city
ORDER BY total_spent DESC;Групувати за c.id важливо, оскільки ім'я клієнта теоретично може змінитися або бути неунікальним. Ідентифікатор однозначно визначає групу.
HAVINGWHERE фільтрує окремі рядки до групування. HAVING фільтрує вже сформовані групи.
Наприклад, знайдемо клієнтів, які витратили понад 3000:
SELECT
c.full_name,
SUM(o.total_amount) AS total_spent
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.full_name
HAVING SUM(o.total_amount) > 3000
ORDER BY total_spent DESC;Не можна замінити цю умову на WHERE SUM(o.total_amount) > 3000, оскільки SUM обчислюється після формування груп.
WHERE і HAVING можна використовувати разом:
SELECT
c.full_name,
COUNT(o.id) AS paid_order_count,
SUM(o.total_amount) AS paid_total
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.full_name
HAVING SUM(o.total_amount) >= 2000
ORDER BY paid_total DESC;Логіка запиту:
залишаються лише замовлення зі статусом paid;
вони групуються за клієнтом;
для кожного клієнта обчислюється кількість і сума;
залишаються групи із сумою від 2000.
Тепер поєднаємо фільтрацію, CASE, агрегування, GROUP BY, HAVING і сортування.
Потрібно отримати клієнтів із Києва або Львова, врахувати лише оплачені та відправлені замовлення, визначити рівень витрат і залишити лише тих, хто має щонайменше одне таке замовлення:
SELECT
c.full_name,
c.city,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent,
CASE
WHEN SUM(o.total_amount) >= 5000 THEN 'VIP'
WHEN SUM(o.total_amount) >= 2000 THEN 'постійний'
ELSE 'новий'
END AS customer_level
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE c.city IN ('Київ', 'Львів')
AND o.status IN ('paid', 'shipped')
GROUP BY c.id, c.full_name, c.city
HAVING COUNT(o.id) >= 1
ORDER BY total_spent DESC;Зверніть увагу на порядок роботи:
JOIN поєднує таблиці;
WHERE відсіює непотрібні замовлення та міста;
GROUP BY створює групу для кожного клієнта;
COUNT і SUM обчислюють статистику;
HAVING фільтрує групи;
CASE визначає рівень клієнта;
ORDER BY сортує кінцевий результат.
NULL у фільтрахNULL означає відсутнє або невідоме значення. Перевірка через = для NULL не працює:
-- Неправильно для пошуку NULL:
SELECT *
FROM customers
WHERE email = NULL;Для цього використовують IS NULL або IS NOT NULL:
SELECT *
FROM customers
WHERE email IS NOT NULL;У наших даних поле email має обмеження NOT NULL, тому цей запит поверне всіх клієнтів.
Агрегатні функції, крім COUNT(*), зазвичай ігнорують NULL. Якщо потрібно замінити NULL на інше значення, використовуйте COALESCE:
SELECT
c.full_name,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.full_name
ORDER BY total_spent DESC;LEFT JOIN залишає в результаті навіть клієнтів без замовлень, а COALESCE перетворює відсутню суму на 0.
WHERE замість HAVINGНеправильно:
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
WHERE SUM(total_amount) > 3000
GROUP BY customer_id;Агрегатні умови потрібно писати в HAVING:
SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 3000;Неправильно або неоднозначно:
SELECT *
FROM orders
WHERE status = 'paid'
OR status = 'shipped'
AND total_amount > 2000;Краще явно вказати бажану логіку:
SELECT *
FROM orders
WHERE (status = 'paid' OR status = 'shipped')
AND total_amount > 2000;GROUP BYНеправильно:
SELECT customer_id, status, SUM(total_amount)
FROM orders
GROUP BY customer_id;Для одного клієнта можуть існувати різні статуси, тому PostgreSQL не знає, який status показати.
Потрібно або додати поле до групування:
SELECT customer_id, status, SUM(total_amount)
FROM orders
GROUP BY customer_id, status;або прибрати його з результату.
ORDER BYНеправильно покладатися на порядок, у якому рядки зберігаються в таблиці:
SELECT *
FROM orders;Якщо порядок важливий, завжди вказуйте його явно:
SELECT *
FROM orders
ORDER BY ordered_at DESC, id DESC;NULL через =Неправильно:
WHERE email = NULLПравильно:
WHERE email IS NULLЗнайдіть усі замовлення зі статусом paid або shipped, вартість яких перевищує 1500. Відсортуйте їх за датою від найновішого до найстарішого.
Виведіть усіх клієнтів із Києва або Львова, чиє ім'я містить літеру а без врахування регістру.
Для кожного статусу замовлень порахуйте:
кількість замовлень;
середню суму;
найбільшу суму.
Знайдіть міста, у яких загальна сума замовлень перевищує 3000.
Для кожного клієнта порахуйте суму оплачених замовлень і класифікуйте клієнтів:
VIP, якщо сума не менша за 4000;
звичайний, якщо сума не менша за 1500;
новий в інших випадках.
Приклад розв'язання п'ятого завдання:
SELECT
c.full_name,
c.city,
SUM(o.total_amount) AS paid_total,
CASE
WHEN SUM(o.total_amount) >= 4000 THEN 'VIP'
WHEN SUM(o.total_amount) >= 1500 THEN 'звичайний'
ELSE 'новий'
END AS customer_type
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.full_name, c.city
ORDER BY paid_total DESC;WHERE фільтрує окремі рядки.
AND, OR, NOT дають змогу будувати складні умови.
IN зручно використовувати для перевірки списку значень.
BETWEEN перевіряє входження до діапазону, включно з його межами.
LIKE і ILIKE використовують для пошуку тексту за шаблоном.
ORDER BY сортує результат за одним або кількома полями.
CASE створює значення залежно від умов.
GROUP BY об'єднує рядки в групи.
WHERE застосовується до групування, а HAVING — після нього.
Агрегатні функції COUNT, SUM, AVG, MIN і MAX допомагають отримувати статистику.
Для перевірки NULL потрібно використовувати IS NULL або IS NOT NULL.