Discussion :

[frangipane44]

Bonjour,

je bloque sur une requête SQL ou plutôt la méthode à utiliser, j'espère que quelqu'un pourra m'aider.

Voici mon cas fonctionnel :
Des personnes saisissent leur planning sur le mois dans une interface. Ces personnes peuvent travailler dans plusieurs bureaux dans la même journée. Ma table PLANNING résultant de ces plannings ressemble à ça :

IDPERSONNE	DATE	IDBUREAU	HEURE_DEBUT	HEURE_FIN
1	26/09/2017	1	08:00	08:45
1	26/09/2017	1	09:00	10:00
1	26/09/2017	2	10:00	12:00
1	26/09/2017	1	13:00	15:00
1	26/09/2017	2	15:00	17:00
1	26/09/2017	1	17:00	18:00
2	02/09/2017	3	09:00	10:00
2	02/09/2017	3	10:00	12:00
2	02/09/2017	4	13:00	18:00
2	03/09/2017	3	09:00	10:00
2	03/09/2017	3	10:00	12:00
2	03/09/2017	4	13:00	18:00

Mon but est de connaître les personnes qui se déplacent de bureau en bureau dans la même journée. Je dois pouvoir connaître chaque déplacement fait sur une journée par une personne. A partir des données précédentes, ma table résultat DEPLACEMENT doit ressembler à ça :

IDPERSONNE	DATE	IDBUREAU1	HEURE_FIN_BUREAU1	IDBUREAU2	HEURE_DEBUT_BUREAU2
1	26/09/2017	1	10:00	2	10:00
1	26/09/2017	2	12:00	1	13:00
1	26/09/2017	1	15:00	2	15:00
1	26/09/2017	2	17:00	1	17:00
2	02/09/2017	3	12:00	4	13:00
2	03/09/2017	3	12:00	4	13:00

Maintenant je n'arrive pas à obtenir cette table DEPLACEMENT. Ce qui me pose problème dans les requêtes que j'ai essayé, ce sont surtout les personnes qui font plus de 2 déplacements dans une même journée (la personne avec l'ID 1 dans l'exemple). Je suis parti sur quelque chose comme ça :

Code :

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

1
2
3
4
5
6
 
SELECT p1.IDPERSONNE, p1.DATE, p1.IDBUREAU, max(p1.HEURE_FIN), p2.IDBUREAU, min(p2.HEURE_DEBUT)
FROM PLANNING p1
INNER JOIN PLANNING p2 on p1.IDPERSONNE=p2.IDPERSONNE and p1.DATE=p2.DATE and p1.HEURE_DEBUT < p2.HEURE_DEBUT
WHERE p1.IDBUREAU <> p2.IDBUREAU
GROUP BY p1.IDPERSONNE, p1.DATE

Ce qui me pose souci bien évidemment lorsque la personne fait plus de 2 déplacements dans la journée.

Comment vous y prendriez-vous pour faire cette requête?

Merci d'avance pour vos réponses.

[escartefigue]

Bonjour,

Sous réserve que votre jeu de données en entrée ne comporte pas d'anomalie genre une personne présente dans deux bureaux en même temps, alors il vous suffit de trier les données par identifiant personne et date/heure de début pour constituer votre résultat
Ensuite, faites une auto-jointure et le tour est joué

[frangipane44]

Merci pour votre réponse. Malheureusement ça ne renvoie pas du tout ce que je veux. Voilà ce que j'ai fait :

Code :

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

1
2
3
4
5
6
7
 
select p1.IDPERSONNE, p1.DATE, p1.IDBUREAU, p1.HEURE_FIN, p2.IDBUREAU, p2.HEURE_DEBUT
from 
(SELECT * FROM PLANNING order by date, heure_debut asc) as p1
inner join PLANNING p2 on p1.IDPERSONNE=p2.IDPERSONNE and p1.DATE=p2.DATE and p2.HEURE_DEBUT > p1.HEURE_DEBUT
where p1.IDBUREAU<>p2.IDBUREAU
group by p1.IDPERSONNE, p1.DATE, p1.IDBUREAU, p2.IDBUREAU

Je m'y suis mal pris? Une autre idée?

[tatayo]

Bonjour,
Je dirai qu'il faut voir le problème ainsi:
Renvoyer les lignes L1 de la table, pour lesquelles on trouve une autre ligne L2 avec la même personne, un bureau différent et une même date et une heure supérieure, mais pour lesquelles on ne trouve pas d'autre ligne pour la même personne, la même date, le même bureau que L2 et une heure qui s'intercale entre celle de L1 et celle de L2.

Il ne reste plus qu'à traduire tout ça en SQL

Tatayo.

[escartefigue]

quel est le sgbd ?

[frangipane44]

Si je fais ça

Code :

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

