Практичний проєкт: створюємо маленьку базу даних для магазину

Повертаємось до PostgreSQL.

У попередній лекції ти вивчив індекси та базову продуктивність.

Ти дізнався:

• чому запити можуть ставати повільними;
• що таке індекси;
• як EXPLAIN ANALYZE допомагає зрозуміти запити;
• чому індекси корисні;
• чому індекси не безкоштовні;
• коли індекси можуть допомогти;
• коли індекси — це просто дорогі прикраси.

Дуже добре.

Тепер настав час скласти багато частин разом.

Сьогодні ми створимо маленький практичний проєкт на PostgreSQL.

Не фейковий приклад з однією таблицею.

Не загадкову базу з назвами типу table1 і data2.

Ні.

Сьогодні ми створимо маленьку базу даних для магазину.

Вона матиме:

customers
categories
products
orders
order_items

Це вже схоже на реальне проєктування бази даних.

Достатньо маленьке, щоб зрозуміти.

Достатньо реальне, щоб бути корисним.

Як тренувальний меч.

Але для SQL.

Менш небезпечний.

Зазвичай.

Що ти вивчиш

У цій лекції ти навчишся:

• проєктувати маленьку реляційну базу даних;
• створювати кілька пов’язаних таблиць;
• використовувати первинні ключі;
• використовувати зовнішні ключі;
• додавати реалістичні дані;
• читати дані за допомогою JOIN;
• обчислювати суми замовлень;
• рахувати замовлення кожного клієнта;
• обчислювати загальні витрати клієнта;
• обчислювати продажі по категоріях;
• використовувати агрегатні функції в практичному проєкті;
• створювати корисні індекси;
• думати як проєктувальник бази даних.

До кінця цієї лекції ти матимеш робочий міні-проєкт бази даних.

Не просто випадкові SQL-команди.

А реальну структуру.

Маленьку базоданну машину.

З клієнтами.

Продуктами.

Замовленнями.

І достатньою кількістю зв’язків, щоб PostgreSQL відчув себе важливим.

Ідея проєкту

Ми створюємо маленьку базу даних для магазину.

Магазин продає продукти.

Кожен продукт належить до категорії.

Клієнти можуть створювати замовлення.

Кожне замовлення може містити багато продуктів.

Це означає, що нам потрібні кілька таблиць.

Структура буде така:

categories
products
customers
orders
order_items

Зв’язки:

categories -> products
customers  -> orders
orders     -> order_items
products   -> order_items

Простими словами:

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

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

Замість того, щоб зберігати все в одній величезній хаотичній таблиці, ми розділяємо дані на чисті таблиці.

Бо одна величезна таблиця здається простою на початку.

А потім стає кошмаром.

Як покласти інструменти, шкарпетки, документи, паролі і макарони в одну шухляду.

Можливо.

Але не мудро.

Підготуй базу даних

Відкрий PostgreSQL:

sudo -iu postgres psql

Підключись до своєї бази даних:

\c learning_postgresql

Якщо бази ще немає, створи її:

CREATE DATABASE learning_postgresql;

Потім підключись:

\c learning_postgresql

Тепер видали старі таблиці, якщо вони існують.

Порядок важливий, бо деякі таблиці залежать від інших.

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS categories;

Ми видаляємо order_items першою, бо вона залежить від orders і products.

PostgreSQL захищає зв’язки.

Як строгий бібліотекар.

Але з більшою кількістю крапок з комою.

Створюємо таблицю Categories

Категорія групує продукти.

Приклади:

Electronics
Furniture
Office
Kitchen

Створи таблицю:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Важливі деталі:

• id — це первинний ключ;
• name є обов’язковим;
• name має бути унікальним.

Ми не хочемо дві категорії з назвою Electronics.

Однієї Electronics достатньо.

Навіть для PostgreSQL.

Створюємо таблицю Products

Кожен продукт належить до однієї категорії.

