Пекарната, етап 6: отчетът, копието и DROP в SQL

Проблем

Месецът свърши

Октомври приключи. Пекарната иска отчет: как растат приходите ден по ден, кой е най-добрият клиент и какъв дял от парите идва от него. После иска копие на базата, преди да започне нов сезон. А ти, за да видиш целия път, ще махнеш базата парче по парче, докато не стане празна, както в началото на етап 1.

Полетата тръгват от базата след етап 5: данните, индексът, изгледът order_totals, тригерът take_stock и колоната phone.

Идеята

Натрупаната сума

Прозоречна функция смята стойност от няколко реда, без да ги слива в един. SUM(t.total) OVER (ORDER BY o.ordered_at) е сумата на всички поръчки до този ред включително:

SQLпразна база

SELECT o.ordered_at, t.total,
       SUM(t.total) OVER (ORDER BY o.ordered_at) AS so_far
FROM order_totals t
JOIN orders o ON o.id = t.id
WHERE t.status <> 'cancelled'
ORDER BY o.ordered_at;

Така се вижда как месецът расте до 47 €, без отказаната поръчка.

Пробвай

Отчетът

Отчет по клиенти, без отказаните поръчки: името, похарченото (spent), делът от всички приходи в проценти (share, закръглен до 1 знак) и мястото в класирането (place, с RANK). Подреди по място. Сумите вземи от изгледа order_totals.

SQLпразна база

SELECT c.name, SUM(t.total) AS spent
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
GROUP BY c.id;

Решение

SELECT c.name,
       SUM(t.total) AS spent,
       ROUND(100.0 * SUM(t.total) / SUM(SUM(t.total)) OVER (), 1) AS share,
       RANK() OVER (ORDER BY SUM(t.total) DESC) AS place
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.status <> 'cancelled'
GROUP BY c.id
ORDER BY place;

По-точно, за любопитните

RANK дава еднакво място на равните и прескача следващото (1, 1, 3). DENSE_RANK не прескача (1, 1, 2), а ROW_NUMBER дава различни номера дори на равните.

Идеята

Копието

Преди голяма промяна (нов сезон, миграция, внос на данни) се прави копие. VACUUM INTO записва цялата база в нов файл, докато тя работи, а ATTACH отваря копието като втора база до първата:

SQLпразна база

VACUUM INTO '/pekarna-2026-10-31.db';
ATTACH '/pekarna-2026-10-31.db' AS copy;

SELECT
  (SELECT COUNT(*) FROM orders) AS orders_now,
  (SELECT COUNT(*) FROM copy.orders) AS orders_in_copy,
  (SELECT COUNT(*) FROM copy.sqlite_master WHERE type = 'table') AS tables_in_copy;

Шестте поръчки и петте таблици са и в копието.

Пусни полето втори път и VACUUM INTO спира: файлът вече го има. Така е и в истинската работа: всяко копие е нов файл с датата в името.

По-точно, за любопитните

В PostgreSQL копието се прави отвън: pg_dump bakery > bakery-2026-10-31.sql и връщане с psql. Копие, което никой не е пробвал да върне, не е копие.

Виж го

Какво зависи от какво

Изгледът order_totals чете от order_items. Ако таблицата изчезне първа:

SQLпразна база

DROP TABLE order_items;
SELECT * FROM order_totals;

Изгледът остава, но вече сочи към нищо. Индексите и тригерите на таблица изчезват с нея, изгледите не. Затова първо се махат изгледите. PostgreSQL е по-строг: там DROP TABLE спира, докато има изглед, който чете от таблицата.

Пробвай

Изгледът, тригерът и индексът

Махни изгледа order_totals, тригера take_stock и индекса orders_customer.

SQLпразна база

SELECT type, name
FROM sqlite_master
WHERE type IN ('view', 'trigger', 'index') AND name NOT LIKE 'sqlite_%';

Решение

DROP VIEW order_totals;
DROP TRIGGER take_stock;
DROP INDEX orders_customer;

Виж го

Родителят първи

Таблица, към която други сочат, не може да изчезне, докато има редове, които сочат към нея:

SQLпразна база

DROP TABLE customers;

DROP TABLE първо трие всички редове, а външният ключ в orders не дава да изчезнат клиенти с поръчки.

Упражнение

Празна база

Махни петте таблици така, че нито един DROP да не спре с грешка. Накрая в базата не бива да остане нищо.

SQLпразна база

DROP TABLE order_items;

Решение

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

По-точно, за любопитните

orders преди order_items също минава, но по случайност: ON DELETE CASCADE изтрива редовете на поръчките, преди таблицата да изчезне. Без CASCADE същият ред спира. Сигурният ред е винаги от децата към родителите. В PostgreSQL DROP TABLE … CASCADE маха и всичко, което зависи от таблицата: удобно и опасно.

Идеята

Целият път

Базата е празна, както в началото. По пътя:

Етап 1: петте CREATE TABLE с ключове, NOT NULL, UNIQUE, CHECK, DEFAULT и CASCADE.
Етап 2: INSERT, RETURNING, транзакция с BEGIN и COMMIT, ROLLBACK и INSERT … SELECT.
Етап 3: въпросите с JOIN, LEFT JOIN, GROUP BY, WITH и COALESCE.
Етап 4: UPDATE, DELETE и upsert, с правилата, които спират грешното.
Етап 5: индекс, изглед, тригер и ALTER TABLE.
Етап 6: отчет с прозорци, копие с VACUUM INTO и DROP.

На твоя компютър последната стъпка е изтриването на файла bakery.db. В PostgreSQL е DROP DATABASE bakery;, пуснато от друга база, докато никой не е свързан към нея.

Тест

Провери се

1. Каква е разликата между SUM(t.total) с GROUP BY и SUM(SUM(t.total)) OVER ()?Второто е грешкаПървото е сумата на групата, второто е сборът на всички групи, показан на всеки редНяма разлика

2. Защо DROP TABLE customers спира с грешка, докато има поръчки?orders сочи към customers. DROP първо трие редовете, а външният ключ не дава да изчезнат клиенти с поръчкиcustomers е защитена таблицаDROP изисква IF EXISTS

3. Изтриваш таблицата order_items. Какво става с изгледа order_totals?Изтрива се с неяПази старите сумиОстава, но всяко четене от него дава грешка

Пекарната, етап 6: отчетът, копието и DROP

Бисквитки