Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

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


Sujet :

Développement SQL Server

  1. #1
    Futur Membre du Club
    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

  2. #2
    Expert éminent
    regarde les fonctions LEAD ou LAG.
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  3. #3
    Futur Membre du Club
    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.

  4. #4
    Rédacteur

    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 :


    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  5. #5
    Futur Membre du Club
    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 :


    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 :



    Merci encore pour votre aide.

  6. #6
    Modérateur

    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

  7. #7
    Futur Membre du Club
    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 :


  8. #8
    Modérateur

    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 )

  9. #9
    Futur Membre du Club
    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 !

  10. #10
    Futur Membre du Club
    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 !

  11. #11
    Modérateur

    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);

  12. #12
    Membre expert
    … 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' ;




    [TH="bgcolor: #F4F4F4"]ID[/TH]
    [TH="bgcolor: #F4F4F4"]START_DATE[/TH]
    [TH="bgcolor: #F4F4F4"]END_DATE[/TH]
    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 …

###raw>template_hook.ano_emploi###