Створи таблицю:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0),
  category_id INTEGER NOT NULL REFERENCES categories(id)
);

Ця таблиця має:

• назву продукту;
• ціну продукту;
• кількість на складі;
• зв’язок з категорією.

Важливий зв’язок:

category_id INTEGER NOT NULL REFERENCES categories(id)

Це означає:

Кожен продукт має належати до існуючої категорії.

PostgreSQL не дозволить продукту посилатися на категорію, якої не існує.

Добре.

Бо уявні категорії не дуже корисні для звітів.

Або для психічного спокою.

Створюємо таблицю Customers

Клієнти роблять замовлення.

Створи таблицю:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Важливі деталі:

• email унікальний;
• name обов’язкове;
• created_at заповнюється автоматично.

У реальних застосунках email часто використовується для входу або комунікації.

Тому дублікати email зазвичай дуже погана ідея.

Дві Anna можуть існувати.

Два однакові email — ні.

PostgreSQL погоджується.

Створюємо таблицю Orders

Замовлення належить одному клієнту.

Створи таблицю:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  status VARCHAR(50) NOT NULL DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ця таблиця зберігає саме замовлення.

Але зверни увагу.

Тут немає колонки total.

Чому?

Бо суму можна обчислити з order_items.

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

Але для навчання краще їх обчислювати.

Це показує, як працюють дані.

А PostgreSQL любить навчати.

Строго.

Створюємо таблицю Order Items

Одне замовлення може містити багато продуктів.

Наприклад:

Замовлення 1:
- Laptop x 1
- Mouse x 2

Саме тому нам потрібна таблиця order_items.

Створи таблицю:

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0)
);

Важливі деталі:

• order_id з’єднується з orders;
• product_id з’єднується з products;
• quantity має бути більше нуля;
• unit_price зберігає ціну продукту на момент замовлення.

Чому ми зберігаємо unit_price тут?

Бо ціни продуктів можуть змінюватися пізніше.

Уяви, що продукт сьогодні коштує 25.

Завтра він коштує 30.

Старі замовлення мають пам’ятати стару ціну.

Інакше звіти перетворяться на історичне місце злочину.

Тому order_items.unit_price зберігає ціну, використану в конкретному замовленні.

Дуже практично.

Дуже реально.

Додаємо категорії

Додай категорії:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office'),
  ('Kitchen');

Перевір їх:

SELECT * FROM categories;

Ти маєш побачити чотири категорії.

Маленький початок.

Цивілізація бази даних починається тут.

Додаємо продукти

Додай продукти:

INSERT INTO products (name, price, stock_quantity, category_id)
VALUES
  ('Laptop', 900.00, 5, 1),
  ('Mouse', 25.00, 30, 1),
  ('Keyboard', 70.00, 15, 1),
  ('Desk Chair', 150.00, 8, 2),
  ('Bookshelf', 120.00, 4, 2),
  ('Notebook', 5.00, 100, 3),
  ('Pen', 2.00, 200, 3),
  ('Whiteboard', 80.00, 3, 3),
  ('Coffee Mug', 12.00, 40, 4),
  ('Kitchen Knife', 35.00, 20, 4);

Перевір продукти:

SELECT * FROM products;

Тепер у нас є продукти, пов’язані з категоріями.

Але ми все ще бачимо category_id.

Люди віддають перевагу назвам.

Тому скоро ми використаємо JOIN.

Бо загадкові ID — це не інтерфейс користувача.

Це крик про допомогу.

Додаємо клієнтів

Додай клієнтів:

INSERT INTO customers (email, name)
VALUES
  ('anna@example.com', 'Anna'),
  ('marco@example.com', 'Marco'),
  ('sofia@example.com', 'Sofia'),
  ('luca@example.com', 'Luca');

Перевір клієнтів:

SELECT * FROM customers;

Тепер у нас є люди, які можуть купувати речі.

Дуже небезпечно.

