Fonctions de date/heure en MySQL


précédentsommairesuivant

Evènements les plus récents d'un historique

Enoncé du problème
On dispose d'une table historique contenant l'historique des évènements survenus sur un ensemble d'éléments. Nous prendrons comme exemple du matériel informatique, cependant les requêtes SQL peuvent s'adapter à n'importe quel exemple.

Les éléments considérés seront donc des PC, et les évènements associés seront par exemple: des réparations, des mises à jour, des réinstallations, etc.

On cherche à avoir la liste des derniers évènements survenus pour chacun des matériels.



Pour ce faire, on dispose des tables suivantes :

  • Materiel : le matériel informatique (éléments)
  • Type_Evenement : les évènements
  • Historique : l'historique des évènements survenus sur le matériel (association entre Matériel et Type_Evenement)
Script de création
Sélectionnez
CREATE TABLE materiel (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  description CHAR(30) NOT NULL
) ;


CREATE TABLE type_evenement (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  libelle CHAR(30) NOT NULL
) ;


CREATE TABLE historique (
  id            INT PRIMARY KEY AUTO_INCREMENT,
  idMat         INT NOT NULL,
  idEvent       INT NOT NULL,
  datetimeEvent DATETIME,
  gravite       INT NOT NULL DEFAULT "0",
  commentaires  VARCHAR(100) NOT NULL DEFAULT "",
  
  CONSTRAINT FOREIGN KEY fk_idMat   (idMat)   REFERENCES materiel(id),
  CONSTRAINT FOREIGN KEY fk_idEvent (idEvent) REFERENCES type_evenement(id)  
) ;



Insertion des données
Sélectionnez
INSERT INTO materiel (id, description)
VALUES
(1, "PC 1"),
(2, "PC 2"),
(3, "PC 3"),
(4, "PC 4"),
(5, "PC 5"),
(6, "PC 6");


INSERT INTO type_evenement (id, libelle)
VALUES
(1, "Première mise en service"),
(2, "Dépannage sur site"),
(3, "Réparation atelier"),
(4, "Retour fournisseur"),
(5, "Mise à jour OS"),
(6, "Réinstallation OS"),
(7, "Scan antivirus"),
(8, "Traitement antivirus") ;


INSERT INTO historique (idMat, idEvent, datetimeEvent, gravite, commentaires)
VALUES
(1, 1, "2005-06-01 14:00:00", 0, ""),
(2, 1, "2005-06-01 15:30:00", 0, ""),
(3, 1, "2005-07-01 10:45:00", 0, ""),
(4, 1, "2005-07-15 09:00:00", 0, ""),
(4, 3, "2005-09-21 10:15:00", 3, "Un membre du service commercial a pris son lecteur CD pour un porte-goblet!"),
(2, 2, "2005-09-24 08:10:00", 2, "L'ordi de la secrétaire ne démarrait pas: le cable était débranché..."),
(3, 7, "2005-09-26 00:08:00", 1, ""),
(1, 5, "2005-10-01 09:00:00", 1, "Mise à jour Service Pack 2"),
(1, 6, "2005-10-01 10:30:00", 4, "La mise à jour a planté..."),
(1, 7, "2006-01-09 00:02:00", 1, ""),
(2, 7, "2006-01-09 00:08:00", 1, ""),
(3, 7, "2006-01-09 00:56:00", 1, ""),
(4, 7, "2006-01-09 01:04:00", 1, ""),
(3, 8, "2006-01-09 09:15:00", 5, "Un imbecile a téléchargé un fichier illégal contenant un virus."),
(3, 6, "2006-01-09 16:35:00", 5, "L'imbecile s'est fait viré...") ;



Par ailleurs, le problème faisant intervenir un nombre conséquent de paramètres, nous ajouterons des contraintes pas à pas (sous forme de raffinements), avant d'aboutir à notre requête finale.

Requête de base

Pour chacun des éléments dans l'historique, on cherche à déterminer le dernier évènement survenu. Pour cela, on effectue la requête suivante :

 
Sélectionnez
SELECT H1.*

FROM historique H1
LEFT OUTER JOIN historique H2
ON (H1.idMat = H2.idMat AND H1.datetimeEvent < H2.datetimeEvent)

WHERE H2.datetimeEvent IS NULL
ORDER BY H1.idMat ;


Explications : On part de l'historique H1 et pour chaque évènement on cherche les autres évènements (H2) portant sur le même matériel, mais plus récents (jointure LEFT JOIN réflexive). Le dernier évènement n'aura pas de correspondance dans H2, d'où la condition WHERE H2.datetimeEvent IS NULL

