Projekt praktyczny: budujemy małą bazę danych dla sklepu

Witaj z powrotem.

W poprzedniej lekcji poznałeś indeksy i podstawy wydajności.

Nauczyłeś się:

• dlaczego zapytania mogą stawać się wolne;
• czym są indeksy;
• jak EXPLAIN ANALYZE pomaga zrozumieć zapytania;
• dlaczego indeksy są przydatne;
• dlaczego indeksy nie są za darmo;
• kiedy indeksy mogą pomóc;
• kiedy indeksy są tylko drogą dekoracją.

Bardzo dobrze.

Teraz czas połączyć wiele elementów w całość.

Dzisiaj zbudujemy mały praktyczny projekt w PostgreSQL.

Nie sztuczny przykład z jedną tabelą.

Nie tajemniczą bazę z nazwami typu table1 i data2.

Nie.

Dzisiaj stworzymy małą bazę danych dla sklepu.

Będzie miała:

customers
categories
products
orders
order_items

To już przypomina projektowanie prawdziwej bazy danych.

Wystarczająco małe, żeby zrozumieć.

Wystarczająco realne, żeby było przydatne.

Jak miecz treningowy.

Ale dla SQL.

Mniej niebezpieczny.

Zazwyczaj.

Czego się nauczysz

W tej lekcji nauczysz się:

• projektować małą relacyjną bazę danych;
• tworzyć wiele powiązanych tabel;
• używać kluczy głównych;
• używać kluczy obcych;
• dodawać realistyczne dane;
• czytać dane za pomocą JOIN;
• obliczać sumy zamówień;
• liczyć zamówienia dla każdego klienta;
• obliczać całkowite wydatki klienta;
• obliczać sprzedaż według kategorii;
• używać funkcji agregujących w praktycznym projekcie;
• tworzyć przydatne indeksy;
• myśleć jak projektant bazy danych.

Pod koniec tej lekcji będziesz mieć działający mini projekt bazy danych.

Nie tylko losowe komendy SQL.

Prawdziwą strukturę.

Małą maszynę bazodanową.

Z klientami.

Produktami.

Zamówieniami.

I wystarczającą liczbą relacji, żeby PostgreSQL poczuł się ważny.

Pomysł projektu

Budujemy małą bazę danych dla sklepu.

Sklep sprzedaje produkty.

Każdy produkt należy do kategorii.

Klienci mogą składać zamówienia.

Każde zamówienie może zawierać wiele produktów.

To oznacza, że potrzebujemy kilku tabel.

Struktura będzie taka:

categories
products
customers
orders
order_items

Relacje:

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

Prościej:

Jedna kategoria ma wiele produktów.
Jeden klient ma wiele zamówień.
Jedno zamówienie ma wiele pozycji zamówienia.
Jeden produkt może pojawić się w wielu pozycjach zamówień.

Tutaj relacyjne bazy danych zaczynają być naprawdę użyteczne.

Zamiast trzymać wszystko w jednej wielkiej chaotycznej tabeli, dzielimy dane na czyste tabele.

Bo jedna wielka tabela wygląda łatwo na początku.

A potem staje się koszmarem.

Jak wrzucenie narzędzi, skarpet, dokumentów, haseł i makaronu do jednej szuflady.

Możliwe.

Ale niemądre.

Przygotowanie bazy danych

Otwórz PostgreSQL:

sudo -iu postgres psql

Połącz się ze swoją bazą danych:

\c learning_postgresql

Jeśli baza nie istnieje, utwórz ją:

CREATE DATABASE learning_postgresql;

Potem połącz się z nią:

\c learning_postgresql

Teraz usuń stare tabele, jeśli istnieją.

Kolejność jest ważna, ponieważ niektóre tabele zależą od innych.

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;

Usuwamy najpierw order_items, ponieważ zależy od orders i products.

PostgreSQL chroni relacje.

Jak surowy bibliotekarz.

Ale z większą liczbą średników.

Tworzenie tabeli Categories

Kategoria grupuje produkty.

Przykłady:

Electronics
Furniture
Office
Kitchen

Utwórz tabelę:

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

Ważne szczegóły:

