SQL Academy · Lekcja

Dlaczego warto przechowywać historię

Audyt, cofanie zmian i analiza danych historycznych

Dlaczego warto przechowywać historię to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 1 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Problem z nadpisywaniem

Za każdym razem, gdy wykonuje się UPDATE lub DELETE, stare dane bezpowrotnie znikają. Brzmi to efektywnie, ale powoduje rzeczywiste problemy: nie można odpowiedzieć na pytania takie jak jaka była cena w zeszły wtorek? albo kto i kiedy zmienił ten rekord?

Przechowywanie historii oznacza zapisywanie każdej wersji wiersza, a nie tylko jego najnowszej wersji. W tej lekcji wyjaśniono, dlaczego ma to znaczenie i jak SQL pomaga to osiągnąć.

Trzy powody, by zachowywać historię

Istnieją trzy klasyczne powody, aby zachowywać dane historyczne w bazie danych:

1. Audyt — potwierdzenie, że zmiana nastąpiła, kto jej dokonał i kiedy.
2. Cofanie — wycofanie błędu bez przywracania całej bazy danych.
3. Analityka — odpowiadanie na pytania dotyczące przeszłości, wykrywanie trendów i porównywanie okresów.

Dobrze zaprojektowana strategia przechowywania historii spełnia wszystkie trzy potrzeby bez nadmiernego dublowania danych.

Prosta tabela audytu

Najprostsze podejście polega na utworzeniu osobnej tabeli audytu, która rejestruje każdą zmianę. Każdy wiersz zawiera starą wartość, nową wartość, informację o osobie dokonującej zmiany oraz czas jej wykonania.

Poniżej znajduje się tabela audytu dla tabeli products. Kolumna operation przechowuje wartości INSERT, UPDATE lub DELETE.

