Critère SQL pour vérifier une "continuité" de plusieurs éléments dans le temps

Bonjour à tous !

Ma prise de tête du moment consiste à tenter d’effectuer des requêtes pour vérifier que différents éléments (ayant une date de de début et une de fin), mis dans l’ordre chronologique, forment une suite continue de jours, afin de vérifier que cette suite couvre bien une certaine période. Ces éléments sont liés à des personnes, et je dois donc retourner les personnes selon ce critère de continuité.
Il y a évidemment d’autres critères pour sélectionner ces périodes (des statuts), mais je pense que je devrais arriver à les insérer dans la version finale.

Pour exemple, mettons que j’ai des données similaires à celles dans le tableau ci-dessous.

L’idée est donc de sortir soit Pierre, pour qui :

• la date de début est plus grande ou égale à la date de début du premier élément ;
• la date de fin est plus petite ou égale la date de fin du dernier élément ;
• la date de fin d’un élément coïncide avec (ou est plus grande que) celle de début d’un autre élément.

Ou à l’inverse, sortir Paul, Marie ou Alice :

• soit parce que la date de début est plus petite que la date de début de tous ses éléments ;
• soit parce que la date de fin est plus grande que la date de fin de tous ses éléments ;
• soit parce que la période couverte par tous les éléments n’est pas continue, typiquement :
  • un des éléments a sa date de fin qui :
    • est plus petite que la fin "globale" ;
    • ne coïncide avec aucune date de début d’un autre élément.
  • un des éléments a sa date de début qui :
    • est plus grande que le début "global" ;
    • ne coïncide avec aucune date de fin d’un autre élément.

Je pense que je peux m’en sortir avec pas mal de ces critères :

• pour savoir si on a au moins un élément qui commence à (ou avant) la date de début globale, une jointure depuis ma personne vers les éléments, jointure avec un "critère renforcé" et la vérification  ;
• similairement pour voir si on a un élément qui s’arrête à (ou après) la date de fin globale.

Ce qui me manque actuellement, c’est cette histoire de comparaison entre deux éléments — dans le cas de Pierre comme dans l’autre — sachant que, histoire de ne pas faire simple, je peux avoir des éléments qui se chevauchent (si on prend le cas de Pierre, la date de fin du premier élément serait le 20, et Pierre répondrait toujours aux critères de "continuité"), et que je n’ai évidemment pas toujours deux éléments ?

Merci d’avance 

Je pense qu’il faut que tu fasses un effort pour reformuler ta question.

Tu dis par exemple : ’ L’idée est donc de sortir soit Pierre, pour qui : ’ ; je lis cela et je me pose une question. Est-ce que sortir, ça veut dire Exclure, ou au contraire, ça veut dire afficher, parce que justement, c’est ça la solution.

Tu dis aussi (première ligne) que tu veux Vérifier … Vérifier, ça veut dire que ta fonction va renvoyer Oui ou non. Point final.

Et plus loin, il semblerait que ta fonction ne doive par renvoyer oui ou non, mais renvoyer une extraction de la base, qui vérifie certains critères. Il semblerait, je n’en suis pas sûr.

Je pense qu’en faisant l’effort de formuler précisément quel est ton objectif, quelles sont tes contraintes, tu vas avoir quelque chose de compréhensible par un lecteur lambda. Et donc, tu auras résolu toi-même ton problème.

En fait, il y a les deux cas : soit je n’affiche pas Pierre parce que je souhaite afficher ceux pour qui les différents critères listés ne sont pas remplis, soit je l’affiche parce que justement les critères sont remplis.

Justement, mon souci est la rédaction de ce critère pour savoir si les éléments sont bien contigus/continus dans le temps. Après, pour ce qui est de savoir s’il retourne oui ou non, je crois que je devrais m’en sortir. Pour rappel :

Pour être donc plus précis, c’est comment rédiger une expression SQL qui permet de savoir que deux enregistrements (ou plus) sont contigus en fonction de leurs dates de début et de fin qui me pose problème.
Pour être tout à fait honnête, je ne pense pas que je doive parler d’expression au sens MySQL, parce que ce que je cherche à faire n’est peut-être pas le simple fruit d’un truc logique, ça implique probablement des jointures aussi. Donc pour moi ce sera un critère.

D’accord, la prochaine fois je ne mettrai aucun contexte et je résoudrai le problème avant d’accepter que j’ai besoin d’aide et d’en demander.

Non c’est pas clair, mais on ne peut pas me reprocher de ne pas avoir expliqué, il me semble.
Oui c’est entre autres aussi parce que ce n’est pas clair pour moi que j’ai créé le sujet — et c’est aussi grâce à la réflexion pour le rédiger que j’ai pu formuler les parties "mathématiques" de mes critères énoncés dans le premier message.

Pour le coup, j’aurais grandement mieux apprécié des vraies questions pour que je puisse tenter de mieux expliquer — ce que je ferais volontiers, comme je l’ai dit plus haut, c’est aussi en partie pour m’aider à clarifier que je viens ici, et j’en veux pour preuve que j’ai tenté de répondre à tes interrogations — que ces phrases que JE trouve plutôt lapidaires.

Merci.

Edit

Le souci pourrait être formulé aussi de la manière suivante.

