Un Quiz prima di partire: Think Set Oriented!

Venerdi alle 11.30 partirò per Seattle per l'MVP Summit 2008. Il ritorno è previsto per il 18 Aprile e durante questo periodo cerchero di bloggare tutto ciò che non sarò strettamente sotto NDA.

Detto questo, però, prima di partire vorrei lanciare quiz lasciandovi cosi una decina di giorni di tempo per trovarne la soluzione. Il quiz è piuttosto complesso ed è sempre legato all'utilizzo di dati temporali, e come in precedenza lo spunto me lo ha dato Alberto che tempo fa mi sottopose una problematica interessante, che può sicuramente essere un buon esercizio utile a tutti per cercare di ragionare sempre più in modo "Set-Oriented"

La situazione di partenza è questa:

USE [tempdb]
GO

IF (OBJECT_ID('dbo.S1') IS NOT NULL) DROP TABLE dbo.S1;
IF (OBJECT_ID('dbo.S2') IS NOT NULL) DROP TABLE dbo.S2;
GO

CREATE TABLE [dbo].[S1](
    [Telefono] VARCHAR(100) NOT NULL,
    [DataInizio] [int] NOT NULL,
    [DataFine] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[S2](
    [Indirizzo] VARCHAR(100) NOT NULL,
    [DataInizio] [int] NOT NULL,
    [DataFine] [int] NULL
) ON [PRIMARY]
GO

insert [dbo].[S1] ([Telefono], [DataInizio], [DataFine]) values ('12345', 20071219, 20080202)
insert [dbo].[S1] ([Telefono], [DataInizio], [DataFine]) values ('67890', 20080202, null)
insert [dbo].[S2] ([Indirizzo], [DataInizio], [DataFine]) values ('Via Forze Armate 13', 20071201, 20071231)
insert [dbo].[S2] ([Indirizzo], [DataInizio], [DataFine]) values ('Via Liberta 77', 20071231, null)
GO

Si hanno quindi due tabelle S1 e S2 che contengono delle informazioni sulle variazioni dei dati avvenuti in una anagrafica utenti (la tabella dell'anagrafica cliente non l'abbiamo e non ci serve). La tabella S1 contiene le modifiche avvenute al numero di telefono di un ipotetico utente, la tabella S2 contiene invece le modifiche all'indirizzo dello stesso.

Per semplicità ipotizziamo che tutte le modifche siano relative ad un solo utente.

DataInizio e DataFine definiscono il range di validità temporale del valore. Quando datafine = null significa che quella riga rappresenta il valore attualmente in uso.

Applicando questa logica, la tabella S1 ha questo significato:

  • l'utente ci ha comunicato il suo numero di telefono (12345) la prima volta nel 2007-12-19.
  • l'utente ha mantenuto tale numero di telefono fino al 2008-02-02
  • dal 2008-02-02 il numero di telefono cambia e diventa 67890
  • Il numero di telefono suddetto è quello attualmente in uso

La richiesta è molto semplice: produrre un risultato che ricostruisca in modo consolidato lo storico dei dati di questo utente, tenendo conto della validità temporale degli stessi.

Dobbiamo quindi produrre questo risultato:

clip_image001

Non usate cursori ne cicli ovviamente Smile

Risolvere questo quiz aiuta ad esercitarsi a pensare ragionando in termini di insiemi piuttosto che di singole righe, tenendo però conto delle connotazioni temporali che hanno i dati.

Questo porta a disegnare meglio i database ed a poter recupera i dati tendendo correttamente in considerazione la loro validità nel tempo.

E poi è semplicemente una bella sfida da superare Smile

Published mercoledì 9 aprile 2008 18.32 by dmauri
Filed under: ,

Comments

# re: Un Quiz prima di partire: Think Set Oriented!

giovedì 10 aprile 2008 12.40 by AlessandroD

Posto 2 soluzioni, per aprire una parentesi sulle "performance".

Allora, prima cosa aggiorno le date (perché hai usato degli int per rappresentare le date?) a null con un valore di comodo che mi permette di scrivere query più efficienti:

update dbo.S1 set DataFine= 99990101 where DataFine is null

update dbo.S2 set DataFine= 99990101 where DataFine is null

go

Poi crea un paio di indici sulle due tabelle per via di quella parentesi che dicevo:

create index idxI on dbo.S1 (DataInizio)

create index idxF on dbo.S1 (DataFine)

create index idxI on dbo.S2 (DataInizio)

create index idxF on dbo.S2 (DataFine)

go

La mia prima soluzione è:

;with

ElencoDate as (

select row_number() over (order by Data) as Prog, Data from (

select DataInizio as Data from dbo.S1 union

select DataFine from dbo.S1 union

select DataInizio from dbo.S2 union

select DataFine from dbo.S2

) d

),

NuoviIntervalli as (

select i.Data as DataInizio, f.Data as DataFine

from

ElencoDate i inner join ElencoDate f

on f.Prog= i.Prog + 1

)

select i.DataInizio, nullif (i.DataFine, '99990101') as DataFine, ind.Indirizzo, t.Telefono

from

NuoviIntervalli i left join dbo.S1 t

on t.DataInizio < i.DataFine and t.DataFine > i.DataInizio

left join dbo.S2 ind

on ind.DataInizio < i.DataFine and ind.DataFine > i.DataInizio

order by i.DataInizio

Mentre la seconda soluzione è:

;with

ElencoDate as (

select Data from (

select DataInizio as Data from dbo.S1 union

select DataFine from dbo.S1 union

select DataInizio from dbo.S2 union

select DataFine from dbo.S2

) d

),

NuoviIntervalli as (

select i.Data as DataInizio, f.Data as DataFine

from

ElencoDate i cross apply (

select top 1 Data from ElencoDate where Data > i.Data order by Data

) f

)

select i.DataInizio, nullif (i.DataFine, '99990101') as DataFine, ind.Indirizzo, t.Telefono

from

NuoviIntervalli i left join dbo.S1 t

on t.DataInizio < i.DataFine and t.DataFine > i.DataInizio

left join dbo.S2 ind

on ind.DataInizio < i.DataFine and ind.DataFine > i.DataInizio

order by i.DataInizio

La logica è la stessa, ma l'implentazione cambia.

Qui da me la prima query presente nel piano di esecuzione solo degli index scan con un totale di 43 logical reads (attivando le statistiche con set stastics io on).

La seconda invece nel piano presenta dei bei index seek ma il totale delle logical reads sale a 56.

Quindi mi vien tanta voglia di dire (anche perché risultati analoghi li ho riscontrati anche in altre circostanze) che la ricerca di scrivere query che presentino il più possibile dei seek sugli indici non è sempre cosa buona e giusta, perché alla fin fine sono le operazioni di IO che comandano e l'obiettivo dovrebbe essere quello di tenerle il più basse possibile, con ovvimente un occhio al piano di esecuzione, ma non solo a questo.

Che ne pensi?

# re: Un Quiz prima di partire: Think Set Oriented!

giovedì 10 aprile 2008 15.15 by dmauri

Ciao Alessandro

non dico nulla sulla soluzione, vediamo che altre soluzioni arrivano, per quanto riguarda la domanda sugli int come data. Ho girato pari pari la richiesta cosi com'è arrivata a me, con la condizione che si trova dal cliente. Cmq direi ad occhio e croce che può essere una soluzione di DataWareHouse dove è intelligente avere come chiave surrogata della dimensione tempo non un numero qualsiasi ma il numero che esce dalla sequenza aaaammgg (anno, mese, giorno) che è sempre monotonica crescente e quindi può essere utilizzata al posto di una identity (ad esempio)

# re: Un Quiz prima di partire: Think Set Oriented!

giovedì 10 aprile 2008 18.50 by marcellus

Ecco la mia soluzione:

create function f_GetNextInterval(@DataInizo int, @DataFine int)

returns table as return

with ds as(

select top 2 DataInizio D from S1 where DataInizio>@DataFine

union select top 2 DataFine D from S1 where DataFine>@DataFine

union select top 2 DataInizio from S2 where DataInizio>@DataFine

union select top 2 DataFine from S2 where DataFine>@DataFine

), ds2 as(

Select top 2 * from Ds order by D

)

select DataInizio=min(D),DataFine=max(D) from ds2

go

with r as(

select * from f_GetNextInterval(0,0)

union all

select n.DataInizio, case n.DataFine when n.DataInizio then 99991231 else n.DataFine end

from r

cross apply f_GetNextInterval(r.DataInizio,r.DataInizio) n

where n.DataInizio is not null

)

 select r.*, S2.Indirizzo, S1.Telefono from r

 left outer join S1

 on r.DataInizio<isnull(S1.DataFine, 99991231) and r.DataFine>S1.DataInizio

 left outer join S2

 on r.DataInizio<isnull(S2.DataFine, 99991231) and r.DataFine>S2.DataInizio

 option(maxrecursion 0)

go

marc.

# re: Un Quiz prima di partire: Think Set Oriented!

venerdì 11 aprile 2008 10.42 by marcellus

...mmm... in tutte le union basta un top 1, il top 2 non serve a nulla.

marc.

# Quiz sui dati temporali - la soluzione su SQL Magazine

mercoledì 1 ottobre 2008 13.01 by Impedance Mismatch

Tempo fa, prima di partire per l&#39;MVP Summit 2008, proposi un quiz sulla risoluzione delle problematiche

(C) 2007 User Group Italiano di SQL Server