Для гаманців.

Додаємо замовлення

Створи кілька замовлень:

INSERT INTO orders (customer_id, status)
VALUES
  (1, 'paid'),
  (1, 'paid'),
  (2, 'paid'),
  (3, 'new'),
  (4, 'cancelled');

Це означає:

Anna має два замовлення.
Marco має одне замовлення.
Sofia має одне нове замовлення.
Luca має одне скасоване замовлення.

Перевір замовлення:

SELECT * FROM orders;

Замовлення існують.

Але вони порожні.

Замовлення без продуктів — як коробка від піци без піци.

Технічно коробка.

Емоційно розчарування.

Додаємо позиції замовлень

Тепер додай продукти до замовлень:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
  (1, 1, 1, 900.00),
  (1, 2, 2, 25.00),
  (1, 3, 1, 70.00),

  (2, 6, 10, 5.00),
  (2, 7, 20, 2.00),

  (3, 4, 1, 150.00),
  (3, 5, 1, 120.00),

  (4, 9, 2, 12.00),
  (4, 10, 1, 35.00),

  (5, 2, 1, 25.00);

Перевір позиції замовлень:

SELECT * FROM order_items;

Тепер у нас є реальна міні-структура магазину.

Клієнти.

Замовлення.

Продукти.

Позиції.

Ціни.

Кількості.

Зв’язки.

Красива маленька машина бази даних.

Показуємо продукти з назвами категорій

Почнемо з простого JOIN.

SELECT
  p.id,
  p.name AS product_name,
  p.price,
  p.stock_quantity,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.id;

Це дає читабельні дані продуктів.

Замість:

Laptop | category_id = 1

отримуємо:

Laptop | Electronics

Саме це потрібно реальним застосункам.

Користувачі не мають розшифровувати ID.

Користувачі і так достатньо страждають.

Особливо коли пароль вимагає один символ, одну цифру, стародавню руну і емоційну стабільність.

Показуємо замовлення з іменами клієнтів

Тепер покажи замовлення з іменами клієнтів:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  c.email,
  o.status,
  o.created_at
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
ORDER BY o.id;

Це показує, хто зробив кожне замовлення.

Дуже корисно.

Таблиця лише з customer_id технічно правильна.

Але читабельні дані кращі.

Читабельні дані відділяють хороший admin panel від печери з Excel-файлом.

Показуємо повні деталі замовлень

Тепер покажемо позиції замовлень з інформацією про продукт і клієнта.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS item_total
FROM order_items AS oi
JOIN orders AS o
ON oi.order_id = o.id
JOIN customers AS c
ON o.customer_id = c.id
JOIN products AS p
ON oi.product_id = p.id
ORDER BY o.id, oi.id;

Це вже серйозний запит.

Він з’єднує:

order_items
orders
customers
products

Тепер ми бачимо:

Який клієнт купив який продукт.
Скільки штук.
За якою ціною.
З якою сумою.

Це сила реляційних баз даних.

Маленькі таблиці.

Правильно з’єднані.

Корисні відповіді.

Без шухляди хаосу.

Обчислюємо суму кожного замовлення

Тепер обчислимо загальну вартість кожного замовлення.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.status,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, c.name, o.status
ORDER BY o.id;

Тут ми використовуємо:

• JOIN;
• SUM;
• GROUP BY.

Дуже корисна комбінація.

База даних обчислює суми з позицій замовлень.

Без калькулятора.

Без нервового Excel-файлу.

PostgreSQL справляється.

З гідністю.

Обчислюємо тільки оплачені замовлення

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

Тому можемо фільтрувати по статусу.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Це показує тільки оплачені замовлення.

Дуже важливо.

Бо рахувати скасовані замовлення як дохід — чудовий спосіб зробити красиві звіти і жахливі бізнес-рішення.

Красиві числа можуть брехати.

SQL допомагає.

Але тільки якщо правильно питаєш.

