The SQLpro Blog

, Author

Ecco diverse forme di scrittura di una funzione di sovrapposizione degli intervalli per MS SQL Server.
Testare la sovrapposizione degli intervalli vi permette di sapere, per esempio, se due periodi si sovrappongono. Questo è talvolta essenziale nella gestione degli orari per evitare che la stessa persona debba essere presente in due luoghi diversi allo stesso tempo o che lo stesso luogo sia occupato allo stesso tempo…
C’è una funzione SQL prevista per questo di non-OVERLAPS nello standard SQL. Tuttavia la scrittura di questa funzione può influenzare notevolmente le prestazioni. Ecco perché vedremo diverse forme di tale scrittura.

AVVERTENZA: non sono considerati sovrapposti un intervallo che inizia o il precedente finito (uguaglianza dei limiti “fine” del precedente e “inizio” del prossimo). In effetti, per principio si considera che gli intervalli abbiano tutti dei limiti chiusi a sinistra e aperti a destra -].

Intervalli sovrapposti
Intervalli sovrapposti
Intervalli non sovrapposti
Intervalli non sovrapposti

0 – La formulazione del predicato normativo OVERLAPS è la seguente:

::=
::=
::= OVERLAPS
::=
::=

Regole generali
1) Se il valore del predicato del valore della riga 1 è il valore nullo o il valore del predicato del valore della riga 2 è il valore nullo, allora il risultato del predicato di sovrapposizione è Unknown e non vengono applicate ulteriori regole generali di questa
sottoclausola.
2) Sia D1 il valore del primo campo del predicato del valore della riga 1 e D2 il valore del primo campo del predicato del valore della riga 2.
3) Caso:
a) Se il tipo più specifico del secondo campo del predicato del valore della riga 1 è un tipo di dato datetime, allora
sia E1 il valore del secondo campo del predicato del valore della riga 1.
b) Se il tipo più specifico del secondo campo del predicato del valore della riga 1 è INTERVALLO, allora sia I1
il valore del secondo campo del predicato del valore della riga 1. Sia E1 = D1 + I1.
4) Se D1 è il valore nullo o se E1 inf D1, allora sia S1 = E1 e sia T1 = D1. Altrimenti, lasciamo S1 = D1 e lasciamo T1
= E1.
5) Caso:
a) Se il tipo più specifico del secondo campo del predicato del valore di riga 2 è un tipo di dati datetime, allora
lascia che E2 sia il valore del secondo campo del predicato del valore di riga 2.
b) Se il tipo più specifico del secondo campo del predicato del valore di riga 2 è INTERVAL, allora lasciamo che I2 sia
il valore del secondo campo del predicato del valore di riga 2. Sia E2 = D2 + I2.
6) Se D2 è il valore nullo o se E2 inf D2, allora S2 = E2 e T2 = D2. Altrimenti, S2 = D2 e T2
= E2.
7) Il risultato del predicato di sovrapposizione è il risultato della seguente espressione:

( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
OR
( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
OR
( S1 = S2 AND ( T1 T2 OR T1 = T2 )

NOTA : le terme inf étant le caractère :
<

Cette définition inclue une problématique qui est qu’un intervalle peut avoir une borne indéfinie, par exemple venir d’un passé lointain, comme de ne pas savoir quand il va se terminer et dans les deux cas, l’une des bornes peut être NULL (voire les deux).

Intervalli con limiti aperti sovrapposti
Intervalli con limiti aperti sovrapposti
Intervalli con limiti aperti non sovrapposti
Intervalli con limiti aperti non sovrapposti

1 – test di sovrapposizione per MS SQL Server:

CREATE FUNCTION dbo.f_overlaps (@i1_begin DATETIME2(0), @i1_end DATETIME2(0),
@i2_begin DATETIME2(0), @i2_end DATETIME2(0))
RETURNS BIT
AS
BEGIN
/* Nous suposons qu'aucun défaut de coordination de temps n'est possible (contrainte CHECK dans la table). Si ce n'est pas le cas :
IF @i1_begin > @i1_end RETURN NULL;
IF @i2_begin > @i2_end RETURN NULL;
*/
-- pas de chevauchement si l'un des intervalle est avant ou après l'autre :
IF @i2_begin >= @i1_end RETURN 0;
IF @i2_end <= @i1_begin RETURN 0;
-- sinon, chevauchement :
RETURN 1;
END;
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Microsoft SQL Server MVP

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *