Moyenne

Question

Bonjour...

Je débute en SQL et un premier soucis se pose à moi.

J'ai une table toute simple du type
DATE                                    VALEUR
28/03/2008 15:00:00               100
28/03/2008 15:01:00               122
28/03/2008 15:02:00               98
28/03/2008 15:03:00               105
28/03/2008 15:04:00               114
etc...

Toutes les minutes, j'ajoute une nouvelle ligne.

Je souhaite afficher cet historique sous forme de courbe.

Pour cela, je fait
   "SELECT * FROM ma_table ORDER BY Date DESC LIMIT 100
Car je veux afficher 100 valeurs sur ma courbe.

Désormais, je voudrais faire une requête en spécifiant une date de début et une date de fin, et que cela me retourne TOUJOURS 100 valeurs.

Si par exemple entre Date_debut et Date_fin il y a 200 valeurs. je souhaite qu'on me retourne 100 valeurs moyénnées.
Si par exemple entre Date_debut et Date_fin il y a 2000 valeurs. je souhaite qu'on me retourne 100 valeurs moyénnées.

Comment effectuer une telle requête ???

G__D · Answer

Une précision. Je souhaite que la moyenne soit par tranche de temps, et non par nombre de ligne.

C'est à dire que si entre date_debut et date_fin il y a 200 minutes. Je veux obtenir les 100 valeurs correspodant à la moyenne des valeurs sur 2 minutes.

Dans l'exemple de la table donnée au dessus, une insertion est faites toutes les minutes. Mais il peut très bien y avoir des trous de plusieurs minutes entre chaques...

C'est clair  ce que je dis ????

Farfadh · Answer

C'est assez clair, mais ce que tu veux faire est beaucoup trop complexe pour un débutant, sauf si tu es un développeur confirmé et que c'est juste MySQL que tu connais mal.

MySQL ne peut à priori pas te faire ça en une seule requête, sauf erreur de ma part. A mon avis tu dois récupérer tous tes enregistrements et traiter les données par PHP. Et si tu demandes à ce dernier de te tracer un graphique, tu dois imaginer une méthode pour que l'image ne soit générée qu'en cas de nécessité, c'est à dire quand des dates et des valeurs sont ajoutées à la table, sinon renvoyer la dernière. Sinon tu risques de surcharger le serveur en travail, ce qui mécontentera ton hébergeur le cas échéant.

Après tu n'as aucune idée de combien de valeurs tu disposeras, ni de leur régularité, ce qui fait qu'il faut déterminer une manière de calculer les moyennes qui puisse "boucher les trous". Une moyenne pondérée peut faire l'affaire.

La méthode que je recommande, c'est déjà de calculer le temps écoulé entre le début et la fin que tu obtiendras par ces requêtes :

SELECT date FROM ma_table ORDER BY date LIMIT 1
SELECT date FROM ma_table ORDER BY date DESC LIMIT 1

Tu devras convertir les dates depuis le format MySQL ('yyyy-mm-dd hh:mm:ss')
au format Unix (entier) pour pouvoir calculer la différence :

function date_mysql2unix($DATEMYSQL)
// converti une date depuis le format MySQL vers Unix
// remarque : il y a d'autres méthodes possibles pour décomposer la date MySQL, comme avec une expression régulière
// désolé pour le format des variables, par habitude je met leur préfixe en minuscules et leur nom en majuscules
{
        // decompose la date MySQL
        list($une_DATE, $un_MOMENT)=  explode(' ', $MYDATESQL);
        list($une_ANNEE, $un_MOIS, $un_JOUR)= explode('-', $une_DATE);
        list($une_HEURE, $une_MINUTE, $une_SECONDE)= explode(':', $un_MOMENT);
        
        // construit et renvoie la date Unix
        return(mktime(intval($une_HEURE), intval($une_MINUTE), intval($une_SECONDE), intval($un_MOIS), intval($un_JOUR), intval($une_ANNEE)));
}

Ensuite tu devras créer 100 dates arbitraires entre le début et la fin dans un tableau :

$duree= $fin- $debut; //calcule la duree
$intervale= $duree/ 99; // calcule l'intervale (duree/(nombre_d_intervales- 1) puisqu'on ne compte pas la date de debut)
for($date= $debut; $date<= $fin; $date+= $intervale) // parcoure la duree depuis le debut jusqu'a la fin par intervales
    $dates_arbitraires[]= $date; // stocke les dates arbitraires dans un tableau

Ensuite il faudra que tu rappatries toutes les dates et valeurs de ta table :

SELECT date, valeur FROM ma_table ORDER BY date