Je ne peux évidemment pas simplement utiliser BETWEEN, parce que justement cela ne va pas faire de cas des "trous" entre mes dates "globales" et les valeurs des éléments. D’autre part, comme le but est de chercher parmi des personnes, je n’ai pas les dates "globales" de disponible avant de lancer la requête.

Avec ces deux formulations, je me rends compte que mon problème serait de "calculer ces trous" entre les éléments.

Edit ₂

Pour les musiciens intéressés, j’ai fait un peu mumuse avec un fiddle. Il y a plus ou moins les données fournies dans le premier message, la différence étant que j’ai mis trois éléments par personne.

1
2
3
4
5
6

 SELECT something FROM (
      SELECT
      [Debut]
      ,[Fin]
      ,diff = DATEDIFF(hour, Lag([Fin], 1) OVER(ORDER BY [Debut], [Fin]), [Debut])
      WHERE something.diff > 24

J’ai du developper quelque chose de semblable ( pour recuperer des "timepunch" de travail qui ont moins de 8hr d’ecart entre le OUT et le prochain IN ). C’est plutot "pseudocode" mais ca ressemble a ce que tu aurais besoin.

Tu travailles sur des jours ; je vais considérer que ce sont des entiers ; il peut y avoir une petite embrouille sur ce point.

Une des difficultés, c’est qu’on travaille sur des intervalles. Si jour_début = jour_fin, alors la durée n’est pas 0, mais 1 jour. Et donc pour le cas général, la durée d’un élément n’est pas fin_element-debut_element, mais fin_element-debut_element+1

Allons-y progressivement ; il faut dtoujours décomposer un problème compliqué en problèmes plus simples.

 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

select id_personne , min(debut_element) as min_jour ,  max(fin_element) as max_jour
from ma_table 
group by id_personne
-- Cette requete donne le min(debut_element) et max(fin_element) pour chaque personne.


select id_personne  
from ma_table 
group by id_personne, debut, fin
having min(debut_element) = debut and max(fin_element) = fin
-- Cette 2ème requete donne les individus pour lesquels le debut et la fin matchent 
-- entre les colonne 'Debut/fin' et les colonnes éléments.
-- Pas tout à fait sur que cette condition t'intéresse.


select id_personne 
from ma_table 
group by id_personne 
having max(fin_element) - min(debut_element)+ 1 =  sum ( fin_element - debut_element+1)
-- Cette requete donne les personnes pour lesquelles la somme des durées cumulées des 'éléments' 
-- coïncide avec la durée totale max_fin - min_debut +1
--
-- La somme des longueurs des maillons coïncide avec la longueur de la chaine.
-- Condition nécessaire pour que nos éléments forment une "chaine" , mais condition non suffisante.



select id_personne  
from ma_table 
group by id_personne 
having max(fin_element) - min(debut_element)+ 1 =  sum ( fin_element - debut_element+1)
MINUS
select id_personne from ma_table t
  where not_exists 
   ( select * from ma_table t1, ma_table t2 
     where t1.id_personne = t.id_personne and t2.id_personne = t.id_personne 
     and t1.rowid <>t2.rowid and t1.fin_element between t2.debut_element and t2.fin_element )
-- Cette dernière requete, c'est la requête précédente 
-- MINUS
-- la liste des personnes pour lesquelles on a 2 éléments qui se chevauchent.

`

A priori la dernière requête est celle qu’il te faut. Il faut peut-être combiner avec la condition  : having min(debut_element) = debut and max(fin_element) = fin

Mince, je pensais que SQL Fiddle permettait de naviguer entre les différentes requêtes testées avec le schéma… Bon, j’imagine assez que vous n’en avez pas besoin, mais il suffirait d’ajouter /{numéro d'essai} (à partir de 1) à l’URL fourni plus haut pour voir ce qui a été essayé.

Tu travailles sur des jours ; je vais considérer que ce sont des entiers ; il peut y avoir une petite embrouille sur ce point.

Une des difficultés, c’est qu’on travaille sur des intervalles. Si jour_début = jour_fin, alors la durée n’est pas 0, mais 1 jour. Et donc pour le cas général, la durée d’un élément n’est pas fin_element-debut_element, mais fin_element-debut_element+1

Désolé, c’est vrai que j’utilise la dénomination telle qu’exprimée dans le besoin, alors qu’on compte en réalité les nuits (d’ailleurs, je suis assez certain que ça va me retomber dessus à un moment ou un autre juste parce que ce ne sera pas clair pour l’utilisateur final…).
Mais du coup, ça implique que les dates de début et de fin des éléments doivent être les mêmes, je n’ai pas besoin d’ajouter ce + 1 qui serait effectivement nécessaire en cas de décompte des jours.

Je vais tenter de m’approprier vos propositions (notamment les adapter à MySQL 5.6 puis voir comment les envoyer dans le DQL de Doctrine) et je reviendrai éditer pour la suite.

Merci de votre aide

Edit

Malheureusement, je ne vois pas trop comment adapter OVER(ORDER BY …) pour MySQL (je me méfie que ça implique plus que de simplement ordonner sur toute la requête), je vais donc voir avec la proposition de elegance.

Je ne souhaite pas laisser ce sujet en plan, donc je réponds.

Mais c’est pour dire que je n’ai pas réussi à adapter la requête, et que le système que j’avais imaginé n’est malheureusement pas viable. Du coup, cette manière de détecter n’est plus d’actualité.

Encore merci pour vos propositions.