Requete avec row dépendant du précédent

Version imprimable

06/11/2019, 22h04
jolann69

Requete avec row dépendant du précédent

Bonsoir à tous,

J'ai une table avec les données suivantes :

Id Object | Start | End

A | 06/11/2019 09:00 | 06/11/2019 09:15
A |06/11/2019 09:05 | 06/11/2019 09:20
A |06/11/2019 09:10 | 06/11/2019 09:25
A |06/11/2019 09:15 | 06/11/2019 09:30
B | 06/11/2019 09:05 | 06/11/2019 09:20
B |06/11/2019 09:10 | 06/11/2019 09:25
B |06/11/2019 09:15 | 06/11/2019 09:30
B |06/11/2019 09:35 | 06/11/2019 09:50

Et je voudrais une requête qui me retourne:

A | 06/11/2019 09:00 | 06/11/2019 09:15
A |06/11/2019 09:15 | 06/11/2019 09:30
B | 06/11/2019 09:05 | 06/11/2019 09:20
B |06/11/2019 09:35 | 06/11/2019 09:50

Pour chaque Objet, je veux récupérer la plus petite date de début, puis ensuite la plus petite date de début qui est supérieure ou égale à la date de fin de la précédente.
Pour l'instant je le fais avec un curseur, mais c'est très moche et pas performant. Je sais qu'il est possible de le faire avec une requête mais je ne sais pas trop comment m'y prendre.

Merci d'avance pour votre aide.
Thomas
06/11/2019, 22h39
Invité

regarde les fonctions LEAD ou LAG.
06/11/2019, 22h49
jolann69

J'ai regardé la doc de LEAD et LAG avec la commande OVER, mais je t'avoue que je n'ai pas compris le principe. Car moi je ne dois pas juste parcourir le suivant, mais les N suivants jusqu'a trouvé le prochain dont la date de début est supérieure à la date du fin du précédent choisi.

1 pièce(s) jointe(s)

Code:

1
2
3
4
5
6
7
8
9
10
SELECT T1.*, T2.DEBUT, T2.FIN 
FROM   UNE_TABLE AS T1
       JOIN UNE_TABLE AS T2 ON  T1.ID_OBJET = T2.ID_OBJET 
                            AND T2.DEBUT >= T1.FIN
                            AND T1.FIN < T2.FIN 
WHERE NOT EXISTS(SELECT *
                 FROM   UNE_TABLE AS T
                 WHERE  T.ID_OBJET = T1.ID_OBJET
                 AND  T.DEBUT < T1.DEBUT
                 AND  T.DEBUT < T1.FIN)

Apprenez le SQL ! Mon livre est fait pour cela :
Pièce jointe 515669

A +

2 pièce(s) jointe(s)

Bonjour,
Merci pour ta réponse. J'ai essayé ce que tu as mis avec un jeu d'essai :

Code:

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
 
