Problème de reqûete Mysql

Version imprimable

Bonjour tous le monde,
j'ai une requête plutôt compliquer à améliorer.

Pour vous situer le contexte:
Je développe actuellement sur un logiciel crée par des anciens élèves.
Ce logiciel à pour but de gérer la gestion des absences sur les semaines en cours et à venir.

Principe :
L'utilisateur crée des TMA.
Chaque utilisateur de l'application est associé à une TMA donné.
Ensuite chaque utilisateur peut ou non définir les journées où il sera absent.

Sachant que chaque TMA doit obligatoirement avoir une personne assigné et présent la journée, la fonctionnalité d'astreinte permet de déterminer quels jours une TMA sera sans personne présente.

Ce que je fait actuellement :
Actuellement, un utilisateur du logiciel peut uniquement définir une Absence sur la journée entière.
Je souhaite donc ajouter une fonctionnalité, où un utilisateur aura la possibilité de choisir l'horaire de son absence ( c'est à dire, absent toute la journée, le matin ou le soir).

Le Problème:
Il est donc nécessaire de prendre en compte dans la fonctionnalité d'astreinte, la présence ou non de chaque utilisateur assigné à une tma ( présence le matin ou le soir ou toute la journée)

Voici ci joint la requête sql déjà conçu par les anciens :
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 select JOUR, TMA from ( select jours2.jour jour, TMA2.TMA tma , '' as Nb from -- Selection les jours et les TMA concerné (select jour from calendrier where jour >= CURDATE()) jours2, planastre.tma tma2 -- jour2 --> selectionner le jour du calendrier supérieur à la date actuel where DAYOFWEEK(jours2.jour) between 2 and 6 and not exists (select 1 from planastre.fermetures fe -- Verifie si la TMA est fermé ou non where fe.tma = tma2.TMA and fe.date_ferm = jours2.jour) -- Verifie si la TMA est fermé ou non union select jour, tma, COUNT(NOM) from -- Selection des jours, tma et le nombre de personnes (select jours.jour, af.TMA tma , af.nom nom, af.niveau from (SELECT cal0.JOUR as jour FROM planastre.calendrier cal0 where jour >= CURDATE()) jours, planastre.affectations af where DAYOFWEEK(jours.jour) between 2 and 6 and not exists (select absences.date_abs, affectations.tma, affectations.nom from planastre.absences absences, -- Selectionner la date de labsence, la tma affecté et le nom des affectations planastre.affectations affectations where absences.nom = AFFECTATIONS.NOM and absences.date_abs=jours.jour and (absences.jour = "JOURNEE" or absences.jour = "MATIN") and absences.type_abs != "Tele-Travail" and affectations.tma=af.tma and affectations.nom = af.nom) and not exists (select 1 from planastre.fermetures fe where fe.tma = af.tma and fe.date_ferm = jours.jour) ) t1 group by jour,tma ) T2 group by jour, tma having max(Nb) = 0;
Ce que représente la requête :
Cette requête récupère les jours où des tma ont des problème d'astreinte.
La ligne en gras représente le moment ou la requête va comparer un par un les utilisateur assigné à une tma et voir si la journée d'absence de l'utilisateur correspond à une journée du calendrier. Ainsi on peux, par comparaison ligne par ligne déterminé si il y a problème d'astreinte.

Ce que j'ai rajouté:
La ligne en gras et italique correspond à mon ajout afin que la requête compare chaque absence d'utilisateur par date d'absence et par leur horaire d'absence.
Actuellement il me prend en compte les problèmes d'astreintes pour laquelle tous les utilisateurs d'une TMA sont absents la journée et les problèmes d'astreintes pour lesquelles personne n'est présent le Matin.
Cependant lorsque je souhaite ajouter le cas de l'absence de type Soir, la requête va définir un problème d'astreinte pour les TMA dont les personnes sont soit absent le matin ou l'aprèm.
Cependant dans le cas où deux personnes sont affectées à une tma et qu' une personne est uniquement absent le matin et l'autre le soir, il ne faut pas qu'il y est un problème d'astreinte car sur la journée il y aura toujours quelqu'un sur la TMA.

Quelqu'un aurait t'il une solution à mon problème s'il vous plaît.

ps: sachant que je suis sur du mysql, la fonctionnalité Intersect ne fonctionne pas et j'ai utilisé un inner join mais ça ne marche pas.