De nombreuses personnes tendent à exprimer ce traitement au moyen de requêtes imbriquées, cependant :

  • Les requêtes imbriquées ne sont implémentées qu'à partir de la version 4.1 de MySQL
  • Les requêtes imbriquées tendent à rendre les exécutions moins rapides
  • Les requêtes imbriquées ne permettent pas d'exprimer les raffinements suivants

Raffinement 1 : les N derniers évènements de l'historique

Cette fois ci, on cherche une requête plus générale pour afficher les N derniers évènements survenus sur chaque matériel. Dans notre exemple, N=3 :

 
Sélectionnez
SELECT H1.*

FROM historique H1
LEFT OUTER JOIN historique H2
ON (H1.idMat=H2.idMat AND H1.datetimeEvent <= H2.datetimeEvent)

GROUP BY H1.id
HAVING COUNT(*) <= 3
ORDER BY H1.idMat, H1.datetimeEvent ;



Explications : Ici, on fait presque la même jointure que précédemment (sauf que le "<" de la jointure est remplacé par un "<="), puis on regroupe par évènement et on ne garde que ceux ayant au plus N évènements plus récents (condition du HAVING).

Raffinement 2 : prise en compte du matériel n'apparaissant pas dans l'historique

Pour l'instant, pour des raisons de simplicité, nous ne nous sommes intéressés qu'aux matériels pour lesquels on avait déjà un enregistrement dans l'historique. Cependant, il existe des matériels qui n'ont pas de correspondance dans l'historique (PC 5 et 6). On souhaite maintenant tenir compte des matériels qui n'apparaissent pas dans l'historique.

Pour cela, on utilise une jointure de type LEFT OUTER JOIN (s'il n'y a pas de correspondance dans l'historique, les colonnes prendront la valeur NULL)

 
Sélectionnez
SELECT materiel.* , H1.*
FROM materiel

LEFT OUTER JOIN historique H1 
ON ( materiel.id = H1.idMat )

LEFT OUTER JOIN historique H2 
ON ( H1.idMat = H2.idMat AND H1.datetimeEvent <= H2.datetimeEvent )

GROUP BY materiel.id, H1.id
HAVING COUNT(*) <= 3
ORDER BY materiel.id, H1.datetimeEvent



Explications : On part de l'ensemble des matériels répertoriés, puis on recherche s'il existe des évènements correspondant dans l'historique (jointure LEFT JOIN). Si aucun évènement n'est trouvé dans l'historique, les colonnes de H1 prendront la valeur NULL. Pour le reste, il s'agit des mêmes jointures que dans le raffinement précédent.

Raffinement 3 : spécifications multicritères

Maintenant, on souhaite afficher les N derniers évènements de gravité supérieure à 3 pour tous les matériels (y compris ceux qui n'apparaissent pas dans l'historique).

Dit autrement, c'est comme si on souhaitait effectuer les mêmes requêtes que précédemment, mais sur un historique réduit aux seuls évènements de gravité supérieure à 3.

Pour cela, on effectue la requête suivante :

 
Sélectionnez
SELECT materiel.*, H1.*
FROM materiel

LEFT OUTER JOIN historique H1 
ON ( materiel.id = H1.idMat
AND 3 < H1.gravite)

LEFT OUTER JOIN historique H2 
ON ( H1.idMat = H2.idMat AND H1.datetimeEvent <= H2.datetimeEvent 
AND 3 < H2.gravite)

GROUP BY materiel.id, H1.id
HAVING COUNT(*) <= 3
ORDER BY materiel.id, H1.datetimeEvent



Remarquez la place des conditions 3<gravite : elles se trouvent dans les conditions de jointures.

Explications : Quand on fait nos jointures, c'est entre un ensemble de départ et un ensemble réduit à certaines données (par exemple, entre l'ensemble des matériels et l'ensemble des évènements réduit à ceux de gravité supérieure à 3). Par conséquent, les conditions de réduction de domaine sont situés dans les conditions de jointures LEFT JOIN.

Si on mettait ces conditions dans une clause WHERE, la signification serait complètement différente (l'ensembles des évènements de gravité supérieure à 3 faisant partie des N derniers évènements). Par ailleurs, on aurait un problème de suppression de lignes pour les matériels n'ayant pas d'évènements correspondants...


précédentsommairesuivant

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2006 Pierre Caboche. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.