• id jest kluczem głównym;
• name jest wymagane;
• name musi być unikalne.

Nie chcemy dwóch kategorii o nazwie Electronics.

Jedna Electronics wystarczy.

Nawet dla PostgreSQL.

Tworzenie tabeli Products

Każdy produkt należy do jednej kategorii.

Utwórz tabelę:

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)
);

Ta tabela ma:

• nazwę produktu;
• cenę produktu;
• ilość w magazynie;
• relację z kategorią.

Ważna relacja to:

category_id INTEGER NOT NULL REFERENCES categories(id)

To oznacza:

Każdy produkt musi należeć do istniejącej kategorii.

PostgreSQL nie pozwoli produktowi wskazywać na kategorię, która nie istnieje.

Dobrze.

Bo wymyślone kategorie nie są najlepsze dla raportów.

Ani dla zdrowia psychicznego.

Tworzenie tabeli Customers

Klienci składają zamówienia.

Utwórz tabelę:

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

Ważne szczegóły:

• email jest unikalny;
• name jest wymagane;
• created_at wypełnia się automatycznie.

W prawdziwych aplikacjach email często służy do logowania albo kontaktu.

Dlatego duplikaty emaili to zwykle bardzo zły pomysł.

Dwie Anny mogą istnieć.

Dwa identyczne emaile nie powinny.

PostgreSQL się zgadza.

Tworzenie tabeli Orders

Zamówienie należy do jednego klienta.

Utwórz tabelę:

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
);

Ta tabela przechowuje samo zamówienie.

Ale zauważ jedną rzecz.

Nie ma kolumny total.

Dlaczego?

Bo sumę można obliczyć z order_items.

W wielu prawdziwych systemach sumy też się zapisuje, ze względu na wydajność i historię.

Ale do nauki lepiej je obliczać.

To pokazuje, jak działają dane.

A PostgreSQL lubi uczyć.

W surowy sposób.

Tworzenie tabeli Order Items

Jedno zamówienie może zawierać wiele produktów.

Na przykład:

Zamówienie 1:
- Laptop x 1
- Mouse x 2

Dlatego potrzebujemy order_items.

Utwórz tabelę:

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)
);

Ważne szczegóły:

• order_id łączy się z orders;
• product_id łączy się z products;
• quantity musi być większe od zera;
• unit_price przechowuje cenę produktu w momencie zamówienia.

Dlaczego zapisujemy unit_price tutaj?

Bo ceny produktów mogą się później zmienić.

Wyobraź sobie, że produkt kosztuje dziś 25.

Jutro kosztuje 30.

Stare zamówienia powinny pamiętać starą cenę.

Inaczej raporty zmieniają się w historyczne miejsce zbrodni.

Dlatego order_items.unit_price zapisuje cenę używaną w tym konkretnym zamówieniu.

Bardzo praktyczne.

Bardzo realne.

Dodawanie kategorii

Dodaj kategorie:

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

Sprawdź je:

SELECT * FROM categories;

Powinieneś zobaczyć cztery kategorie.

Mały początek.

Cywilizacja bazy danych zaczyna się tutaj.

Dodawanie produktów

Dodaj produkty:

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);

Sprawdź produkty:

SELECT * FROM products;

Teraz mamy produkty połączone z kategoriami.

Ale nadal widzimy category_id.

Ludzie wolą nazwy.

Więc za chwilę użyjemy JOIN.

Bo tajemnicze ID to nie interfejs użytkownika.

To wołanie o pomoc.

Dodawanie klientów

Dodaj klientów:

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

Sprawdź klientów:

SELECT * FROM customers;

Teraz mamy ludzi, którzy mogą kupować rzeczy.

Bardzo niebezpieczne.

Dla portfeli.

Dodawanie zamówień

Utwórz kilka zamówień:

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

To oznacza:

Anna ma dwa zamówienia.
Marco ma jedno zamówienie.
Sofia ma jedno nowe zamówienie.
Luca ma jedno anulowane zamówienie.

Sprawdź zamówienia:

SELECT * FROM orders;

Zamówienia istnieją.

Ale są puste.