DECLARE @Planning_Proposal_Temp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
INSERT INTO @Planning_Proposal_Temp(object, dateFrom, dateTo ) 
SELECT * 
  FROM (VALUES 
       ('A', '2019-11-06 09:00:00', '2019-11-06 09:15:00'),
       ('A', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('A', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('A', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
	   ('A', '2019-11-06 09:20:00', '2019-11-06 09:35:00'),
	   ('A', '2019-11-06 09:25:00', '2019-11-06 09:40:00'),
	   ('A', '2019-11-06 09:30:00', '2019-11-06 09:45:00'),
	   ('A', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('A', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('A', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
       ('B', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('B', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('B', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
       ('B', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('B', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('B', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
	   ('B', '2019-11-06 10:00:00', '2019-11-06 10:15:00'),
	   ('B', '2019-11-06 10:05:00', '2019-11-06 10:20:00'),
	   ('B', '2019-11-06 10:10:00', '2019-11-06 10:25:00'),
	   ('B', '2019-11-06 10:15:00', '2019-11-06 10:30:00'),
	   ('C', '2019-11-06 12:00:00', '2019-11-06 12:20:00'),
	   ('C', '2019-11-06 12:40:00', '2019-11-06 13:00:00'),
	   ('C', '2019-11-06 12:45:00', '2019-11-06 13:05:00'),
	   ('C', '2019-11-06 12:50:00', '2019-11-06 13:10:00'),
	   ('C', '2019-11-06 12:55:00', '2019-11-06 13:15:00'),
	   ('C', '2019-11-06 13:00:00', '2019-11-06 13:20:00'),
	   ('C', '2019-11-06 13:05:00', '2019-11-06 13:25:00'),
	   ('C', '2019-11-06 13:10:00', '2019-11-06 13:30:00'),
	   ('C', '2019-11-06 13:15:00', '2019-11-06 13:35:00'),
	   ('C', '2019-11-06 13:20:00', '2019-11-06 13:40:00')
	   ) T(IdObject, StartDate, EndDate)
 
 
SELECT T1.*, T2.dateFrom, T2.dateTo 
FROM   @Planning_Proposal_Temp AS T1
       JOIN @Planning_Proposal_Temp AS T2 ON  T1.object = T2.object 
                            AND T2.dateFrom >= T1.dateTo
                            AND T1.dateTo < T2.dateTo 
WHERE NOT EXISTS(SELECT *
                 FROM   @Planning_Proposal_Temp AS T
                 WHERE  T.object = T1.object
                 AND  T.dateFrom < T1.dateFrom
                 AND  T.dateFrom < T1.dateTo)

Et ca me retourne le résultat suivant :
Pièce jointe 515946

Voici le code du Curseur que j'utilise et que je désire remplacer avec le même jeu de données:

Code:

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
111
112
 
DECLARE @Planning_Proposal_Temp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
INSERT INTO @Planning_Proposal_Temp(object, dateFrom, dateTo ) 
SELECT * 
  FROM (VALUES 
       ('A', '2019-11-06 09:00:00', '2019-11-06 09:15:00'),
       ('A', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('A', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('A', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
	   ('A', '2019-11-06 09:20:00', '2019-11-06 09:35:00'),
	   ('A', '2019-11-06 09:25:00', '2019-11-06 09:40:00'),
	   ('A', '2019-11-06 09:30:00', '2019-11-06 09:45:00'),
	   ('A', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('A', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('A', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
       ('B', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('B', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('B', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
       ('B', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('B', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('B', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
	   ('B', '2019-11-06 10:00:00', '2019-11-06 10:15:00'),
	   ('B', '2019-11-06 10:05:00', '2019-11-06 10:20:00'),
	   ('B', '2019-11-06 10:10:00', '2019-11-06 10:25:00'),
	   ('B', '2019-11-06 10:15:00', '2019-11-06 10:30:00'),
	   ('C', '2019-11-06 12:00:00', '2019-11-06 12:20:00'),
	   ('C', '2019-11-06 12:40:00', '2019-11-06 13:00:00'),
	   ('C', '2019-11-06 12:45:00', '2019-11-06 13:05:00'),
	   ('C', '2019-11-06 12:50:00', '2019-11-06 13:10:00'),
	   ('C', '2019-11-06 12:55:00', '2019-11-06 13:15:00'),
	   ('C', '2019-11-06 13:00:00', '2019-11-06 13:20:00'),
	   ('C', '2019-11-06 13:05:00', '2019-11-06 13:25:00'),
	   ('C', '2019-11-06 13:10:00', '2019-11-06 13:30:00'),
	   ('C', '2019-11-06 13:15:00', '2019-11-06 13:35:00'),
	   ('C', '2019-11-06 13:20:00', '2019-11-06 13:40:00')
	   ) T(IdObject, StartDate, EndDate)
 
 
	   DECLARE @dateFrom_old datetime;
		DECLARE @dateTo_old datetime;
		DECLARE @object_old varchar(10);
 
 
		DECLARE @dateFrom_new datetime;
		DECLARE @dateTo_new datetime;
		DECLARE @object_new varchar(10);
		DECLARE @Planning_Proposal_Temp_Grouped AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
 
 
DECLARE insertedDataCursor CURSOR 
			FOR SELECT *
				FROM @Planning_Proposal_Temp
				ORDER BY Object, dateFrom
 
 
			OPEN insertedDataCursor 
 
 
			FETCH NEXT FROM insertedDataCursor INTO @object_new, 
													@dateFrom_new, 
													@dateTo_new
 
 
 
			WHILE @@FETCH_STATUS = 0
			BEGIN
 
				IF	@dateFrom_old IS NOT NULL AND
					@dateTo_old IS NOT NULL AND
					@object_old IS NOT NULL
 
				BEGIN
 
					IF ((@object_old <> @object_new) OR (@dateFrom_new >= @dateTo_old))
					BEGIN
						INSERT INTO @Planning_Proposal_Temp_Grouped VALUES( @object_new,
																			@dateFrom_new, 
																			@dateTo_new
																			)
 
					SET @dateFrom_old = @dateFrom_new;
					SET @dateTo_old = @dateTo_new
					SET @object_old = @object_new
 
 
					END
 
				END
 
				ELSE
				BEGIN
					INSERT INTO @Planning_Proposal_Temp_Grouped VALUES( @object_new,
																		@dateFrom_new, 
																		@dateTo_new 
																		)
 
				SET @dateFrom_old = @dateFrom_new;
				SET @dateTo_old = @dateTo_new
				SET @object_old = @object_new
				END
 
 
 
				FETCH NEXT FROM insertedDataCursor INTO @object_new, @dateFrom_new, @dateTo_new
 
 
			END
 
			CLOSE insertedDataCursor 
			DEALLOCATE insertedDataCursor
 
			SELECT * FROM @Planning_Proposal_Temp_Grouped

Et le résultat que je souhaite :

Pièce jointe 515954

Merci encore pour votre aide.

bonjour,

Vous pouvez essayer comme ceci :

Code:

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
 
 
WITH Tmp AS (
	SELECT  PPT.object, PPT.DateFrom, PPT.DateTo, PPT2.DateFrom AS FromSuiv, PPT2.DateTo AS ToSuiv, ROW_NUMBER() OVER(PARTITION BY PPT.object ORDER BY PPT.DateFrom) AS RN
	FROM @Planning_Proposal_Temp AS PPT
	LEFT JOIN @Planning_Proposal_Temp AS PPT2
		ON PPT2.object = PPT.object
		AND PPT2.dateFrom>= PPT.dateTo
	LEFT JOIN @Planning_Proposal_Temp AS PPT3
		ON PPT3.object = PPT.object
		AND PPT3.dateFrom >= PPT.dateTo
		AND PPT3.dateFrom < PPT2.dateFrom
	WHERE PPT3.object IS NULL 
) 
, CTE AS (
	SELECT object, DateFrom, DateTo, FromSuiv, ToSuiv
	FROM Tmp
	WHERE RN = 1
 
	UNION ALL 
 
	SELECT tmp.object, tmp.DateFrom, tmp.DateTo, tmp.FromSuiv, tmp.ToSuiv
	FROM CTE
	INNER JOIN Tmp
		ON tmp.object = cte.object
		AND tmp.DateFrom = cte.FromSuiv
		AND tmp.DateTo = cte.ToSuiv
)
SELECT object, DateFrom, DateTo
FROM CTE
ORDER BY object, dateFrom

1 pièce(s) jointe(s)

J'ai essayé ton code que j'ai du adapter un peu car bizarrement mon SQL Manager n'aimait pas les WITH, et cela ne me retourne que le premier résultat pour chacun des objets :

Code:

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
 
DECLARE @Planning_Proposal_Temp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
INSERT INTO @Planning_Proposal_Temp(object, dateFrom, dateTo ) 
SELECT * 
  FROM (VALUES 
       ('A', '2019-11-06 09:00:00', '2019-11-06 09:15:00'),
       ('A', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('A', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('A', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
	   ('A', '2019-11-06 09:20:00', '2019-11-06 09:35:00'),
	   ('A', '2019-11-06 09:25:00', '2019-11-06 09:40:00'),
	   ('A', '2019-11-06 09:30:00', '2019-11-06 09:45:00'),
	   ('A', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('A', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('A', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
       ('B', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('B', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('B', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
       ('B', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('B', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('B', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
	   ('B', '2019-11-06 10:00:00', '2019-11-06 10:15:00'),
	   ('B', '2019-11-06 10:05:00', '2019-11-06 10:20:00'),
	   ('B', '2019-11-06 10:10:00', '2019-11-06 10:25:00'),
	   ('B', '2019-11-06 10:15:00', '2019-11-06 10:30:00'),
	   ('C', '2019-11-06 12:00:00', '2019-11-06 12:20:00'),
	   ('C', '2019-11-06 12:40:00', '2019-11-06 13:00:00'),
	   ('C', '2019-11-06 12:45:00', '2019-11-06 13:05:00'),
	   ('C', '2019-11-06 12:50:00', '2019-11-06 13:10:00'),
	   ('C', '2019-11-06 12:55:00', '2019-11-06 13:15:00'),
	   ('C', '2019-11-06 13:00:00', '2019-11-06 13:20:00'),
	   ('C', '2019-11-06 13:05:00', '2019-11-06 13:25:00'),
	   ('C', '2019-11-06 13:10:00', '2019-11-06 13:30:00'),
	   ('C', '2019-11-06 13:15:00', '2019-11-06 13:35:00'),
	   ('C', '2019-11-06 13:20:00', '2019-11-06 13:40:00')
	   ) T(IdObject, StartDate, EndDate)
 
DECLARE @Tmp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2, FromSuiv datetime2, ToSuiv datetime2, RN bigint)
INSERT INTO @Tmp(object, dateFrom, dateTo,FromSuiv,ToSuiv,RN )
	SELECT  PPT.object, PPT.DateFrom, PPT.DateTo, PPT2.DateFrom AS FromSuiv, PPT2.DateTo AS ToSuiv, ROW_NUMBER() OVER(PARTITION BY PPT.object ORDER BY PPT.DateFrom) AS RN
	FROM @Planning_Proposal_Temp AS PPT
	LEFT JOIN @Planning_Proposal_Temp AS PPT2
		ON PPT2.object = PPT.object
		AND PPT2.dateFrom>= PPT.dateTo
	LEFT JOIN @Planning_Proposal_Temp AS PPT3
		ON PPT3.object = PPT.object
		AND PPT3.dateFrom >= PPT.dateTo
		AND PPT3.dateFrom < PPT2.dateFrom
	WHERE PPT3.object IS NULL 
 
DECLARE @CTE AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2, FromSuiv datetime2, ToSuiv datetime2)
INSERT INTO @CTE(object, dateFrom, dateTo,FromSuiv, ToSuiv)
	SELECT object, DateFrom, DateTo, FromSuiv, ToSuiv
	FROM @Tmp
	WHERE RN = 1
	UNION ALL 
	SELECT tmp.object, tmp.DateFrom, tmp.DateTo, tmp.FromSuiv, tmp.ToSuiv
	FROM @CTE cte
	INNER JOIN @Tmp tmp
		ON tmp.object = cte.object
		AND tmp.DateFrom = tmp.FromSuiv
		AND tmp.DateTo = cte.ToSuiv
 
 
SELECT object, DateFrom, DateTo
FROM @CTE
ORDER BY object, dateFrom

Résultat :

Pièce jointe 516001

08/11/2019, 14h23
aieeeuuuuu

on ne peut pas adapter le WITH comme ça, car il s'agit d'une CTE récursive.

essayez d'ajouter un ; devant le WITH (enfin, à la fin de l'instruction qui le précède en fait ;) )
08/11/2019, 16h14
jolann69

Effectivement ca marche Nickel !
Je ne connaissais pas le WITH et la récursivité CTE, il faut que je me penche dessus car je vais en avoir besoin pour autre chose !

Merci beaucoup c'est résolu pour moi !
08/11/2019, 22h17
jolann69

Bon j'ai parlé un peu vite. Cette méthode marche, mais en terme de perf c'est la cata par rapport au curseur. On est plus de 100x plus lent !
12/11/2019, 10h08
aieeeuuuuu

quelles est la structure de la vraie table ?

quelle est sa volumétrie ?

essayez en indexant :

Code:

CREATE UNIQUE INDEX ix_T ON #LaTable(object, DateFrom ) INCLUDE (DateTo);

… dommage que SQL-Server ne dispose pas (encore ?) de match_recognize, c'est typiquement le genre de problème pour lequel c'est très amusant à utiliser :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 with data as ( ... ) select id, start_date, end_date from data match_recognize ( partition by obj_id order by start_dat, end_dat measures obj_id as id, start_dat as start_date, end_dat as end_date, classifier() as cls all rows per match -- le pattern == (soit une ligne satisfaisant le critère, soit n'importe quelle ligne) 0 ou N fois pattern( (NEXT_ROW | ANY_ROW)* ) define -- la première OU la suivante (à garder) est celle dont la start_dat >= end_dat de la dernière gardée mais sans tenir compte de la ligne en cours (d'où l'offset 1) NEXT_ROW as prev(obj_id) is null OR start_dat >= last(NEXT_ROW .end_dat,1) ) where cls = 'NEXT_ROW' ;
ID START_DATE END_DATE

A 06/11/2019 09:00 06/11/2019 09:15

A 06/11/2019 09:15 06/11/2019 09:30

A 06/11/2019 09:30 06/11/2019 09:45

A 06/11/2019 09:45 06/11/2019 10:00

B 06/11/2019 09:05 06/11/2019 09:20

B 06/11/2019 09:35 06/11/2019 09:50

B 06/11/2019 10:00 06/11/2019 10:15

B 06/11/2019 10:15 06/11/2019 10:30

C 06/11/2019 12:00 06/11/2019 12:20

C 06/11/2019 12:40 06/11/2019 13:00

C 06/11/2019 13:00 06/11/2019 13:20

C 06/11/2019 13:20 06/11/2019 13:40

le gros + : un seul parcours des données …

ID	START_DATE	END_DATE
A	06/11/2019 09:00	06/11/2019 09:15
A	06/11/2019 09:15	06/11/2019 09:30
A	06/11/2019 09:30	06/11/2019 09:45
A	06/11/2019 09:45	06/11/2019 10:00
B	06/11/2019 09:05	06/11/2019 09:20
B	06/11/2019 09:35	06/11/2019 09:50
B	06/11/2019 10:00	06/11/2019 10:15
B	06/11/2019 10:15	06/11/2019 10:30
C	06/11/2019 12:00	06/11/2019 12:20
C	06/11/2019 12:40	06/11/2019 13:00
C	06/11/2019 13:00	06/11/2019 13:20
C	06/11/2019 13:20	06/11/2019 13:40