0Pricing
SQL Academy · Lezione

Righe temporali e versionate

Query sul tempo di validità e «as of».

Righe temporali e versionate è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cosa sono le tabelle temporali

Le tabelle temporali consentono di monitorare come cambiano i dati nel tempo. Invece di sovrascrivere una riga quando qualcosa cambia, una tabella temporale conserva ogni versione di quella riga, associandola al periodo di tempo in cui era valida.

Esistono due concetti fondamentali: il tempo di validità (quando il fatto era vero nel mondo reale) e il tempo della transazione (quando il database ha registrato il fatto). La combinazione di entrambi dà origine a una tabella completamente bitemporale.

Tempo di validità e tempo della transazione

Il tempo di validità indica quando un fatto è vero nel mondo reale, ad esempio lo stipendio di un dipendente dal 2020-01-01 al 2022-06-30. Il tempo della transazione indica quando la riga del database è stata inserita o resa non valida. Insieme rispondono a due domande: Che cosa era vero? e Quando lo abbiamo saputo?

Nella maggior parte dei casi pratici si inizia dal monitoraggio del tempo di validità, che può essere implementato manualmente utilizzando le colonne valid_from e valid_to.

Creazione di una tabella con tempo di validità

Il modo più semplice per memorizzare righe versionate consiste nell'aggiungere colonne timestamp valid_from e valid_to. Un valore NULL per valid_to (oppure un valore sentinella molto lontano nel futuro, come 9999-12-31) indica che la riga è attualmente attiva.

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

Interrogazione della versione attuale

Per trovare la riga attualmente attiva di ogni dipendente, filtrate le righe in cui valid_to IS NULL (senza limite superiore) oppure in cui la data odierna rientra nell'intervallo di validità. L'utilizzo di un valore sentinella come '9999-12-31' semplifica i confronti tra intervalli.

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

Query a un istante specifico

Una query a un istante specifico chiede: Quali erano i dati in un determinato momento? Si filtrano le righe in cui il timestamp indicato rientra nell'intervallo di validità. Questa è una delle funzionalità più potenti delle tabelle temporali.

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

Aggiornamento di una riga versionata

Quando un fatto cambia, non si deve eseguire UPDATE sulla riga esistente. Si chiude invece la riga attuale impostando il relativo valid_to e si inserisce una nuova riga con il nuovo valore. In questo modo si conserva l'intera cronologia.

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

Utilizzo di daterange per i periodi di validità

Il tipo daterange di PostgreSQL rappresenta in modo elegante un periodo di validità tramite un'unica colonna. È possibile utilizzare l'operatore @> (contiene) per verificare se una data rientra nell'intervallo e aggiungere un vincolo di esclusione per impedire periodi sovrapposti per la stessa entità.

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

Query a un istante specifico con daterange

Con l'approccio daterange, la query a un istante specifico diventa molto leggibile. L'operatore @> verifica che la data indicata sia contenuta nell'intervallo, gestendo automaticamente i limiti inferiore e superiore.

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

Tabelle con versione di sistema (standard SQL)

Lo standard SQL:2011 ha introdotto le tabelle temporali con versione di sistema. Il database gestisce automaticamente le colonne row_start e row_end relative al tempo della transazione. In PostgreSQL questo comportamento viene simulato; in SQL Server e MariaDB è integrato tramite SYSTEM VERSIONING.

L'esempio seguente mostra la sintassi di SQL Server / MariaDB come riferimento per il concetto.

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

Join temporali: allineamento di due tabelle nel tempo

Una difficoltà comune consiste nell'unire due tabelle temporali in base a periodi di tempo corrispondenti. Ad esempio, si possono unire gli stipendi dei dipendenti alle assegnazioni dei reparti quando entrambe hanno periodi di validità. Il join viene eseguito sulla chiave dell'entità e sulla condizione di sovrapposizione, utilizzando && sugli intervalli o confronti espliciti tra date.

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;

Prevenzione di lacune e sovrapposizioni

Due problemi comuni di qualità dei dati nelle tabelle temporali sono le lacune (periodi senza alcun record) e le sovrapposizioni (due righe valide contemporaneamente). Il vincolo di esclusione con && impedisce le sovrapposizioni a livello di database. Per rilevare le lacune è necessario verificare con una query la presenza di intervalli non coperti.

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

Verifica delle conoscenze

Verificate la vostra comprensione delle tabelle temporali e delle query a un istante specifico.

Riepilogo: righe temporali e versionate

In questa lezione avete imparato a modellare i dati variabili nel tempo utilizzando le colonne del tempo di validità e il tipo daterange di PostgreSQL. Punti chiave:

  • Non sovrascrivete mai le righe storiche: chiudete quella precedente e inserite una nuova versione.
  • Utilizzate le query a un istante specifico (valid_from <= target AND valid_to > target) per recuperare i dati relativi a qualsiasi momento passato.
  • Il tipo daterange con l'operatore @> rende le query temporali concise e leggibili.
  • I vincoli di esclusione su && (sovrapposizione di intervalli) garantiscono l'integrità dei dati a livello di database.
  • I join temporali allineano due cronologie intersecando i relativi periodi di validità.

Questi pattern costituiscono il fondamento dell'event sourcing, della registrazione degli audit e di qualsiasi sistema in cui l'accuratezza storica sia importante.

Domande Frequenti

La lezione «Righe temporali e versionate» è gratuita?

Sì — il testo completo di «Righe temporali e versionate» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Righe temporali e versionate»?

Query sul tempo di validità e «as of». Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.

Quanto tempo richiede la lezione «Righe temporali e versionate»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Perché conservare la cronologia
  2. Tabelle di eventi append-only
  3. Righe temporali e versionate
  4. Ricostruire lo stato dagli eventi
← Torna a SQL Academy