Zamówienie bez produktów jest jak pudełko po pizzy bez pizzy.

Technicznie pudełko.

Emocjonalnie rozczarowanie.

Dodawanie pozycji zamówień

Teraz dodaj produkty do zamówień:

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);

Sprawdź pozycje zamówień:

SELECT * FROM order_items;

Teraz mamy prawdziwą mini strukturę sklepu.

Klienci.

Zamówienia.

Produkty.

Pozycje.

Ceny.

Ilości.

Relacje.

Piękna mała maszyna bazodanowa.

Pokazywanie produktów z nazwami kategorii

Zacznijmy od prostego 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;

To daje czytelne dane produktów.

Zamiast:

Laptop | category_id = 1

otrzymujemy:

Laptop | Electronics

Tego potrzebują prawdziwe aplikacje.

Użytkownicy nie powinni dekodować ID.

Użytkownicy i tak już wystarczająco cierpią.

Zwłaszcza kiedy hasło wymaga jednego symbolu, jednej cyfry, starożytnej runy i stabilności emocjonalnej.

Pokazywanie zamówień z nazwami klientów

Teraz pokaż zamówienia z nazwami klientów:

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;

To mówi nam, kto złożył każde zamówienie.

Bardzo przydatne.

Tabela z samym customer_id jest technicznie poprawna.

Ale czytelne dane są lepsze.

Czytelne dane oddzielają dobry panel admina od jaskini z arkuszem Excel.

Pokazywanie pełnych szczegółów zamówień

Teraz pokażmy pozycje zamówień z informacjami o produkcie i kliencie.

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;

To już poważne zapytanie.

Łączy:

order_items
orders
customers
products

Teraz widzimy:

Który klient kupił który produkt.
Ile sztuk.
W jakiej cenie.
Z jaką sumą.

To jest siła relacyjnych baz danych.

Małe tabele.

Dobrze połączone.

Przydatne odpowiedzi.

Bez szuflady chaosu.

Obliczanie sumy każdego zamówienia

Teraz obliczmy wartość każdego zamówienia.

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;

Tutaj używamy:

• JOIN;
• SUM;
• GROUP BY.

Bardzo przydatna kombinacja.

Baza danych oblicza sumy z pozycji zamówień.

Bez kalkulatora.

Bez nerwowego pliku Excel.

PostgreSQL sobie radzi.

Z godnością.

Obliczanie tylko opłaconych zamówień

Zazwyczaj anulowane zamówienia nie powinny liczyć się jako przychód.

Możemy więc filtrować po statusie.

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;

To pokazuje tylko opłacone zamówienia.

Bardzo ważne.

Bo liczenie anulowanych zamówień jako przychodu to świetny sposób na piękne raporty i fatalne decyzje biznesowe.

Ładne liczby mogą kłamać.

SQL pomaga.

Ale tylko jeśli pytasz poprawnie.

Całkowity przychód

Teraz obliczmy całkowity przychód z opłaconych zamówień.

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';

To zwraca jedną liczbę.

Bardzo biznesową liczbę.

Taką, którą ludzie wrzucają na dashboard.

A potem patrzą na nią poważnie.

Nawet jeśli dashboard ma tylko trzy wiersze.

Zamówienia na klienta

Policz, ile zamówień ma każdy klient.

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;

Używamy LEFT JOIN, ponieważ możemy chcieć pokazać też klientów z zerową liczbą zamówień.

W naszych aktualnych danych każdy klient ma przynajmniej jedno zamówienie.

Ale w prawdziwym życiu wielu klientów rejestruje się i nic nie kupuje.

Bardzo normalne.

Bardzo bolesne dla marketingu.

Opłacone zamówienia na klienta

Teraz policz tylko opłacone zamówienia.

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;

Ważny szczegół:

Warunek statusu jest w ON:

AND o.status = 'paid'

Dlaczego?

Bo nadal chcemy zobaczyć wszystkich klientów.

Jeśli damy ten warunek w WHERE, klienci bez opłaconych zamówień mogą zniknąć.

To klasyczny szczegół LEFT JOIN.

Mały szczegół.

Duża różnica.

Pułapka bazodanowa numer 247.