Загальний дохід

Тепер обчислимо загальний дохід з оплачених замовлень.

SELECT
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders AS o
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid';

Це повертає одне число.

Дуже бізнесове число.

Таке число люди ставлять на dashboard.

А потім серйозно на нього дивляться.

Навіть якщо dashboard має тільки три рядки.

Замовлення на клієнта

Порахуй, скільки замовлень має кожен клієнт.

SELECT
  c.name AS customer_name,
  COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
GROUP BY c.name
ORDER BY orders_count DESC;

Ми використовуємо LEFT JOIN, бо можемо хотіти показати також клієнтів з нульовою кількістю замовлень.

У наших поточних даних кожен клієнт має хоча б одне замовлення.

Але в реальному житті багато клієнтів реєструються і нічого не купують.

Дуже нормально.

Дуже боляче для маркетингу.

Оплачені замовлення на клієнта

Тепер порахуй тільки оплачені замовлення.

SELECT
  c.name AS customer_name,
  COUNT(o.id) AS paid_orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
GROUP BY c.name
ORDER BY paid_orders_count DESC;

Важлива деталь:

Умова статусу знаходиться всередині ON:

AND o.status = 'paid'

Чому?

Бо ми все ще хочемо бачити всіх клієнтів.

Якщо поставити цю умову в WHERE, клієнти без оплачених замовлень можуть зникнути.

Це класична деталь LEFT JOIN.

Маленька деталь.

Велика різниця.

Пастка бази даних номер 247.

Збери їх усі.

Загальні витрати клієнта

Тепер обчислимо, скільки кожен клієнт витратив на оплачені замовлення.

SELECT
  c.name AS customer_name,
  COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
LEFT JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY c.name
ORDER BY total_spent DESC;

Тут ми використовуємо COALESCE.

Чому?

Бо якщо клієнт не має оплачених замовлень, сума буде NULL.

Ми хочемо 0.

Оце:

COALESCE(SUM(oi.quantity * oi.unit_price), 0)

означає:

Якщо сума існує, покажи її.
Якщо сума NULL, покажи 0.

Дуже практично.

Дуже дружньо до dashboard.

Дуже “не лякай бухгалтера”.

Продажі по категоріях

Тепер обчислимо продажі по категоріях.

SELECT
  cat.name AS category_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items AS oi
JOIN products AS p
ON oi.product_id = p.id
JOIN categories AS cat
ON p.category_id = cat.id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY cat.name
ORDER BY total_sales DESC;

Це відповідає на питання:

Які категорії приносять найбільший дохід?

Дуже корисно для бізнесу.

Можливо, переможе Electronics.

Можливо, Office.

Можливо, Kitchen здивує всіх чашками.

Ніколи не недооцінюй чашки.

Кількість проданих продуктів

Тепер порахуємо, скільки одиниць кожного продукту було продано.

SELECT
  p.name AS product_name,
  SUM(oi.quantity) AS units_sold
FROM products AS p
JOIN order_items AS oi
ON p.id = oi.product_id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY p.name
ORDER BY units_sold DESC;

Це корисно для планування складу.

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

Якщо книжкові полиці не продаються, можливо, перестань будувати дерев’яну імперію.

Дані допомагають ухвалювати рішення.

Іноді жорстко.

Продукти, які ніколи не продавалися

Тепер знайдемо продукти, які ніколи не продавалися в оплачених замовленнях.

SELECT
  p.name AS product_name
FROM products AS p
LEFT JOIN order_items AS oi
ON p.id = oi.product_id
LEFT JOIN orders AS o
ON oi.order_id = o.id
AND o.status = 'paid'
GROUP BY p.id, p.name
HAVING COUNT(o.id) = 0
ORDER BY p.name;

Цей запит трохи складніший.

Він використовує:

• LEFT JOIN;
• GROUP BY;
• HAVING;
• COUNT.