CREATE TABLE products_audit (
  audit_id   SERIAL PRIMARY KEY,
  product_id INT            NOT NULL,
  operation  VARCHAR(6)     NOT NULL,  -- INSERT / UPDATE / DELETE
  old_price  NUMERIC(10,2),
  new_price  NUMERIC(10,2),
  changed_by TEXT           NOT NULL,
  changed_at TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

Wypełnianie tabeli audytu

Do tabeli audytu można zapisywać dane ręcznie, ale najbardziej niezawodnym rozwiązaniem jest wyzwalacz bazy danych, który uruchamia się automatycznie za każdym razem, gdy dane ulegają zmianie. Dzięki temu żaden kod aplikacji nie może ominąć rejestru.

W tym przykładzie wstawiamy bezpośrednio jeden wiersz audytu, aby pokazać jego strukturę przed omówieniem wyzwalaczy.

INSERT INTO products_audit (product_id, operation, old_price, new_price, changed_by)
VALUES (42, 'UPDATE', 9.99, 12.49, 'alice');

SELECT * FROM products_audit ORDER BY changed_at DESC LIMIT 5;

Odczytywanie ścieżki audytu

Gdy w tabeli audytu zgromadzą się wiersze, można je odpytywać, aby uzyskać odpowiedzi na pytania audytowe. Poniższe zapytanie pokazuje pełną historię cen pojedynczego produktu, zaczynając od najnowszej ceny.

SELECT
  changed_at,
  changed_by,
  operation,
  old_price,
  new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;

Daty obowiązywania: historia według czasu obowiązywania

Tabela audytu rejestruje moment wykonania zmiany (czas transakcji). Czasami trzeba również śledzić, kiedy dana informacja była prawdziwa w rzeczywistym świecie — nazywa się to czasem obowiązywania.

Dodanie kolumn valid_from i valid_to do głównej tabeli tworzy historię według czasu obowiązywania, nazywaną czasami wolno zmieniającym się wymiarem (SCD Type 2).

CREATE TABLE employee_history (
  id          SERIAL PRIMARY KEY,
  employee_id INT            NOT NULL,
  department  TEXT           NOT NULL,
  salary      NUMERIC(10,2)  NOT NULL,
  valid_from  DATE           NOT NULL,
  valid_to    DATE           -- NULL means current record
);

-- Current record for employee 7
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Engineering', 85000, '2023-01-01');

Aktualizowanie wolno zmieniającego się rekordu

Gdy pracownik zmienia dział, nie wykonuje się UPDATE jego wiersza. Zamiast tego zamyka się stary wiersz, ustawiając valid_to, i wstawia nowy wiersz bez daty końcowej. Pozwala to zachować pełną historię.

-- Step 1: close the current record
UPDATE employee_history
SET valid_to = '2024-06-01'
WHERE employee_id = 7 AND valid_to IS NULL;

-- Step 2: insert the new record
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Product', 90000, '2024-06-01');

-- Verify history
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
ORDER BY valid_from;

Odpytywanie w określonym momencie

Dzięki kolumnom czasu obowiązywania można zapytać, co było prawdą w określonym dniu — czego nie dałoby się zrobić przy użyciu zwykłego modelu opartego na UPDATE.

Klauzula WHERE sprawdza, czy docelowa data mieści się w przedziale obowiązywania wiersza.

-- What department and salary did employee 7 have on 2023-09-15?
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
  AND valid_from <= '2023-09-15'
  AND (valid_to > '2023-09-15' OR valid_to IS NULL);

Temporalne tabele z wersjonowaniem systemowym

Współczesne bazy danych SQL (PostgreSQL 16+, SQL Server, MySQL 8) obsługują temporalne tabele z wersjonowaniem systemowym. Baza danych automatycznie śledzi czas transakcji w ukrytych kolumnach, a wcześniejsze stany można odpytywać za pomocą specjalnej składni.

Przykład dla SQL Server — koncepcja jest taka sama w różnych silnikach:

-- SQL Server / MariaDB style (illustrative)
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  status      VARCHAR(20),
  total       NUMERIC(10,2),
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

-- Query historical state
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2024-01-15 12:00:00'
WHERE order_id = 100;

Wykorzystywanie historii do cofania zmian

Tabele historii służą nie tylko do odczytu — można ich użyć do cofania błędów. Jeśli zadanie wsadowe uszkodziło 500 rekordów cen, można je przywrócić z tabeli audytowej bez sięgania do kopii zapasowej.

-- Undo all price changes made by the bad batch job at a specific time
UPDATE products p
SET price = a.old_price
FROM products_audit a
WHERE p.id         = a.product_id
  AND a.operation  = 'UPDATE'
  AND a.changed_by = 'batch_job'
  AND a.changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';

-- Confirm affected rows
SELECT COUNT(*) AS rows_restored FROM products_audit
WHERE changed_by = 'batch_job'
  AND changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';

Analiza danych w czasie

Dane historyczne umożliwiają analizę szeregów czasowych. Można śledzić, jak zmieniała się dana metryka, porównywać wartości z miesiąca na miesiąc lub wykrywać anomalie — a wszystko to bez korzystania z osobnej hurtowni danych.

To zapytanie pokazuje średnią cenę produktu w każdym miesiącu kalendarzowym na podstawie tabeli audytowej.

SELECT
  DATE_TRUNC('month', changed_at) AS month,
  ROUND(AVG(new_price), 2)         AS avg_price
FROM products_audit
WHERE product_id = 42
  AND operation IN ('INSERT', 'UPDATE')
GROUP BY 1
ORDER BY 1;

Sprawdzenie wiedzy

Sprawdź swoją wiedzę na temat przechowywania danych historycznych w SQL.

Podsumowanie: Dlaczego warto zachowywać historię

W tej lekcji dowiedział się Pan lub dowiedziała się Pani, dlaczego nadpisywanie danych jest ryzykowne oraz jak wzorce SQL pozwalają zachować historię na potrzeby audytu, cofania zmian i analizy danych.

Najważniejsze informacje:

Tabele audytowe rejestrują każdą operację INSERT, UPDATE i DELETE, a także informują, kto ją wykonał i kiedy.
Wiersze z czasem obowiązywania (SCD Type 2) używają kolumn valid_from / valid_to do rejestrowania osi czasu w świecie rzeczywistym.
Zapytania na określony moment odpowiadają na pytania dotyczące przeszłości, filtrując dane według tych kolumn dat.
Systemowo wersjonowane tabele temporalne automatyzują śledzenie czasu transakcji na poziomie bazy danych.
Dane historyczne umożliwiają precyzyjne cofanie zmian i zaawansowaną analizę szeregów czasowych bez konieczności korzystania z kopii zapasowych.

Zachowywanie przeszłości nie jest narzutem — stanowi podstawę godnych zaufania systemów, które można poddawać audytowi.

Często zadawane pytania

Czy lekcja „Dlaczego warto przechowywać historię” jest bezpłatna?

Tak — pełny tekst „Dlaczego warto przechowywać historię” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Dlaczego warto przechowywać historię”?

Audyt, cofanie zmian i analiza danych historycznych Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 1 z 4.

Ile czasu zajmuje lekcja „Dlaczego warto przechowywać historię”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?

Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

← Powrót do SQL Academy