Pour pouvoir calculer tes moyennes en toute liberté, il vaut mieux les stocker dans un tableau et d'avoir sous la main des dates Unix plutôt que MySQL :

while($enregistrement= mysql_fetch_array($resultat_requete))
{
    $enregistrement['date']= date_mysql2unix($enregistrement['date']);
    $dates_et_valeurs_originales[]= $enregistrement;
}

Maintenant, le gros du problème ! Il s'agit de calculer les moyennes. Et le problème en question, c'est qu'on ne connait pas les intervales entre les dates. On va devoir donc utiliser une sorte de moyenne pondérée par les distances entre les dates arbitraires et les dates de la tables :

function moyenne_ponderee($point_de_depart, $points_reels_et_valeurs)
// calcule la moyenne pondérée, NULL si aucun points réels
// les $points_reels_et_valeurs sont en fait des tableaux dans un tableau, de structure array(array(point0, valeur0), array(point1, valeur1), ...) et ça tombe bien, les enregistrements des dates et valeurs que l'on possède a cette structure
{
    if(!count($points_reels_et_valeurs)) return NULL; // si on a pas de points réels, quitte et renvoie NULL
    // ce cas de figure ne devrait jamais arriver, mais on doit éviter une division par zéro
    // (et selon le célèbre Anonymous détruire la matrice de la réalité )

    $ponderation_totale= 0; // pondération totale des valeurs
    $cumul_des_valeurs= 0; // total des valeurs pondérées

    foreach($points_reels_et_valeurs as $un_point)
    {
        $distance= abs($un_point[0]- $point_de_depart); // calcul de la distance, valeur absolue car peut être négative
        $ponderation= $distance- $distance/ $un_point[0]; // calcul de la pondération (revoir en ajoutant un facteur exponentiel si nécessaire)
         $ponderation_totale+= $ponderation; // on ajoute la ponderation au total
        $valeur_ponderee= $un_point[1]* $ponderation; // calcul de la valeur pondérée
         $cumul_des_valeurs+= $valeur_ponderee; // on ajoute la valeur ponderee au total
    }

    // maintenant qu'on a les totaux, on peut calculer la moyenne pondérée et la renvoyer
    return($cumul_des_valeurs/ $ponderation_totale);
}

Et maintenant qu'on a notre outil mathématique, on peut calculer les moyennes :

foreach($dates_arbitraires as $un_index => $une_date) // pour chaque date arbitraire
    $moyenne[$un_index]= moyenne_ponderee($une_date, $dates_et_valeurs_originales); // calculer la moyenne

Le tableau $moyenne ainsi obtenu contient 100 valeurs qui sont les moyennes pondérées désirées. Je ne sais pas quelle est l'efficacité de la méthode, je ne suis pas un grand mathématicien. Calculer les moyennes pondérées sur l'ensemble des valeurs est une solutions très médiocre mais si des dates sont trop espacées, cela compliquait beaucoup trop le calcul. Une solution serait d'appliquer une fonction de type exponentielle sur les distances pour que les valeurs jouent d'autant moins qu'elles sont éloignées de la date dont on cherche la moyenne. Je ne développerai pas ce point par contre, modifier la fonction de la moyenne n'est pas si difficile par rapport au reste.

Et non je n'ai pas testé ce code car je n'ai nulle intention de me casser la tête pour faire un script complet surtout si je dois générer un graphique. Ce qui est d'ailleurs paradoxal vu les efforts que je viens de déployer pour trouver une solution.

Je crois que notre débutant a mal évalué la difficulté de son projet et j'ai bien peur qu'il ne soit pas à même d'utiliser toutes les données que je viens d'élaborer. Mais bon, le développement, c'est ma passion. Ceci dit, je ne ferai pas ça pour tout le monde .

Oh, et si je suis à côté de la plaque et que MySQL savait faire ça simplement, qu'on me le signale, mais j'en doute fort. Ce dernier est un gestionnaire de base de données, pas un outil mathématique de haute précision, déjà qu'il a du mal à travailler sur les dates vu la pauvreté de ses fonctions.

Farfadh · Answer

Je viens de m'appercevoir d'une petite erreur dans cette portion de solution. En effet, lorsque que je récupère tous les enregistrements de la table, je travaille sur la partie indexée par des chaines des tableaux obtenus avec mysql_fetch_array pour convertir la date. Puis, dans la fonction de calcul de moyenne, je travaille sur un tableau indexé numériquement. Sans m'étendre davantage, je précise qu'il faut veiller à la compatibilité de ces deux calculs, en veillant de bien fournir à la fonction de moyenne un tableau tel que décrit dans ses commentaires.

Moyenne

3 réponses

Votre réponse

Discussions similaires