Quelle requête compliquée et mal écrite !
La voici récrite de manière plus lisible :

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
SELECT JOUR, TMA 
FROM 
(
	SELECT jours2.jour jour, TMA2.TMA tma , '' as Nb 
	FROM 												-- Selection les jours et les TMA concerné 
	(
		SELECT jour 
		FROM calendrier 
		WHERE jour >= CURDATE()
	) jours2, -- jour2 --> selectionner le jour du calendrier supérieur à la date actuel 
	planastre.tma tma2 --====> PRODUIT CARTESIEN !
	WHERE DAYOFWEEK(jours2.jour) BETWEEN 2 AND 6  
		AND NOT EXISTS
		(
			SELECT 1 
			FROM planastre.fermetures fe															-- Verifie si la TMA est fermé ou non 
			WHERE fe.tma = tma2.TMA 
				AND fe.date_ferm = jours2.jour
		)											-- Verifie si la TMA est fermé ou non 
 
UNION
 
SELECT jour, tma, COUNT(NOM) 
FROM 																	-- Selection des jours, tma et le nombre de personnes 
(
	SELECT jours.jour, af.TMA tma , af.nom nom, af.niveau 
	FROM  									
	(
		SELECT cal0.JOUR as jour
		FROM planastre.calendrier cal0 
		WHERE jour >= CURDATE()
	) jours, 
	planastre.affectations af --====> PRODUIT CARTESIEN !
	WHERE DAYOFWEEK(jours.jour) BETWEEN 2 AND 6
		AND NOT EXISTS
		(
			SELECT absences.date_abs, affectations.tma, affectations.nom 
			FROM planastre.absences absences,		-- Selectionner la date de labsence, la tma affecté et le nom des affectations
			planastre.affectations affectations  --====> PRODUIT CARTESIEN !
			WHERE absences.nom = AFFECTATIONS.NOM 
				AND absences.date_abs = jours.jour 
				AND 
				(
					absences.jour = "JOURNEE" 
					OR absences.jour = "MATIN"
				) 
				AND absences.type_abs != "Tele-Travail" 
				AND affectations.tma = af.tma 
				AND affectations.nom = af.nom
		)
		AND NOT EXISTS
		(
			SELECT 1 
			FROM planastre.fermetures fe
			WHERE fe.tma = af.tma 
				AND fe.date_ferm = jours.jour
		)
	)  t1 
	GROUP BY jour,tma
) T2 
GROUP BY jour, tma 
HAVING max(Nb) = 0;

J'y ai détecté 2 produits cartésiens, ce qui n'est généralement pas bon pour les performances.

Citation:

j'ai utilisé un inner join mais ça ne marche pas

C'était justement à la place des produits cartésiens ?

J'ai l'impression qu'on peut simplifier certaines choses...
Que contient la colonne TMA ?
Ne peut-on faire une jointure entre la table planastre.tma et la table calendrier ?

16/05/2018, 11h27
Babujhi

Bonjour et merci de votre aide.
La colonne TMA contient uniquement le nom de mes TMA.
Je pense pas que l'on peu faire une jointure entre la table planastre.tma et calendrier car
la table calendrier dispose des colonnes JOUR et FERIE( 0 ou 1), tandis que la table tma contient TMA (nom de la tma), LOGICIEL (logiciel utilisé pour une tma) , VERSION( version du logiciel pris en charge)

Cordialement
16/05/2018, 13h51
escartefigue

S'il n'existe aucun critère de jointure, c'est que la table est incomplète, qu'il manque des tables, ou que la requête est mal pensée
Dans tous les cas, faute de critère de jointure, la requête va construire une table dont l'effectif sera le produit cartésien des effectifs des tables jointes, ce qui risque de planter votre requête en time-out si les volumes respectifs sont conséquents
Afin de mieux comprendre, expliquez c'est qu'est une "TMA" ?

Décomposons pour tenter de simplifier...

1) Jours de semaine futurs
Code:

1 2 3 4 SELECT jour FROM calendrier WHERE jour >= CURRENT_DATE AND DAYOFWEEK(jour) BETWEEN 2 AND 6
2) Jointure croisée (produit cartésien) entre les TMA et les jours futurs en excluant les couples figurant dans les fermetures
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT t.TMA, jf.jour FROM planastre.tma t CROSS JOIN ( -- Jours de semaine futurs SELECT jour FROM calendrier WHERE jour >= CURRENT_DATE AND DAYOFWEEK(jour) BETWEEN 2 AND 6 ) jf WHERE NOT EXISTS ( SELECT * FROM planastre.fermetures f WHERE f.TMA = t.TMA AND f.date_ferm = jf.jour )
3) J'ajoute les affectations potentielles aux TMA à la requête précédente et j'élimine les couples {jour, nom} qui figurent dans les absences
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 SELECT t.TMA, jf.jour, af.nom FROM planastre.tma t INNER JOIN planastre.affectations af ON af.TMA = t.TMA CROSS JOIN ( -- Jours de semaine futurs SELECT jour FROM calendrier WHERE jour >= CURRENT_DATE AND DAYOFWEEK(jour) BETWEEN 2 AND 6 ) jf WHERE NOT EXISTS ( SELECT * FROM planastre.fermetures f WHERE f.TMA = t.TMA AND f.date_ferm = jf.jour ) AND NOT EXISTS ( SELECT * FROM planastre.absences ab WHERE ab.nom = af.nom AND ab.date_abs = jf.jour )
Regardez déjà si ça donne un résultat exploitable pour les couples {TMA, jour} possibles.

Ensuite, questions de compréhension de votre problème :

Citation:

Cependant dans le cas où deux personnes sont affectées à une tma et qu' une personne est uniquement absent le matin et l'autre le soir, il ne faut pas qu'il y est un problème d'astreinte car sur la journée il y aura toujours quelqu'un sur la TMA.

1) Peut-il y avoir plus de deux personnes affectées à une TMA ?

2) Une seule personne est-elle suffisante pour une TMA ?

3) Si les toutes les personnes affectées à une TMA sont absentes la même demi-journée, la TMA est-elle à conserver ? Autrement dit, la TMA peut-elle avoir lieu sur la demie-journée où il y a au moins une personne présente ou bien doit-il y avoir au moins une personne à tout moment de la journée ?

17/05/2018, 11h08
Artemus24

Salut Babujhi.

Citation:

Envoyé par Babujhi

j'ai une requête plutôt compliquer à améliorer.

Votre requête est mal écrite et c'est pourquoi elle parait compliquée.
Il se peut aussi que vous ayez un problème de modélisation de votre base de données.

Citation:

Envoyé par Babujhi

Je développe actuellement sur un logiciel crée par des anciens élèves.

Est-ce dans le cadre d'un projet scolaire ?

Citation:

Envoyé par Babujhi

L'utilisateur crée des TMA.

Qu'est-ce qu'une TMA ? Tierce Maintenance Applicative ?

Citation:

Envoyé par Babujhi

Chaque utilisateur de l'application est associé à une TMA donné.

Donnez un jeu d'essai par table ainsi que le descriptif de vos tables.
Sans cela, nous ne sommes pas capable de vous trouver la bonne requête.

Citation:

Envoyé par Babujhi

Sachant que chaque TMA doit obligatoirement avoir une personne assignée et présente durant la journée, la fonctionnalité d'astreinte permet de déterminer quels jours une TMA sera sans personne présente.

Vous recherchez pour une TMA donnée, tous les jours qui n'ont pas une personne d'assignée.

Citation:

Envoyé par Babujhi

Actuellement, un utilisateur du logiciel peut uniquement définir une Absence sur la journée entière.
Je souhaite donc ajouter une fonctionnalité, où un utilisateur aura la possibilité de choisir l'horaire de son absence ( c'est à dire, absent toute la journée, le matin ou le soir).

Vous définissez une colonne ayant que trois valeurs possibles, à savoir "matin", "soir" et "journée".
Par simplification, il vaut mieux utiliser une code comme "m" pour "matin", "s" pour "soir" et "j" pour "journée", au lieu de stocker un libellé.
A ce code, vous devez associer la TMA, la date et l'utilisateur.

Citation:

Envoyé par Babujhi

Ainsi on peux, par comparaison ligne par ligne déterminé si il y a problème d'astreinte.

Votre problème d'astreinte va se décomposer en plusieurs cas que voici :
1) un utilisateur est présent durant toute la journée.
2) un utilisateur est absent le matin et un autre utilisateur est absent le soir.

Pour le cas 1), c'est facile à déterminer. Il suffit de comptabiliser les utilisateurs présents.
Autrement dit, ayant ni "m" ni "s" ni "j" !

Pour le cas 2), c'est un peu plus difficile à faire car vous devez rechercher des couples.
C'est-à-dire un utilisateur absent le matin et un autre utilisateur absent le soir, pour la même TMA de la même journée.
Sachant que celui qui est absent le matin est présent le soir, et vice-versa.

@+