Zbierz je wszystkie.

Całkowite wydatki klienta

Teraz obliczmy, ile każdy klient wydał na opłacone zamówienia.

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;

Tutaj używamy COALESCE.

Dlaczego?

Bo jeśli klient nie ma opłaconych zamówień, suma wynosi NULL.

My chcemy 0.

To:

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

oznacza:

Jeśli suma istnieje, pokaż ją.
Jeśli suma jest NULL, pokaż 0.

Bardzo praktyczne.

Bardzo przyjazne dla dashboardów.

Bardzo “nie strasz księgowego”.

Sprzedaż według kategorii

Teraz obliczmy sprzedaż według kategorii.

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;

To odpowiada na pytanie:

Które kategorie generują największy przychód?

Bardzo przydatne w biznesie.

Może wygra Electronics.

Może Office.

Może Kitchen zaskoczy wszystkich kubkami.

Nigdy nie lekceważ kubków.

Liczba sprzedanych produktów

Teraz policzmy, ile sztuk każdego produktu zostało sprzedanych.

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;

To jest przydatne do planowania magazynu.

Jeśli długopisy dobrze się sprzedają, zamów więcej długopisów.

Jeśli regały się nie sprzedają, może przestań budować drewniane imperium.

Dane pomagają w decyzjach.

Czasem brutalnie.

Produkty nigdy niesprzedane

Teraz znajdźmy produkty, które nigdy nie zostały sprzedane w opłaconych zamówieniach.

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;

To zapytanie jest trochę bardziej zaawansowane.

Używa:

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

Idea jest taka:

Pokaż produkty, które mają zero opłaconych zamówień.

Bardzo przydatne.

Bardzo biznesowe.

Bardzo “dlaczego kupiliśmy tego 500 sztuk?”.

Produkty z niskim stanem magazynowym

Pokaż produkty z niskim stanem magazynowym.

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

To proste zapytanie.

Ale bardzo praktyczne.

W prawdziwym systemie mogłoby zasilać alert:

Niski stan magazynowy. Zamów więcej.

Bazy danych nie służą tylko do przechowywania danych.

Mogą pomagać zauważać problemy.

Jak cichy asystent.

Który mówi tylko SQL.

Tworzenie przydatnych indeksów

Teraz dodajmy indeksy, które mają sens w tym projekcie.

Klucze główne mają już indeksy automatycznie.

Ale klucze obce są dobrymi kandydatami na indeksy.

Utwórz indeksy:

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);

Te indeksy mogą pomagać przy JOIN.

Teraz utwórz indeksy dla częstego filtrowania:

CREATE INDEX idx_orders_status
ON orders(status);

CREATE INDEX idx_orders_created_at
ON orders(created_at);

Mogą pomóc przy zapytaniach typu:

WHERE status = 'paid'

oraz:

ORDER BY created_at DESC

Nie twórz indeksów na ślepo.

Tutaj mają sens, ponieważ te kolumny prawdopodobnie będą używane w joinach, filtrach albo sortowaniu.

Indeksy powinny wynikać z prawdziwych zapytań.

Nie z wibracji.

PostgreSQL nie optymalizuje wibracji.

Już o tym rozmawialiśmy.

Nadal tragiczne.

Sprawdzanie zapytania przez EXPLAIN ANALYZE

Przeanalizujmy zapytanie.

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;

Szukaj słów takich jak:

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

Nie panikuj, jeśli wynik wygląda skomplikowanie.

Celem nie jest jeszcze zrozumienie każdego szczegółu.

Celem jest zacząć czytać plan.

PostgreSQL mówi ci, jak pracuje.

Jak mechanik otwierający silnik.

Na początku wygląda strasznie.

Potem powoli zaczyna mieć sens.

Potem to ty stajesz się straszny.

W dobrym sensie.

Aktualizacja stanu magazynowego po zamówieniu

W prawdziwych sklepach, gdy zamówienie zostaje opłacone, stan magazynowy powinien się zmniejszyć.

Na przykład jeśli zamówienie 1 sprzedało:

Laptop x 1
Mouse x 2
Keyboard x 1