1
2
3
4
5
6
7
 
	SELECT p1.IDPERSONNE b, p1.DATE c , p1.IDBUREAU d, max(p2.HEURE_FIN), p2.IDBUREAU , min(p1.HEURE_DEBUT)
	FROM PLANNING p1 
	inner join PLANNING p2 on p1.IDPERSONNE=p2.IDPERSONNE and p1.DATE=p2.DATE and p1.HEURE_DEBUT > p2.HEURE_DEBUT
	where p1.IDBUREAU<>p2.IDBUREAU
	group by p1.IDPERSONNE, p1.DATE, p1.IDBUREAU, p2.IDBUREAU
	order by p1.IDPERSONNE, p1.DATE

Les résultats sont bons pour l'ID PERSONNE 2 mais pas pour la 1ère personne qui fait des aller-retours dans 2 bureaux sur la même journée.

[aieeeuuuuu]

Bonjour,

Vous pouvez faire 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
 
WITH CTE AS (
SELECT 
		IDPERSONNE
	,	DATE
	,	LAG(IDBUREAU) OVER (PARTITION BY IDPERSONNE, DATE ORDER BY HEURE_FIN) AS IDBUREAU1	
	,	LAG(HEURE_FIN) OVER (PARTITION BY IDPERSONNE, DATE ORDER BY HEURE_FIN) AS HEURE_FIN_BUREAU1	
	,	IDBUREAU		AS IDBUREAU2
	,	HEURE_DEBUT		AS HEURE_DEBUT_BUREAU2
FROM  LaTable
)
SELECT IDPERSONNE, DATE, IDBUREAU1	, HEURE_FIN_BUREAU1	, IDBUREAU2,HEURE_DEBUT_BUREAU2
FROM CTE
WHERE IDBUREAU1 <> IDBUREAU2

[frangipane44]

Merci pour votre aide. Désolé j'ai oublié de préciser que le SGBD est du Mysql en version 5.1.61.

Du coup il ne connaît pas les commandes WITH, LAG, OVER...

[frangipane44]

@tatayo J'ai essayé de traduire ce que vous indiquez en SQL mais apparemment je n'ai pas réussi, ça ne fonctionne pas :

Code :

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

1
2
3
4
5
6
 
select *
from PLANNING p1
inner join PLANNING p2 on p1.IDPERSONNE=p2.IDPERSONNE and p1.IDBUREAU<>p2.IDBUREAU and p1.date=p2.date and p1.heure_debut>p2.heure_debut
left outer join PLANNING p3 on p2.IDPERSONNE=p3.IDPERSONNE and p2.IDBUREAU=p3.IDBUREAU and p2.date=p3.date and p1.heure_debut>p3.heure_debut and p2.heure_debut>p3.heure_debut
where p3.IDPERSONNE is null

Après je commence à avoir le cerveau en ébullition, je m'y suis peut-être mal pris...

[tatayo]

"Ca ne fonctionne pas", c'est très vague.
Mais je dirais que le test sur l'heure n'est pas bon, car l'heure de P3 doit être ENTRE l'heure de P1 et celle de P2.

Code sql :

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

1
2
3
4
5
6
 
select *
from PLANNING p1
inner join PLANNING p2 on p1.IDPERSONNE=p2.IDPERSONNE and p1.IDBUREAU<>p2.IDBUREAU and p1.date=p2.date and p1.heure_debut>p2.heure_debut
left outer join PLANNING p3 on p2.IDPERSONNE=p3.IDPERSONNE and p2.IDBUREAU=p3.IDBUREAU and p2.date=p3.date and p3.heure_debut>p1.heure_debut and p3.heure_debut<p2.heure_debut
where p3.IDPERSONNE is null

Tatayo.

[frangipane44]

La requête ci-dessus me renvoie 12 lignes au lieu de 6 lignes donc ça ne va pas. C'est un casse-tête ce truc...

[Waldar]

Je n'ai pas de MySQL sous la main, je suppose qu'il ne supporte toujours pas les CTE ?
Je ne sais pas s'il faut passer par des tables intermédiaires, car il y a de la factorisation utilisée plusieurs fois.
Bref, j'ai simulé la reconstruction des fonctions de fenêtrage, c'est pas terrible mais je n'ai pas d'autre méthode sous la main.

