Home > Articoli > Applicazione pratica dei database temporali

Applicazione pratica dei database temporali

Introduzione

L’utilizzo dei principi che stanno alla base dell’idea dei database temporali è una pratica che può essere di grande aiuto anche per la risoluzione di problemi legati alla presenza di informazioni temporale nei database ordierni.

In questo articolo si dimostrerà come richieste apparentemente non riconducibili alle regole definite dai concetti legati ai database temporali siano, invece, completamente risolvibili applicanto in toto tali principi. L’applicazione di questi ultimi è necessaria in quanto porta due fondamentali vantaggi:

  • Query più semplici da scrivere e da leggere
  • Migliori performance e maggiori possibilità di ottimizzazione da parte del Query Optimizer

Definizione del problema

In un database esistono due tabelle chiamate "VariationA" e "VariationB" che contengono rispettivamente le informazioni circa alle modifiche effettute alla tabelle "A" e "B".
Le tabelle "A" e "B" contengono quindi i dati attuali, mentre invece le rispettive "VariationA" e "VariationB" contengono i dati passati, ivi compresa la data di modifica degli stessi.

Questo il codice T-SQL da utilizzare per creare la situazione di partenza appena descritta

use [tempdb] go

if object_id(‘dbo.VariationA’) is not null
  drop table dbo.[VariationA]

if object_id(‘dbo.VariationB’) is not null
  drop table [dbo.VariationB] go


—  Variations for column A

—      ——————
—      Year    Old Value
—      ——————
—      2001        A
—      2002        B
—      2003        C
—      2005        D

CREATE TABLE VariationA (  
    Position INT,
    OldValue VARCHAR (5),
    ChangeDate DATETIME )

INSERT VariationA (Position, OldValue, ChangeDate) VALUES (1, ‘A’, ’01/01/2001′)
INSERT VariationA (Position, OldValue, ChangeDate) VALUES (2, ‘B’, ’01/01/2002′)
INSERT VariationA (Position, OldValue, ChangeDate) VALUES (3, ‘C’, ’01/01/2003′)
INSERT VariationA (Position, OldValue, ChangeDate) VALUES (4, ‘D’, ’01/01/2005′)


—  Variations for column B

—      ——————
—      Year    Old Value
—      ——————
—      2000        1
—      2003        2
—      2004        3
—      2005        4

CREATE TABLE VariationB (  
    Position INT,
    OldValue INT,
    ChangeDate DATETIME )

INSERT VariationB (Position, OldValue, ChangeDate) VALUES (1, 1, ’01/01/2000′)
INSERT VariationB (Position, OldValue, ChangeDate) VALUES (2, 2, ’01/01/2003′)
INSERT VariationB (Position, OldValue, ChangeDate) VALUES (3, 3, ’01/01/2004′)
INSERT VariationB (Position, OldValue, ChangeDate) VALUES (4, 4, ’01/01/2005′)
go

La richiesta che viene fatta è quella di produrre una tabella che elenchi tutte le coppie di valori che "A" e "B" hanno avuto nel tempo.
Il risultato deve quindi essere:

ChangeDate                OldValueOfA   OldValueOfB
2000-01-01 00:00:00.000   A             1
2001-01-01 00:00:00.000   A             2
2002-01-01 00:00:00.000   B             2
2003-01-01 00:00:00.000   C             2
2004-01-01 00:00:00.000   D             3
2005-01-01 00:00:00.000   D             4

Risoluzione

Il primo scoglio da superare è legato alla difficoltà di identificare le tabelle VariationA e VariationB come tabelle temporali, ossia munite di due colonne atte ad identificare la validità del valore e non solo una come invece attualmente accade.
Dopotutto se la colonna ChangeDate nelle suddette tabelle identifica una data di cambiamento, ossia la data fino alla quale il valore è valido, possiamo dedurre che tutte le date tra questa e la data del precedente valore reppresentino l’arco di tempo in cui il valore sia stato effettivamente utilizzato.
Possiamo quindi cercare di trasformare la tabella VariationA in una tabella in cui ci siano queste colonne:

Position
OldValue
ValidFrom
ValidTo

per ottenere questa tabella è sufficiente mettere VariationA in relazione con se stessa:

select
 Position = va2.Position,
 OldValue = va2.OldValue,
 ValidFrom = coalesce(va1.ChangeDate, ‘19000101’),
 ValidTo = va2.ChangeDate
from
 [VariationA] as va1
right outer join
 [VariationA] as va2 on va1.Position = va2.Position – 1

il risultato è proprio quello che cercavamo:

Position   OldValue   ValidFrom                 ValidTo
1          A           1900-01-01 00:00:00.000   2001-01-01 00:00:00.000
2          B           2001-01-01 00:00:00.000   2002-01-01 00:00:00.000
3          C           2002-01-01 00:00:00.000   2003-01-01 00:00:00.000
4          D           2003-01-01 00:00:00.000   2005-01-01 00:00:00.000

A questo punto è necessario fare le stessa cosa con la tabella VariationB in modo da ottenere questo risultato:

Position   OldValue   ValidFrom                 ValidTo
1          1          1900-01-01 00:00:00.000   2000-01-01 00:00:00.000
2          2          2000-01-01 00:00:00.000   2003-01-01 00:00:00.000
3          3          2003-01-01 00:00:00.000   2004-01-01 00:00:00.000
4          4          2004-01-01 00:00:00.000   2005-01-01 00:00:00.000

Con queste due tabelle possiamo applicare senza alcuno sforzo (grazie al fatto che i database si basano sulla teoria degli insiemi) quanto Gianluca ha mostrato nelle sessioni su dati temporali. Anche se gli operatori OVERLAPS e CONTAINS non sono supportati da T-SQL, possiamo "facilmente" applicarne l’algortimo di funzionamento al nostro problema. Dobbiamo infatti recuperare tutte le righe dalla tabella VariationA e dalla tabella VariationB per cui le date di validità si sovrappongono (OVERLAPS) o si contengono (CONTAINS):

select
 ChangeDate = case when c1.ValidTo < c2.ValidTo then c1.ValidTo else c2.ValidTo end,
 OldValueOfA = c1.OldValue,
 OldValueOfB = c2.OldValue  
from
 [cte_v1] c1, [cte_v2] c2
where
 (c1.ValidFrom < c2.ValidTo and c1.ValidTo >= c2.ValidFrom)
and
 (c2.ValidFrom < c1.ValidTo and c2.ValidTo >= c1.ValidFrom)

dove cte_v1  e cte_v2 fanno a tabelle temporanee o CTE che contengono il risultato delle query fatte su VariationA e VariationB.

La clausola "case" viene usata per ridurre le colonne di validità ValidFrom…ValidTo all’unica colonna "ChangeDate" richiesta come risultato.

Conclusioni

Grazie all’applicazione della teoria dei database temporali, è possibile risolvere problemi apparentemente molto complessi con estrema eleganza ed efficienza. La query prodotta è la più efficiente possibile in quanto permette all’optmizer di una database engine di sfruttare al meglio gli indici, e di effettuare il minor numero di letture possibili.
Altre soluzioni sono ovviamente possibili, ma sono di gran lunga più complesse (sia concettualmente che operativamente) sia meno efficienti.

E’ anche altresì evidente come, se è possibile, è bene memorizzare i dati utilizzando delle tabelle già nelle forma temporale corretta, usando con due colonne per definire l’arco delle date di validità ValidFrom…ValidTo. In questo modo è possibile evitare di dover generare le suddette colonne a runtime, permettendo un’ulteriore ottimizzazione e un miglior utilizzo degli indici.

Ringraziamenti

Un grazie ad Alberto per aver sollevato il problema nel suo blog.

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Unit testing: Come scrivere la tua prima unit test!

Nell’articolo precedente, il secondo di questa serie, abbiamo descritto come installare il framework tSQLt, il …