Możemy ręcznie zaktualizować stan:

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;

Potem sprawdź:

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

To działa.

Ale ręczne aktualizacje są niebezpieczne.

W prawdziwych aplikacjach trzeba to obsługiwać ostrożnie.

Zwykle wewnątrz transakcji.

O bezpiecznych zmianach w bazie danych porozmawiamy później.

Bo bazy danych są potężne.

A potęga plus nieuwaga daje interesujące katastrofy.

Lepsze zapytanie do aktualizacji stanu magazynowego

Możemy zaktualizować stan magazynowy na podstawie pozycji zamówienia.

Dla zamówienia 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;

To aktualizuje wszystkie produkty z zamówienia 1 w jednym zapytaniu.

Bardzo użyteczne.

Bardzo potężne.

Również niebezpieczne, jeśli użyjesz złego order_id.

PostgreSQL posłucha.

To nie twoja mama.

Nie zapyta:

Jesteś pewien, kochanie?

Dlatego zawsze sprawdzaj warunki.

Znajdowanie szczegółów zamówień dla jednego klienta

Pokaż wszystkie produkty z opłaconych zamówień Anny:

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;

To zapytanie można wykorzystać do:

Historii zamówień klienta.
Faktur.
Paneli administracyjnych.
Obsługi klienta.

Bardzo realne.

Bardzo przydatne.

Bardzo “powiedz mi, co ten klient kupił”.

Wyszukiwanie produktów po kategorii i cenie

Pokaż produkty z kategorii Electronics tańsze niż 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;

To zapytanie mogłoby zasilać filtr w sklepie internetowym.

Kategoria.

Cena.

Sortowanie.

Bardzo normalne.

Bardzo częste.

Bardzo dobre miejsce, żeby pomyśleć o indeksach, jeśli tabela urośnie.

Dodawanie indeksu na wiele kolumn

Jeśli często filtrujesz produkty po kategorii i sortujesz po cenie, ten indeks może pomóc:

CREATE INDEX idx_products_category_price
ON products(category_id, price);

Ten indeks jest przydatny dla zapytań typu:

WHERE category_id = 1
ORDER BY price ASC

Kolejność kolumn ma znaczenie.

Indeks zaczyna się od category_id.

Potem jest price.

To nie jest przypadek.

To wynika z tego, jak działa zapytanie.

Indeksy to nie naklejki.

To strategia.

Mała strategia bazodanowa.

Jak szachy, ale z większą liczbą faktur.

Typowe błędy w małych projektach bazodanowych

Trzymanie wszystkiego w jednej tabeli

Zły pomysł:

customer_name
customer_email
product_name
category_name
order_date
quantity
price

Wszystko w jednej tabeli.

Wygląda łatwo.

Na początku.

Potem zmienia się email klienta.

Potem zmienia się nazwa kategorii.

Potem zmienia się cena produktu.

Potem raporty robią się dziwne.

Potem ktoś otwiera Excel.

Potem społeczeństwo upada.

Używaj powiązanych tabel.

Po to są relacyjne bazy danych.

Zapominanie o kluczach obcych

Bez kluczy obcych dane mogą stać się niespójne.

Na przykład:

Pozycja zamówienia wskazuje na produkt 999.
Ale produkt 999 nie istnieje.

Źle.

Klucze obce chronią relacje.

PostgreSQL może pilnować drzwi.

Pozwól mu.

Zapominanie o cenach historycznych

Jeśli pozycje zamówienia tylko wskazują na produkty i nie zapisują unit_price, stare zamówienia mogą się zmienić, gdy zmieni się cena produktu.

To zwykle źle.

Stare zamówienie powinno pamiętać cenę z momentu zakupu.

Historia ma znaczenie.

Zwłaszcza gdy chodzi o pieniądze.

Liczenie anulowanych zamówień jako przychodu

To klasyczny błąd w raportach.

Zawsze myśl o statusie.

WHERE o.status = 'paid'

Bez tego raport przychodu może zawierać anulowane albo nieopłacone zamówienia.

Wykres wygląda lepiej.

Rzeczywistość nie.

Rzeczywistość zwykle wygrywa.