1. Numérotation croissante par personne, date, trié par heure_debut - équivalent de row_number() over(partition by idpersonne, dt order by heure_debut asc.
J'appellerai ce résultat par la suite CTE_AGG1 :

Code :

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

1
2
3
4
5
6
7
    select t1.idpersonne, t1.dt, t1.idbureau, t1.heure_debut, t1.heure_fin
         , count(t2.idpersonne) as rn1
      from PLANNING as t1
 left join PLANNING as t2  on t2.idpersonne  = t1.idpersonne
                          and t2.dt          = t1.dt
                          and t2.heure_debut < t1.heure_debut
  group by t1.idpersonne, t1.dt, t1.idbureau, t1.heure_debut, t1.heure_fin

2. Numérotation croissante par personne, date, bureau trié par heure_debut - équivalent de row_number() over(partition by idpersonne, dt, idbureau order by heure_debut asc.
Je le soustrait du row_number précédent pour avoir un identifiant supportant les alternances de bureau dans la même journée.
J'appellerai ce résultat par la suite CTE_AGG2 :

Code :

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

1
2
3
4
5
6
7
8
    select t1.idpersonne, t1.dt, t1.idbureau, t1.heure_debut, t1.heure_fin
         , t1.rn1 - count(t2.idpersonne) as grp
      from cte_agg1 as t1
 left join PLANNING as t2  on t2.idpersonne  = t1.idpersonne
                          and t2.dt          = t1.dt
                          and t2.idbureau    = t1.idbureau
                          and t2.heure_debut < t1.heure_debut
  group by t1.idpersonne, t1.dt, t1.idbureau, t1.heure_debut, t1.heure_fin, t1.rn1

3. Je fais un agrégat qui supprime les instances qui se suivent des mêmes personnes, date, bureau.
Cette étape combinée au deux précédentes d'appelle la méthode de Tabibitosan, vous trouverez des articles & solutions (avec des fonctions de fenêtrage) notamment sur le forum Oracle, mais c'est adaptable à n'importe quel SGBD (la preuve).
J'appellerai ce résultat par la suite CTE_AGG3 :

Code :

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

1
2
3
4
5
  select idpersonne, dt, idbureau
       , min(heure_debut) as heure_debut
       , max(heure_fin)   as heure_fin
    from cte_agg2
group by idpersonne, dt, idbureau, grp

4. Numérotation croissante par personne, date et heure de début - équivalent de row_number() over(partition by idpersonne, dt order by heure_debut asc.
J'appellerai ce résultat par la suite CTE_AGG4 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
    select t1.idpersonne
         , t1.dt
         , t1.idbureau
         , t1.heure_debut
         , t1.heure_fin
         , count(t2.idpersonne) as rn2
      from cte_agg3 as t1
 left join cte_agg3 as t2  on t2.idpersonne  = t1.idpersonne
                          and t2.dt          = t1.dt
                          and t2.heure_debut < t1.heure_debut
  group by t1.idpersonne, t1.dt, t1.idbureau, t1.heure_debut, t1.heure_fin

5. Requête finale, je n'ai plus qu'une simple jointure à effectuer :

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
  select t1.idpersonne
       , t1.dt
       , t1.idbureau    as idbureau1
       , t1.heure_fin   as heure_fin_bureau1
       , t2.idbureau    as idbureau2
       , t2.heure_debut as heure_debut_bureau2
    from cte_agg4 as t1
    join cte_agg4 as t2  on t2.idpersonne = t1.idpersonne
                        and t2.dt         = t1.dt
                        and t2.rn2        = t1.rn2 + 1
order by t1.idpersonne, t1.dt, t1.heure_fin;
 
idpersonne  dt          idbureau1  heure_fin_bureau1  idbureau2  heure_debut_bureau2
----------  ----------  ---------  -----------------  ---------  -------------------
         1  2017-09-26          1  10:00                      2  10:00
         1  2017-09-26          2  12:00                      1  13:00
         1  2017-09-26          1  15:00                      2  15:00
         1  2017-09-26          2  17:00                      1  17:00
         2  2017-09-02          3  12:00                      4  13:00
         2  2017-09-03          3  12:00                      4  13:00

[Waldar]

Et pour information avec un SGBD qui supporte quelques fonctionnalités en plus, votre requête s'écrirait ainsi :

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
with cte_numer (idpersonne, dt, idbureau, heure_debut, heure_fin, grp) as
(
select idpersonne, dt, idbureau, heure_debut, heure_fin
     , row_number() over(partition by idpersonne, dt           order by heure_debut asc)
     - row_number() over(partition by idpersonne, dt, idbureau order by heure_debut asc)
  from planning
)
  ,  cte_agg (idpersonne, dt, idbureau1, heure_fin_bureau1, idbureau2, heure_debut_bureau2) as
(
  select idpersonne, dt
       , idbureau
       , max(heure_fin)
       , lead(idbureau)         over(partition by idpersonne, dt order by min(heure_debut) asc)
       , lead(min(heure_debut)) over(partition by idpersonne, dt order by min(heure_debut) asc)
    from cte_numer
group by idpersonne, dt, idbureau, grp
)
  select idpersonne, dt, idbureau1, heure_fin_bureau1, idbureau2, heure_debut_bureau2
    from cte_agg
   where idbureau2 is not null
order by idpersonne, dt, heure_fin_bureau1;

[frangipane44]

Bonjour,

merci beaucoup Waldar pour ton aide.
Ça fonctionne bien avec le jeu de données que j'ai fourni en exemple. En revanche avec mon jeu de données réelles (900000 lignes dans la table PLANNING), la requête pour créer CTE_AGG2 ne termine jamais, le left join est fatal je pense. Même en ajoutant des index.

Une autre idée?

[frangipane44]

En repositionnant les index de la table PLANNING, dorénavant ça fonctionne bien. En 4 minutes.
J'ai dû rajouter un delete en revanche afin de supprimer certaines lignes qui contenaient des déplacements d'un lieu vers le même lieu.

Merci beaucoup!!!