Ідея така:

Покажи продукти, які мають нуль оплачених замовлень.

Дуже корисно.

Дуже бізнесово.

Дуже “чому ми купили 500 штук цього?”.

Продукти з низьким запасом

Покажи продукти з низьким запасом на складі.

SELECT
  name AS product_name,
  stock_quantity
FROM products
WHERE stock_quantity < 10
ORDER BY stock_quantity ASC;

Це простий запит.

Але дуже практичний.

У реальній системі він міг би живити сповіщення:

Низький запас. Замов більше.

Бази даних потрібні не тільки для зберігання даних.

Вони можуть допомагати помічати проблеми.

Як тихий помічник.

Який говорить тільки SQL.

Створюємо корисні індекси

Тепер додамо індекси, які мають сенс у цьому проєкті.

Первинні ключі вже мають індекси автоматично.

Але зовнішні ключі — хороші кандидати на індекси.

Створи індекси:

CREATE INDEX idx_products_category_id
ON products(category_id);

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

CREATE INDEX idx_order_items_order_id
ON order_items(order_id);

CREATE INDEX idx_order_items_product_id
ON order_items(product_id);

Ці індекси можуть допомагати з JOIN.

Тепер створи індекси для частого фільтрування:

CREATE INDEX idx_orders_status
ON orders(status);

CREATE INDEX idx_orders_created_at
ON orders(created_at);

Вони можуть допомагати запитам типу:

WHERE status = 'paid'

і:

ORDER BY created_at DESC

Не створюй індекси наосліп.

Тут вони мають сенс, бо ці колонки, найімовірніше, з’являтимуться в JOIN, фільтрах або сортуванні.

Індекси мають слідувати за реальними запитами.

Не за вайбом.

PostgreSQL не оптимізує вайб.

Ми вже це обговорювали.

Все ще трагічно.

Перевіряємо запит через EXPLAIN ANALYZE

Проаналізуймо один запит.

EXPLAIN ANALYZE
SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Шукай слова на кшталт:

Seq Scan
Index Scan
Hash Join
Nested Loop
Planning Time
Execution Time

Не панікуй, якщо вивід виглядає складним.

Ціль не в тому, щоб зрозуміти кожну деталь прямо зараз.

Ціль — почати читати план.

PostgreSQL показує, як він працює.

Як механік, який відкриває двигун.

Спочатку страшно.

Потім поступово починає мати сенс.

Потім страшним стаєш ти.

У хорошому сенсі.

Оновлення складу після замовлення

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

Наприклад, якщо замовлення 1 продало:

Laptop x 1
Mouse x 2
Keyboard x 1

Можемо оновити запас вручну:

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 1;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE id = 2;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 3;

Потім перевір:

SELECT
  name,
  stock_quantity
FROM products
WHERE id IN (1, 2, 3);

Це працює.

Але ручні оновлення небезпечні.

У реальних застосунках це треба робити обережно.

Зазвичай усередині транзакції.

Про безпечні зміни в базі даних ми поговоримо пізніше.

Бо бази даних потужні.

А потужність плюс неуважність дорівнює цікавим катастрофам.

Кращий запит для оновлення складу

Можна оновити запас на основі позицій замовлення.

Для замовлення 1:

UPDATE products AS p
SET stock_quantity = p.stock_quantity - oi.quantity
FROM order_items AS oi
WHERE p.id = oi.product_id
AND oi.order_id = 1;

Цей запит оновлює всі продукти із замовлення 1 за один раз.

Дуже корисно.

Дуже потужно.

Також небезпечно, якщо використати неправильний order_id.

PostgreSQL послухається.

Він не твоя мама.

Він не спитає:

Ти впевнений, дорогенький?

Тому завжди перевіряй умови.

Знаходимо деталі замовлень одного клієнта

Покажи всі продукти з оплачених замовлень Anna:

SELECT
  c.name AS customer_name,
  o.id AS order_id,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS item_total
FROM customers AS c
JOIN orders AS o
ON c.id = o.customer_id
JOIN order_items AS oi
ON o.id = oi.order_id
JOIN products AS p
ON oi.product_id = p.id
WHERE c.email = 'anna@example.com'
AND o.status = 'paid'
ORDER BY o.id;

Такий запит можна використати для:

Історії замовлень клієнта.
Рахунків.
Адмін-панелей.
Підтримки клієнтів.

Дуже реально.

Дуже корисно.

Дуже “скажи мені, що цей клієнт купив”.

Пошук продуктів за категорією і ціною

Покажи продукти з категорії Electronics дешевші за 100:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics'
AND p.price < 100
ORDER BY p.price ASC;

Цей запит міг би живити фільтр в онлайн-магазині.

Категорія.

Ціна.

Сортування.

Дуже нормально.

Дуже поширено.

Дуже хороше місце, щоб подумати про індекси, якщо таблиця виросте.

Додаємо індекс на кілька колонок

Якщо ти часто фільтруєш продукти за категорією і сортуєш за ціною, цей індекс може допомогти:

CREATE INDEX idx_products_category_price
ON products(category_id, price);

Цей індекс корисний для запитів типу:

WHERE category_id = 1
ORDER BY price ASC

Порядок колонок має значення.

Індекс починається з category_id.

Потім іде price.

Це не випадково.

Це базується на тому, як працює запит.

Індекси — це не наклейки.

Це стратегія.

Маленька стратегія бази даних.

Як шахи, але з більшою кількістю рахунків.

Типові помилки в маленьких проєктах баз даних

Зберігати все в одній таблиці

Погана ідея:

customer_name
customer_email
product_name
category_name
order_date
quantity
price

Усе в одній таблиці.

Виглядає просто.

На початку.

Потім змінюється email клієнта.

Потім змінюється назва категорії.

Потім змінюється ціна продукту.

Потім звіти стають дивними.

Потім хтось відкриває Excel.

Потім суспільство руйнується.

Використовуй пов’язані таблиці.

Саме для цього існують реляційні бази даних.

Забувати зовнішні ключі

Без зовнішніх ключів дані можуть стати неузгодженими.

Наприклад:

Позиція замовлення посилається на продукт 999.
Але продукту 999 не існує.

Погано.

Зовнішні ключі захищають зв’язки.

PostgreSQL може охороняти двері.

Дозволь йому.

Забувати історичні ціни

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

Зазвичай це погано.

Старе замовлення має пам’ятати ціну на момент покупки.

Історія має значення.

Особливо коли йдеться про гроші.

Рахувати скасовані замовлення як дохід

Це класична помилка у звітах.

Завжди думай про статус.

WHERE o.status = 'paid'

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

Графік виглядає краще.

Реальність — ні.

Реальність зазвичай перемагає.

Використовувати INNER JOIN, коли потрібен LEFT JOIN

Якщо хочеш показати всіх клієнтів, навіть тих, хто не має замовлень, використовуй LEFT JOIN.

Якщо використаєш INNER JOIN, клієнти без замовлень зникнуть.

Іноді це правильно.

Іноді це тихо ламає звіт.

SQL не кричить.

Він повертає саме те, що ти попросив.

Навіть якщо попросив погано.

Дуже чесно.

Дуже небезпечно.

Практика

Покажи всі продукти з категоріями:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY c.name, p.name;

Покажи всі замовлення з клієнтами:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.status,
  o.created_at
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
ORDER BY o.id;

Обчисли суми для кожного замовлення:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Обчисли загальний оплачений дохід:

SELECT
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders AS o
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid';

Покажи загальні витрати клієнтів:

SELECT
  c.name AS customer_name,
  COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
LEFT JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY c.name
ORDER BY total_spent DESC;

Покажи продажі по категоріях:

SELECT
  cat.name AS category_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items AS oi