Używanie INNER JOIN, kiedy potrzebujesz LEFT JOIN

Jeśli chcesz pokazać wszystkich klientów, nawet tych bez zamówień, użyj LEFT JOIN.

Jeśli użyjesz INNER JOIN, klienci bez zamówień znikną.

Czasem to poprawne.

Czasem cicho psuje raport.

SQL nie krzyczy.

Zwraca dokładnie to, o co poprosiłeś.

Nawet jeśli poprosiłeś źle.

Bardzo uczciwe.

Bardzo niebezpieczne.

Praktyka

Pokaż wszystkie produkty z kategoriami:

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;

Pokaż wszystkie zamówienia z klientami:

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;

Oblicz sumy dla każdego zamówienia:

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;

Oblicz całkowity opłacony przychód:

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';

Pokaż całkowite wydatki klienta:

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;

Pokaż sprzedaż według kategorii:

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;

Pokaż produkty z niskim stanem magazynowym:

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

Uruchamiaj te zapytania.

Zmieniaj dane.

Dodawaj klientów.

Dodawaj zamówienia.

Dodawaj pozycje zamówień.

Zepsuj coś.

Napraw to.

To jest nauka.

Bardzo PostgreSQL.

Bardzo praktycznie.

Mini wyzwanie

Rozszerz bazę danych sklepu.

Dodaj nową tabelę suppliers.

Zasady:

• jeden dostawca może dostarczać wiele produktów;
• każdy produkt może mieć jednego dostawcę;
• nazwa dostawcy jest wymagana;
• email dostawcy musi być unikalny.

Utwórz tabelę:

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

Dodaj dostawców:

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

Dodaj kolumnę supplier_id do produktów:

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

Zaktualizuj produkty:

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);

Teraz napisz zapytania, które:

• pokażą produkty z nazwą kategorii i nazwą dostawcy;
• policzą produkty dla każdego dostawcy;
• znajdą dostawców, którzy mają produkty z niskim stanem magazynowym;
• obliczą sprzedaż według dostawcy;
• pokażą dostawców, których produkty wygenerowały więcej niż 500 w opłaconej sprzedaży.

Podpowiedź dla sprzedaży według dostawcy:

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;

To wyzwanie dodaje kolejny realistyczny poziom.

Dostawcy.

Produkty.

Sprzedaż.

Raporty.

Baza danych rośnie.

Mózg trochę cierpi.

To normalne.

Rozwój ma swoją cenę.

JOIN też.

Podsumowanie

Dzisiaj zbudowałeś praktyczny projekt w PostgreSQL.

Nauczyłeś się:

• projektować małą bazę danych dla sklepu;
• tworzyć powiązane tabele;
• używać kluczy głównych;
• używać kluczy obcych;
• dzielić dane na czyste struktury;
• zapisywać historyczne ceny w order_items;
• dodawać praktyczne dane przykładowe;
• czytać powiązane dane przez JOIN;
• obliczać sumy zamówień;
• obliczać przychód;
• liczyć zamówienia na klienta;
• obliczać wydatki klientów;
• obliczać sprzedaż według kategorii;
• znajdować produkty z niskim stanem magazynowym;
• tworzyć przydatne indeksy;
• myśleć o prawdziwych problemach raportowania.

To duży krok.

PostgreSQL nie jest już tylko teorią.

Użyłeś go jak prawdziwego narzędzia.

Baza danych to nie tylko tabele.

To struktura.

Relacje.

Reguły.

Pytania.

Odpowiedzi.

I czasem dziwne błędy, które każą ci kwestionować życiowe wybory.

Ale to część podróży.

Następna lekcja

W następnej lekcji zobaczymy, jak PostgreSQL współpracuje z aplikacjami.

Porozmawiamy o tym, jak aplikacje backendowe łączą się z PostgreSQL.

Zobaczymy connection stringi.

Zmienne środowiskowe.

Podstawowe idee bezpieczeństwa.

I dlaczego wpisywanie haseł do bazy danych bezpośrednio w kodzie to fatalny pomysł.

Naprawdę fatalny.

Taki pomysł, który budzi ludzi od bezpieczeństwa w środku nocy.