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:
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:
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:
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:
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):
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.