JOIN products AS p
ON oi.product_id = p.id
JOIN categories AS cat
ON p.category_id = cat.id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY cat.name
ORDER BY total_sales DESC;

Покажи продукти з низьким запасом:

SELECT
  name,
  stock_quantity
FROM products
WHERE stock_quantity < 10
ORDER BY stock_quantity ASC;

Запускай ці запити.

Змінюй дані.

Додавай клієнтів.

Додавай замовлення.

Додавай позиції замовлень.

Зламай щось.

Виправ.

Це і є навчання.

Дуже PostgreSQL.

Дуже практично.

Міні-завдання

Розшир базу даних магазину.

Додай нову таблицю suppliers.

Правила:

• один постачальник може постачати багато продуктів;
• кожен продукт може мати одного постачальника;
• назва постачальника обов’язкова;
• email постачальника має бути унікальним.

Створи таблицю:

CREATE TABLE suppliers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

Додай постачальників:

INSERT INTO suppliers (name, email)
VALUES
  ('Tech Supplier', 'tech@example.com'),
  ('Office Supplier', 'office@example.com'),
  ('Home Supplier', 'home@example.com');

Додай колонку supplier_id до продуктів:

ALTER TABLE products
ADD COLUMN supplier_id INTEGER REFERENCES suppliers(id);

Онови продукти:

UPDATE products
SET supplier_id = 1
WHERE category_id = 1;

UPDATE products
SET supplier_id = 2
WHERE category_id = 3;

UPDATE products
SET supplier_id = 3
WHERE category_id IN (2, 4);

Тепер напиши запити, які:

• покажуть продукти з назвою категорії і назвою постачальника;
• порахують продукти для кожного постачальника;
• знайдуть постачальників, які мають продукти з низьким запасом;
• обчислять продажі по постачальниках;
• покажуть постачальників, чиї продукти принесли більше ніж 500 в оплачених продажах.

Підказка для продажів по постачальниках:

SELECT
  s.name AS supplier_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM suppliers AS s
JOIN products AS p
ON s.id = p.supplier_id
JOIN order_items AS oi
ON p.id = oi.product_id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY s.name
ORDER BY total_sales DESC;

Це завдання додає ще один реалістичний рівень.

Постачальники.

Продукти.

Продажі.

Звіти.

База даних росте.

Мозок трохи страждає.

Це нормально.

Ріст має свою ціну.

JOIN теж.

Підсумок

Сьогодні ти створив практичний проєкт на PostgreSQL.

Ти навчився:

• проєктувати маленьку базу даних для магазину;
• створювати пов’язані таблиці;
• використовувати первинні ключі;
• використовувати зовнішні ключі;
• розділяти дані на чисті структури;
• зберігати історичні ціни в order_items;
• додавати практичні приклади даних;
• читати пов’язані дані через JOIN;
• обчислювати суми замовлень;
• обчислювати дохід;
• рахувати замовлення на клієнта;
• обчислювати витрати клієнтів;
• обчислювати продажі по категоріях;
• знаходити продукти з низьким запасом;
• створювати корисні індекси;
• думати про реальні проблеми звітності.

Це великий крок.

PostgreSQL тепер більше не просто теорія.

Ти використав його як справжній інструмент.

База даних — це не тільки таблиці.

Це структура.

Зв’язки.

Правила.

Питання.

Відповіді.

І іноді дивні помилки, які змушують тебе переосмислювати життєві рішення.

Але це частина шляху.

Наступна лекція

У наступній лекції ми подивимось, як PostgreSQL працює з застосунками.

Поговоримо про те, як backend-застосунки підключаються до PostgreSQL.

Розглянемо connection string.

Змінні середовища.

Базові ідеї безпеки.

І чому записувати паролі від бази даних прямо в коді — це жахлива ідея.

Дуже жахлива.

Така ідея, від якої люди з безпеки прокидаються вночі.