Splitter des lignes avec périodes qui se chevauchent en plusieurs lignes

**Sankasssss** · 16/11/2018, 12h12

Bonjour,

Je cherche à splitter les lignes qui ont des périodes qui se chevauchent pour un même id avec la version la plus récente en prioritaire.

J'ai deux tables, une courante contenant la version la plus récente et une d'historique contenant toutes les anciennes versions.

Voici la déclaration de ces tables pour test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
IF OBJECT_ID('tempdb..#T_DATA_DAT') IS NOT NULL DROP TABLE #T_DATA_DAT
CREATE TABLE #T_DATA_DAT (
	DAT_ID int NOT NULL, -- IDENTITY(-2147483648,1) 
	DAT_START datetime2(0) NOT NULL,
	DAT_END datetime2(0) NOT NULL,
	DAT_VERSION smallint NOT NULL,
	DAT_DATA1 int NULL,
	DAT_DATA2 decimal(8, 2) NULL,
	DAT_DATA3 decimal(4, 2) NOT NULL,
	DAT_DATA4 decimal(4, 2) NOT NULL,
	DAT_CREATED_ON datetime2(3) NOT NULL,
	CONSTRAINT PK_T_DATA_DAT PRIMARY KEY 
		(
			DAT_ID ASC
		)
);
GO

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
IF OBJECT_ID('tempdb..#T_DATA_HISTORY_DAH') IS NOT NULL DROP TABLE #T_DATA_HISTORY_DAH;
GO
 
CREATE TABLE #T_DATA_HISTORY_DAH (
	DAH_ID int NOT NULL,
	DAH_START datetime2(0) NOT NULL,
	DAH_END datetime2(0) NOT NULL,
	DAH_VERSION smallint NOT NULL,
	DAH_DATA1 int NULL,
	DAH_DATA2 decimal(8, 2) NULL,
	DAH_DATA3 decimal(4, 2) NOT NULL,
	DAH_DATA4 decimal(4, 2) NOT NULL,
	DAH_CREATED_ON datetime2(3) NOT NULL,
	CONSTRAINT PK_T_DATA_HISTORY_DAH PRIMARY KEY 
		(
			DAH_ID, DAH_VERSION
		)
);
GO

