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

**jolann69** · 06/11/2019, 22h04

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

Invité · 06/11/2019, 22h39

regarde les fonctions LEAD ou LAG.

**jolann69** · 06/11/2019, 22h49

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.

**SQLpro** · 07/11/2019, 11h23

Code :

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

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 :
Nom : SQL.jpg
Affichages : 332
Taille : 47,4 Ko

A +

**jolann69** · 08/11/2019, 09h45

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

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
 
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 :
Nom : res1.png
Affichages : 297
Taille : 30,2 Ko

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

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

Nom : res2.png
Affichages : 225
Taille : 8,3 Ko

Merci encore pour votre aide.

**aieeeuuuuu** · 08/11/2019, 10h40

bonjour,

Vous pouvez essayer 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
 
 
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

**jolann69** · 08/11/2019, 12h17

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 :

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

Nom : res1.png
Affichages : 222
Taille : 3,4 Ko

**aieeeuuuuu** · 08/11/2019, 14h23

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

)

**jolann69** · 08/11/2019, 16h14

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 !

**jolann69** · 08/11/2019, 22h17

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 !

**aieeeuuuuu** · 12/11/2019, 10h08

quelles est la structure de la vraie table ?

quelle est sa volumétrie ?

essayez en indexant :

Code :

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

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

**JeitEmgie** · 12/11/2019, 19h58

… 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 :

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
 
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 …

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

Développement SQL Server

Discussions similaires

Partager

Partager