Fonctions de date/heure en MySQL


précédentsommairesuivant

Statistiques sur une période donnée

Enoncé du problème
On a une liste de messages échangés. On désire effectuer des statistiques sur le nombre de messages envoyés par jour sur les 30 derniers jours.

Si pour certains jours aucun message n'a été envoyé, le jour doit quand même apparaître dans les statistiques, avec "0" comme nombre de message



On dipose des tables suivantes :

  • Message : les messages échangés
  • Utilisateur : les utilisateurs
Script de création
Sélectionnez
CREATE TABLE message (
  num             INT PRIMARY KEY AUTO_INCREMENT,
  idExpediteur    INT  NOT NULL,
  idDestinataire  INT  NOT NULL,
  sujet           VARCHAR(50) NOT NULL,
  contenu         TEXT NOT NULL,
  datetimeEnvoi   DATETIME NOT NULL,
  
  CONSTRAINT FOREIGN KEY fk_idExpediteur   (idExpediteur)   REFERENCES utilisateur(id),
  CONSTRAINT FOREIGN KEY fk_idDestinataire (idDestinataire) REFERENCES utilisateur(id)
) ;



Prérequis

Pour nous faciliter la tâche, nous allons créer une table contenant un certain nombre de valeurs numériques comprises entre 0 et N.

Script de création + insertion
Sélectionnez
CREATE TABLE nbr (
  n INT PRIMARY KEY
) ;

INSERT INTO nbr VALUES
(0), (1), (2), (3), (4), (5) ;


INSERT IGNORE INTO nbr
SELECT A.n*6 + B.n AS Valeur
FROM nbr A, nbr B ;



Maintenant nous disposons d'un liste des entiers de 0 à 35, ce qui est largement suffisant pour ce que nous souhaitons faire.

La requête

Maintenant, nous diposons de tout ce qu'il nous faut pour effectuer la requête: la table nbr va nous permettre de fabriquer un intervalle de temps allant de (aujourd'hui-30 jours) à la date actuelle. Par ailleurs, le champ de date dans message est de type DATETIME, il nous faudra que la partie "DATE" pour faire la jointure. Ce sera fait grâce à la fonction DATE.

Voici donc notre requête :

 
Sélectionnez
SELECT
  DATE_SUB(CURDATE(), INTERVAL nbr.n DAY) AS jour,
  COUNT(message.num) AS nombreMessages

FROM nbr
LEFT OUTER JOIN message
ON DATE(message.datetimeEnvoi) = DATE_SUB(CURDATE(), INTERVAL nbr.n DAY)

WHERE nbr.n BETWEEN 0 AND 30
GROUP BY jour
ORDER BY jour



Note de version : la fonction DATE est apparue avec la version 4.1.1 de MySQL. Pour les versions antérieures, il faudra utiliser la fonction DATE_FORMAT(message.datetimeEnvoi, "%Y-%m-%d"). Voir partie Rétrocompatibilité pour plus d'informations.

Dans la pratique

Dans la pratique, sur un nombre important d'enregistrements, il se peut que la requête soit lente à cause du GROUP BY. C'est d'autant plus vrai si les dates ne sont pas indexées (les index servent à accélérer les comparaisons sur les champs, par l'usage d'un arbre binaire de recherche).

Pour remédier à ce problème, il peut être judicieux d'avoir recours à des requêtes de type SELECT COUNT(*) pour chacun des intervalles considérés plutôt qu'à une seule requête pour l'ensemble de la période. En effet, l'opération COUNT est une opération simple et nécessitant peu de ressources. Par ailleurs, cela laisse au programmeur une grande liberté et une grande souplesse concernant la période pour laquelle on souhaite établir les statistiques (début, fin et pas d'incrémentation).

En résumé, il a été vérifié dans la pratique que pour un nombre important d'enregistrements, il est préférable d'effectuer plusieurs requêtes SELECT COUNT(*) dans une boucle for. Cette boucle se programme au niveau de l'applicatif (ou éventuellement sous forme de procédure stockée).


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.