J'ai fait quelques jeux de données de test. Devant ces jeux de donnée j'ai mis un commentaire pour essayer de rendre visuel ce que je souhaite obtenir.
La première ligne de ce commentaire est la ligne du temps composée des différents mois.
Les lignes suivantes sont les jeux de données, le numéro est le numéro de version et les signes ( - , ~ , #) la composant montre les différences de data.
Ce qui se trouve après la ligne RESULT est ce que je souhaite obtenir.

Jeu de données n°1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* DATA SET ID 1
|====JAN====|====FEV====|====MAR====|====AVR====|====MAI====|====JUIN===|====JUIL===|
             <-1-->      <----2---->   <--4-->   <--5--->    <----7---->
                                   <--3-->          <~~6~~>
----------------------------------RESULT---------------------------------------------
             <---->      <------------------->   <-><~~~~~>  <--------->       
                                    OR     
             <---->      <---------><-><----->   <-><~~~~~>  <--------->       
*/
INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-02-01', '2018-02-15', 1, 1000, 100, 10, 1, '2018-02-10 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-03-01', '2018-03-31', 2, 1000, 100, 10, 1, '2018-03-01 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-03-31', '2018-04-15', 3, 1000, 100, 10, 1, '2018-03-15 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-04-10', '2018-04-20', 4, 1000, 100, 10, 1, '2018-04-01 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-05-01', '2018-05-20', 5, 1000, 100, 10, 1, '2018-05-01 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (1 , '2018-05-10', '2018-05-25', 6,  500, 100, 10, 1, '2018-05-05 15:00:00.000');
INSERT INTO #T_DATA_DAT         VALUES (1 , '2018-06-01', '2018-06-30', 7, 1000, 100, 10, 1, '2018-06-01 15:00:00.000');

Donc ici on peut voir qu'il y a deux possibilités de résultat, l'important est de garder les valeurs. Si toutes les valeurs sont les mêmes, la ligne peut-être fusionnée comme la ligne 7 mais ce n'est pas du tout une obligation.

Jeu de données n°2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* DATA SET ID 2
|====JAN====|====FEV====|====MAR====|====AVR====|====MAI====|====JUIN===|====JUIL===|
 <--------------2------------------>             <----5--->  <-----7---> <----8---->
    <~3~~>    <~1~~>   <~~4~~>                                   <~6~>      <~9~>
----------------------------------RESULT---------------------------------------------
 <-><~~~~><-----------><~~~~~><---->             <-------->  <---------> <-><~~~><->      
*/
INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-02-03', '2018-02-15', 1,  500, 100, 10, 1, '2018-01-01 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-01-01', '2018-03-31', 2,  500, 100, 10, 1, '2018-01-02 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-01-10', '2018-01-20', 3, 1000, 100, 10, 1, '2018-01-13 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-02-27', '2018-03-15', 4,  500, 100, 10, 1, '2018-02-04 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-05-01', '2018-05-30', 5, 1000, 100, 10, 1, '2018-03-01 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-06-12', '2018-06-20', 6,  500, 100, 10, 1, '2018-03-20 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-06-01', '2018-06-30', 7, 1000, 100, 10, 1, '2018-06-20 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (2 , '2018-07-01', '2018-07-30', 8, 1000, 100, 10, 1, '2018-06-25 15:00:00.000');
INSERT INTO #T_DATA_DAT         VALUES (2 , '2018-07-10', '2018-07-20', 9, 500, 100, 10, 1, '2018-07-01 15:00:00.000');

Ici on peut voir que les datas qui sont englobé dans une version plus récente disparaisse (Version 1 et 6).
Les datas qui englobent des versions plus récentes sont décomposés. C'est le cas de la version 2 qui engobe la 3 et la 4 ainsi que de la version 8 qui englobe la 9.

Jeu de données n°3 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/* DATA SET ID 3
|====JAN====|====FEV====|====MAR====|====AVR====|====MAI====|====JUIN===|====JUIL===|
 <--------------2------------------------------------------>             
    <~~3~~>   <~~~~~~5~~~~~~>   <~~~~~6~~~~>           <~~~~~1~~~~>  	                                 
       <####4###>      <#####7#####>
----------------------------------RESULT---------------------------------------------
 <-><~><#####><~~~~~~~><###########><~~~~~~><--------------><~~~~~>
*/
INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-05-17', '2018-06-15', 1,  500, 100, 10, 1, '2018-01-01 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-01-01', '2018-05-31', 2, 1000, 100, 10, 1, '2018-01-02 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-01-10', '2018-01-20', 3,  500, 100, 10, 1, '2018-01-05 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-01-15', '2018-02-12', 4,   20, 100, 10, 1, '2018-01-10 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-02-07', '2018-03-12', 5,  500, 100, 10, 1, '2018-02-01 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (3 , '2018-03-20', '2018-04-17', 6,  500, 100, 10, 1, '2018-02-20 15:00:00.000');
INSERT INTO #T_DATA_DAT         VALUES (3 , '2018-02-25', '2018-03-31', 7,   20, 100, 10, 1, '2018-02-21 15:00:00.000');

Et voila, je pense que c'est assez parlant.

J'ai bien sûr essayé de résoudre ça mais ma logique ensembliste n'est pas des meilleurs. Je me doute qu'il faudra passer par des CTE et de la récursivité, surtout pour le jeu de données n°3 mais je coince complètement.

Jusqu'ici j'ai ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
 
WITH AllData AS (
	SELECT *
	FROM #T_DATA_DAT
	UNION ALL 
	SELECT *
	FROM #T_DATA_HISTORY_DAH
)
 
, DataFilterIncludesOldVersion AS (
	SELECT 
		*
	FROM AllData d1 
	WHERE NOT EXISTS (
		SELECT 1
		FROM AllData d2
		WHERE d1.DAT_ID = d2.DAT_ID 
			AND d1.DAT_START >= d2.DAT_START 
			AND d1.DAT_END <= d2.DAT_END 
			AND d1.DAT_VERSION < d2.DAT_VERSION
	)
)
 
SELECT *
FROM DataFilterIncludesOldVersion d1
ORDER BY d1.DAT_ID, d1.DAT_CREATED_ON

Qui ne fait qu'agréger les deux sources et enlever les versions anciennes qui sont englobées dans une version plus récente.

Si je ne trouve pas de solution propre je créerai une table parallèle que je reconstituerai chaque nuit par un batch mais j'aimerais vraiment éviter ça si c'est possible.

Merci d'avance pour votre aide.

**SQLpro** · 16/11/2018, 12h29

Il faut d'abord faire une agrégation des périodes. Jetez un œil sur l'article que j'ai écrit à ce sujet :
https://blog.developpez.com/sqlpro/p...alles_en_sql_1

A +

**Sankasssss** · 16/11/2018, 14h05

Bonjour Frédéric,

Merci pour ton intervention et pour tous tes tutos/ouvrages qui m'ont déjà maintes fois aidé. Ce sont des ressources très précieuses.

Je suis ok pour agréger les périodes mais en y réfléchissant bien c'est plus compliqué qu'il n'y parait.
Je ne peux malheureusement pas agréger les lignes contenant les mêmes données comme ça car je perdrais l'information du numéro de version et s'il existe une version entre, cette information est capitale pour définir le résultat.

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
/* DATA SET ID 4
|====JAN====|====FEV====|====MAR====|====AVR====|====MAI====|====JUIN===|====JUIL===|
 <--------------1------------------------------------------>             
                     <--------------3------------------------------------------>   	                                 
            <~~~~~~2~~~~~~>     
-------------------------------------------------------------------------------------
 <---------><~~~~~~~~~~~~~><--------------------------------------------------->
*/
 
INSERT INTO #T_DATA_HISTORY_DAH VALUES (4 , '2018-01-01', '2018-05-31', 1,  500, 100, 10, 1, '2018-02-01 15:00:00.000');
INSERT INTO #T_DATA_HISTORY_DAH VALUES (4 , '2018-02-20', '2018-07-20', 2,   20, 100, 10, 1, '2018-02-20 15:00:00.000');
INSERT INTO #T_DATA_DAT         VALUES (4 , '2018-02-01', '2018-03-10', 3,  500, 100, 10, 1, '2018-02-21 15:00:00.000');
GO;

Je ne peux en aucun cas agréger la version 1 et 3 même si elles contiennent les même données car je perdrais ce numéro de version pour savoir que la version 2 doit venir splitter ce résultat.

**aieeeuuuuu** · 16/11/2018, 18h40

Bonjour

Essayez comme ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
WITH LaTable AS (
    SELECT * FROM #T_DATA_DAT
    UNION ALL
    SELECT * FROM #T_DATA_HISTORY_DAH
)
,   ListeDates AS (
    SELECT DAT_START FROM LaTable
    UNION ALL
    SELECT DAT_END FROM LaTable
)
,   Periodes AS (
    SELECT 
		  DAT_START AS Debut
	   ,	  LEAD(DAT_START) OVER(ORDER BY DAT_START) AS Fin
    FROM	  ListeDates
) 
,   Rattachement AS(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Debut ORDER BY DAT_VERSION DESC) AS RN
    FROM Periodes		AS P
    INNER JOIN LaTable	AS T
	   ON P.Debut >=  T.DAT_START 
	   AND P.Debut < T.DAT_END  
)
,   Bornes AS (
    SELECT *
	   , CASE 
		  WHEN LAG(Fin) OVER(ORDER BY Debut) <> Debut 
		  OR LAG(DAT_VERSION) OVER(ORDER BY Debut) <> DAT_VERSION 
			 THEN 1 
		  ELSE 0 
		END AS Nouveau
    FROM Rattachement
    WHERE RN = 1
)
, Groupes AS (
    SELECT *, SUM(Nouveau) OVER(ORDER BY Debut) AS grp
    FROM Bornes
)
SELECT 
	   MIN(Debut) AS DAT_START
    ,   MAX(Fin) AS DAT_END
    ,   DAT_VERSION
    ,   DAT_DATA1
    ,   DAT_DATA2
    ,   DAT_DATA3
    ,   DAT_DATA4
    ,   DAT_CREATED_ON
FROM Groupes
WHERE Fin IS NOT NULL
GROUP BY 
	   grp
    ,   DAT_VERSION
    ,   DAT_DATA1
    ,   DAT_DATA2
    ,   DAT_DATA3
    ,   DAT_DATA4
    ,   DAT_CREATED_ON

**Sankasssss** · 20/11/2018, 14h25

Bonjour aieeeuuuuu,

Je vais regarder pour essayer de comprendre votre solution et la modifier car malheureusement la fonction LEAD n'existe pas sur le 2008R2 (On devrait normalement upgrader nos serveurs l'année prochaine si mes collègues tiennent la planification).

Un tout grand merci.

**aieeeuuuuu** · 20/11/2018, 16h24

pour les version avant 2012, j'indique dans ce post comment émuler LEAD et LAG

**aieeeuuuuu** · 22/11/2018, 15h44

en version 2008R2, vous aurez aussi je pense un probléme avec la somme cumulée croissante (SUM(...) OVER(ORDER BY ...))

Du coup, ceci devrait fonctionner

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
;
WITH LaTable AS (
    SELECT * FROM #T_DATA_DAT
    UNION ALL
    SELECT * FROM #T_DATA_HISTORY_DAH
)
,   ListeDates AS (
    SELECT DAT_START, ROW_NUMBER() OVER(ORDER BY DAT_START) AS rn
    FROM (
	   SELECT DAT_START FROM LaTable
	   UNION ALL
	   SELECT DAT_END FROM LaTable
    ) AS T
)
,   Periodes AS (
    SELECT 
		  DAT_START AS Debut
	   ,	  --LEAD(DAT_START) OVER(ORDER BY DAT_START) AS Fin
 
		CASE 
			WHEN rn%2 = 1 THEN 
				MAX(CASE WHEN rn%2 = 0 THEN DAT_START END) OVER (PARTITION BY (rn-1) / 2)
			ELSE 
				MAX(CASE WHEN rn%2 = 1 THEN DAT_START END) OVER (PARTITION BY (rn) / 2)
		END AS Fin
    FROM	  ListeDates
) 
,   Rattachement AS(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Debut ORDER BY DAT_VERSION DESC) AS RN, DENSE_RANK() OVER( ORDER BY Debut ) AS RN2
    FROM Periodes		AS P
    INNER JOIN LaTable	AS T
	   ON P.Debut >=  T.DAT_START 
	   AND P.Debut < T.DAT_END  
)
,   Bornes AS (
    SELECT *
	   , CASE 
		  WHEN 
		--		LAG(Fin) OVER(ORDER BY Debut) 
				  CASE 
					  WHEN rn2%2 = 1 THEN 
						  MAX(CASE WHEN rn2%2 = 0 THEN Fin END) OVER (PARTITION BY (rn2) / 2)
					  ELSE 
						  MAX(CASE WHEN rn2%2 = 1 THEN Fin END) OVER (PARTITION BY (rn2+1) / 2)
				  END		
				<> Debut 
		  OR 
			 --LAG(DAT_VERSION) OVER(ORDER BY Debut) 
				  CASE 
					  WHEN rn2%2 = 1 THEN 
						  MAX(CASE WHEN rn2%2 = 0 THEN DAT_VERSION END) OVER (PARTITION BY (rn2) / 2)
					  ELSE 
						  MAX(CASE WHEN rn2%2 = 1 THEN DAT_VERSION END) OVER (PARTITION BY (rn2+1) / 2)
				  END			 
 
			 <> DAT_VERSION 
 
			 THEN 1 
		  ELSE 0 
		END AS Nouveau
    FROM Rattachement
    WHERE RN = 1
)
--SELECT * FROM Bornes
 
