SQL Academy · Lekcja

Wiersze czasowe i wersjonowane

Zapytania według czasu obowiązywania i stanu na dany moment

Wiersze czasowe i wersjonowane to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 3 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.

Czym są tabele temporalne?

Tabele temporalne umożliwiają śledzenie, jak dane zmieniają się w czasie. Zamiast nadpisywać wiersz po zmianie, tabela temporalna zachowuje każdą jego wersję, oznaczając ją okresem, w którym była obowiązująca.

Istnieją dwa kluczowe pojęcia: czas obowiązywania (kiedy dany fakt był prawdziwy w świecie rzeczywistym) oraz czas transakcji (kiedy baza danych zarejestrowała ten fakt). Połączenie obu daje w pełni bi-temporalną tabelę.

Czas obowiązywania a czas transakcji

Czas obowiązywania określa, kiedy dany fakt jest prawdziwy w świecie rzeczywistym — na przykład wynagrodzenie pracownika od 2020-01-01 do 2022-06-30. Czas transakcji określa, kiedy wiersz został wstawiony do bazy danych lub oznaczony jako nieaktualny. Razem te pojęcia odpowiadają na dwa pytania: Co było prawdą? oraz Kiedy się o tym dowiedzieliśmy?

W większości praktycznych zastosowań zaczyna się od śledzenia czasu obowiązywania, które można zaimplementować ręcznie za pomocą kolumn valid_from i valid_to.

Tworzenie tabeli czasu obowiązywania

Najprostszy sposób przechowywania wersjonowanych wierszy polega na dodaniu kolumn ze znacznikami czasu valid_from i valid_to. Wartość NULL w kolumnie valid_to (lub specjalna wartość oznaczająca odległą przyszłość, taka jak 9999-12-31) oznacza, że wiersz jest obecnie aktywny.

CREATE TABLE employee_salary (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_from  DATE NOT NULL,
  valid_to    DATE
);

INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES
  (1, 50000, '2020-01-01', '2022-06-30'),
  (1, 60000, '2022-07-01', NULL);

Odczytywanie bieżącej wersji

Aby znaleźć obecnie aktywny wiersz dla każdego pracownika, należy odfiltrować wiersze, w których valid_to IS NULL (okres jest otwarty) lub w których dzisiejsza data mieści się w zakresie obowiązywania. Użycie wartości specjalnej, takiej jak '9999-12-31', upraszcza porównywanie zakresów.

SELECT employee_id, salary
FROM employee_salary
WHERE valid_to IS NULL
ORDER BY employee_id;

Zapytania as-of

Zapytanie as-of pyta: Jakie były dane w określonym momencie? Należy odfiltrować wiersze, dla których podany znacznik czasu mieści się w okresie obowiązywania. Jest to jedna z najpotężniejszych funkcji tabel temporalnych.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_from, valid_to
FROM employee_salary
WHERE employee_id = 1
  AND valid_from <= '2021-03-15'
  AND (valid_to IS NULL OR valid_to > '2021-03-15');

Aktualizowanie wersjonowanego wiersza

Gdy fakt ulega zmianie, nie należy wykonywać operacji UPDATE na istniejącym wierszu. Zamiast tego należy zamknąć bieżący wiersz, ustawiając jego wartość valid_to, a następnie wykonać INSERT nowego wiersza z nową wartością. Dzięki temu zachowana zostaje pełna historia.

-- Employee 1 gets a raise effective 2023-01-01
BEGIN;

-- Close the current open row
UPDATE employee_salary
SET valid_to = '2022-12-31'
WHERE employee_id = 1
  AND valid_to IS NULL;

-- Insert the new version
INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES (1, 72000, '2023-01-01', NULL);

COMMIT;

Używanie daterange do określania okresów obowiązywania

Typ daterange w PostgreSQL elegancko odwzorowuje okres obowiązywania w jednej kolumnie. Można użyć operatora @> (zawiera), aby sprawdzić, czy data mieści się w zakresie, oraz dodać ograniczenie wykluczające, aby zapobiec nakładaniu się okresów dla tej samej encji.

CREATE TABLE employee_salary_v2 (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_period DATERANGE NOT NULL,
  EXCLUDE USING GIST (employee_id WITH =, valid_period WITH &&)
);

INSERT INTO employee_salary_v2 (employee_id, salary, valid_period)
VALUES
  (1, 50000, '[2020-01-01, 2022-07-01)'),
  (1, 60000, '[2022-07-01, infinity)');

Zapytanie as-of z użyciem daterange

W podejściu opartym na daterange zapytanie as-of staje się bardzo czytelne. Operator @> sprawdza, czy podana data jest zawarta w zakresie, automatycznie uwzględniając jego dolną i górną granicę.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_period
FROM employee_salary_v2
WHERE employee_id = 1
  AND valid_period @> '2021-03-15'::date;

Tabele wersjonowane systemowo (standard SQL)

Standard SQL:2011 wprowadził systemowo wersjonowane tabele temporalne. Baza danych automatycznie zarządza kolumnami czasu transakcji row_start i row_end. W PostgreSQL można zasymulować to rozwiązanie, natomiast w SQL Server i MariaDB jest ono wbudowane za pomocą SYSTEM VERSIONING.

Poniższy przykład przedstawia składnię SQL Server / MariaDB jako odniesienie ilustrujące tę koncepcję.

-- SQL Server / MariaDB syntax (reference)
CREATE TABLE dbo.Product (
  ProductID   INT PRIMARY KEY,
  Name        VARCHAR(100),
  Price       DECIMAL(10,2),
  SysStart    DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEnd      DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStart, SysEnd)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Product_History));

Złączenia temporalne: uzgadnianie dwóch tabel w czasie

Częstym wyzwaniem jest łączenie dwóch tabel temporalnych na podstawie zgodnych okresów. Na przykład można połączyć wynagrodzenia pracowników z przydziałami do działów, gdy oba typy danych mają okresy obowiązywania. Złączenie wykonuje się na podstawie klucza encji ORAZ warunku nakładania się okresów, używając operatora && dla zakresów lub jawnych porównań dat.

CREATE TABLE dept_assignment (
  employee_id INT,
  department  VARCHAR(50),
  valid_period DATERANGE
);

INSERT INTO dept_assignment VALUES
  (1, 'Engineering', '[2020-01-01, infinity)'),
  (1, 'Marketing',   '[2019-01-01, 2020-01-01)');

-- Periods where employee 1 was in Engineering AND had salary > 55000
SELECT s.salary, d.department,
       s.valid_period * d.valid_period AS overlap_period
FROM employee_salary_v2 s
JOIN dept_assignment d
  ON s.employee_id = d.employee_id
  AND s.valid_period && d.valid_period
WHERE s.employee_id = 1
  AND s.salary > 55000;

Zapobieganie lukom i nakładaniu się okresów

Dwa częste problemy z jakością danych w tabelach temporalnych to luki (okresy bez rekordu) oraz nakładanie się okresów (dwa wiersze obowiązujące jednocześnie). Ograniczenie wykluczające z operatorem && zapobiega nakładaniu się okresów na poziomie bazy danych. Wykrywanie luk wymaga sprawdzenia za pomocą zapytania, czy nie brakuje pokrycia.

-- Find gaps in salary history for employee 1
-- (periods where upper(prev) < lower(next))
SELECT
  upper(a.valid_period) AS gap_start,
  lower(b.valid_period) AS gap_end
FROM employee_salary_v2 a
JOIN employee_salary_v2 b
  ON a.employee_id = b.employee_id
  AND upper(a.valid_period) < lower(b.valid_period)
WHERE a.employee_id = 1
  AND NOT EXISTS (
    SELECT 1 FROM employee_salary_v2 c
    WHERE c.employee_id = 1
      AND lower(c.valid_period) > upper(a.valid_period)
      AND lower(c.valid_period) < lower(b.valid_period)
  )
ORDER BY gap_start;

Sprawdzenie wiedzy

Sprawdź swoją wiedzę na temat tabel temporalnych i zapytań as-of.

Podsumowanie: wiersze temporalne i wersjonowane

W tej lekcji dowiedział się Pan lub dowiedziała się Pani, jak modelować dane zmieniające się w czasie za pomocą kolumn czasu obowiązywania i typu daterange w PostgreSQL. Najważniejsze informacje:

Nigdy nie nadpisuj wierszy historycznych — zamknij stary wiersz i wstaw nową wersję.
Używaj zapytań as-of (valid_from <= target AND valid_to > target), aby pobierać dane z dowolnego momentu w przeszłości.
Typ daterange wraz z operatorem @> sprawia, że zapytania temporalne są zwięzłe i czytelne.
Ograniczenia wykluczające z operatorem && (nakładanie się zakresów) zapewniają integralność danych na poziomie bazy danych.
Złączenia temporalne uzgadniają dwie historie, przecinając ich okresy obowiązywania.

Te wzorce stanowią podstawę event sourcingu, rejestrowania audytu oraz każdego systemu, w którym dokładność danych historycznych ma znaczenie.

Często zadawane pytania

Czy lekcja „Wiersze czasowe i wersjonowane” jest bezpłatna?

Tak — pełny tekst „Wiersze czasowe i wersjonowane” 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 „Wiersze czasowe i wersjonowane”?

Zapytania według czasu obowiązywania i stanu na dany moment Ć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 3 z 4.

Ile czasu zajmuje lekcja „Wiersze czasowe i wersjonowane”?

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