SQL Academy · Lektion

Temporale og versionsstyrede rækker

Forespørgsler efter gyldighedstidspunkt og status på et givent tidspunkt

Lektion 3 af 413 trin

Temporale og versionsstyrede rækker er en gratis SQL Academy-lektion på CoddyKit. Dette er lektion 3 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i SQL Academy, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. SQL Academy-kurset indeholder 4 lektioner i alt.

Hvad er temporale tabeller

Temporale tabeller gør det muligt at følge, hvordan data ændrer sig over tid. I stedet for at overskrive en række, når noget ændrer sig, bevarer en temporal tabel alle versioner af rækken, hver markeret med den tidsperiode, hvor den var gyldig.

Der er to centrale begreber: gyldighedstid (hvornår faktummet var sandt i den virkelige verden) og transaktionstid (hvornår databasen registrerede faktummet). Når de kombineres, får du en fuldt bitemporal tabel.

Gyldighedstid kontra transaktionstid

Gyldighedstid angiver, hvornår et faktum er sandt i den virkelige verden — for eksempel en medarbejders løn fra 2020-01-01 til 2022-06-30. Transaktionstid er det tidspunkt, hvor databasens række blev indsat eller udløb. Tilsammen besvarer de to spørgsmål: Hvad var sandt? og Hvornår vidste vi det?

De fleste praktiske anvendelser begynder med sporing af gyldighedstid, som du kan implementere manuelt ved hjælp af kolonnerne valid_from og valid_to.

Oprettelse af en tabel med gyldighedstid

Den enkleste måde at gemme versionerede rækker på er at tilføje tidsstempelkolonnerne valid_from og valid_to. En valid_to på NULL (eller en langt fremtidig markør som 9999-12-31) betyder, at rækken er aktiv i øjeblikket.

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

Forespørgsel efter den aktuelle version

Hvis du vil finde den aktuelt aktive række for hver medarbejder, skal du filtrere efter rækker, hvor valid_to IS NULL (uden slutdato), eller hvor dags dato ligger inden for gyldighedsintervallet. En markør som '9999-12-31' gør det enklere at sammenligne intervaller.

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

Forespørgsler på et bestemt tidspunkt

En forespørgsel på et bestemt tidspunkt spørger: Hvordan så dataene ud på et bestemt tidspunkt? Du filtrerer rækker, hvor det angivne tidsstempel ligger inden for gyldighedsintervallet. Det er en af de mest kraftfulde funktioner ved temporale tabeller.

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

Opdatering af en versioneret række

Når et faktum ændrer sig, skal du ikke udføre UPDATE på den eksisterende række. I stedet lukker du den aktuelle række ved at angive dens valid_to og indsætter en ny række med den nye værdi. Det bevarer hele historikken.

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

Brug af daterange til gyldighedsperioder

PostgreSQLs type daterange modellerer elegant en gyldighedsperiode i en enkelt kolonne. Du kan bruge operatoren @> (indeholder) til at kontrollere, om en dato ligger i intervallet, og tilføje en eksklusionsbegrænsning for at forhindre overlappende perioder for den samme entitet.

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

Forespørgsel på et bestemt tidspunkt med daterange

Med metoden baseret på daterange bliver forespørgslen på et bestemt tidspunkt meget læsevenlig. Operatoren @> kontrollerer, at den angivne dato er indeholdt i intervallet, og håndterer automatisk de nedre og øvre grænser.

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

Systemversionerede tabeller (SQL-standarden)

Standarden SQL:2011 introducerede systemversionerede temporale tabeller. Databasen håndterer automatisk kolonnerne row_start og row_end for transaktionstid. I PostgreSQL efterligner du denne funktion; i SQL Server og MariaDB er den indbygget med SYSTEM VERSIONING.

Eksemplet nedenfor viser syntaksen for SQL Server og MariaDB som reference til begrebet.

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

Temporale sammenføjninger: Tilpasning af to tabeller over tid

En almindelig udfordring er at sammenføje to temporale tabeller på matchende tidsperioder. Du kan for eksempel sammenføje medarbejderes lønninger med afdelingsplaceringer, hvor begge har perioder med gyldighedstid. Du sammenføjer på entitetsnøglen OG en overlapningsbetingelse ved hjælp af && på intervaller eller eksplicitte datosammenligninger.

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;

Forebyggelse af huller og overlapninger

To almindelige problemer med datakvaliteten i temporale tabeller er huller (perioder uden en post) og overlapninger (to rækker, der er gyldige samtidig). Eksklusionsbegrænsningen med && forhindrer overlapninger på databaseniveau. Det kræver en forespørgsel at opdage huller ved at kontrollere, om der mangler dækning.

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

Videnstest

Test din forståelse af temporale tabeller og forespørgsler på bestemte tidspunkter.

Besvar den i den interaktive lektion

Opsummering: Temporale og versionerede rækker

I denne lektion lærte du at modellere data, der ændrer sig over tid, ved hjælp af kolonner for gyldighedstid og PostgreSQLs type daterange. Vigtige pointer:

Overskriv aldrig historiske rækker — luk den gamle række, og indsæt en ny version.
Brug forespørgsler på bestemte tidspunkter (valid_from <= target AND valid_to > target) til at hente data fra et hvilket som helst tidligere tidspunkt.
Typen daterange sammen med operatoren @> gør temporale forespørgsler korte og lette at læse.
Eksklusionsbegrænsninger på && (overlapning mellem intervaller) håndhæver dataintegriteten på databaseniveau.
Temporale sammenføjninger tilpasser to historikker ved at finde fællesmængden af deres gyldighedsperioder.

Disse mønstre danner grundlaget for event sourcing, revisionslogføring og alle systemer, hvor historisk nøjagtighed er vigtig.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 46
Lektioner: 183

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Temporale og versionsstyrede rækker” gratis?

Ja — hele teksten til “Temporale og versionsstyrede rækker” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af SQL Academy-kurset, skal du opgradere til CoddyKit PRO. SQL Academy-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Temporale og versionsstyrede rækker”?

Forespørgsler efter gyldighedstidspunkt og status på et givent tidspunkt Du øver dig i SQL Academy med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på SQL Academy?

Der kræves ingen tidligere erfaring. SQL Academy på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 3 af 4.

Hvor lang tid tager lektionen “Temporale og versionsstyrede rækker”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne SQL Academy-lektion?

Ja. Alle SQL Academy-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

← Tilbage til SQL Academy