, Groupes AS (
    SELECT 
    	   A.Debut
    ,   A.Fin 
    ,   A.DAT_VERSION
    ,   A.DAT_DATA1
    ,   A.DAT_DATA2
    ,   A.DAT_DATA3
    ,   A.DAT_DATA4
    ,   A.DAT_CREATED_ON
    --,   SUM(Nouveau) OVER(ORDER BY Debut) AS grp
    , SUM(B.Nouveau) AS grp
    FROM Bornes AS A
    INNER JOIN Bornes AS B
	   ON B.Debut <= A.Debut
    GROUP BY 
    	   A.Debut
    ,   A.Fin 
    ,   A.DAT_VERSION
    ,   A.DAT_DATA1
    ,   A.DAT_DATA2
    ,   A.DAT_DATA3
    ,   A.DAT_DATA4
    ,   A.DAT_CREATED_ON
)
SELECT 
	   MIN(Debut) AS DAT_START
    ,   MAX(Fin) AS DAT_END
    ,   DAT_VERSION
    ,   DAT_DATA1
    ,   DAT_DATA2
    ,   DAT_DATA3
    ,   DAT_DATA4
    ,   DAT_CREATED_ON
FROM Groupes
WHERE Fin IS NOT NULL
GROUP BY 
	   grp
    ,   DAT_VERSION
    ,   DAT_DATA1
    ,   DAT_DATA2
    ,   DAT_DATA3
    ,   DAT_DATA4
    ,   DAT_CREATED_ON

**Sankasssss** · 23/11/2018, 14h54

Je suis occupé à regarder ta solution et je suis impressionné. Il va vraiment falloir que je travail ma logique ensembliste

Actuellement elle semble très bien fonctionner pour chaque jeu de données séparément mais pas quand on les mets toutes ensembles où la logique doit être appliquée par groupe de DAT_ID.

J'essaie de comprendre ce que tu as fait et j'essaierai de modifier pour l'ajuster.

Encore merci pour ton aide !

Splitter des lignes avec périodes qui se chevauchent en plusieurs lignes

Développement SQL Server

Discussions similaires

Partager

Partager