Bonjour

j'ai une table "meteo" qui est alimenté toutes les 5 minutes via un script qui lit un fichier csv, cette table stock maintenant près de 5 ans de relevés.

dans cette table j'ai un champ "periode" (aaaa-mm-jj hh-mm-ss) puis un autre "relevé_pluie" . je souhaiterez par l’intermédiaire d'une requête  sur mon script, rajouter un champ "cumul_en_24h" et qui lirait chaque ligne des champs "relevé_pluie" et "periode" et indiquerais le cumul en 24h.

Mon but est de récupérer le cumul maxi en 24h d'une période choisi. 

j’espère être clair dans ce que je veux faire , si quelqu'un a une idée ? 

merci

Bonjour,

Une façon assez simple de faire, et de regrouper par date, sans les heures/minutes/secondes, et de faire la somme.

Tu peux créer une vue sur cette requête pour pouvoir l'utiliser comme bon te semble par la suite :

CREATE VIEW v_precicipation_jour AS
	-- Somme précipitation par jour
	SELECT
		DATE( periode ) AS jour,
		SUM( releve_pluie ) AS precipitation
	FROM meteo
	GROUP BY DATE( periode );

Pour récupérer le maxi pour une période donnée :

SELECT MAX( VP.precipitation ) AS max_precipitation
FROM v_precicipation_jour VP
WHERE
    VP.jour >= 'date début période'
    AND VP.jour <= 'date fin période'

Pour récupérer la ou les dates correspondantes à ce maximum :

SELECT VP.jour, VP.precipitation
FROM v_precicipation_jour VP
WHERE
    VP.jour >= '2019-01-01'
    AND VP.jour <= '2019-12-31'
HAVING VP.precipitation = MAX( VP.precipitation )

Merci Benzouye pour ta réponse, j’avoue comme ça ne pas trop y comprendre grand chose. Par contre y a quelques temps tu m'avais aidé pour calculer une variation de pression a 3h et cette requête fonctionne toujours à merveille,

SELECT
    (
        -- Dernière mesure
        SELECT bar
        FROM mesure
        ORDER BY tstamp DESC
        LIMIT 1
    ) - (
        -- Mesure il y a 3 heures
        SELECT bar
        FROM mesure
        WHERE tstamp <= NOW() - INTERVAL 3 HOUR
        ORDER BY tstamp DESC
        LIMIT 1
    ) AS ecart;

ne pourrait on pas l'adapter a ce que je veux ? 

lucasdom a écrit:

j’avoue comme ça ne pas trop y comprendre grand chose

Je t'ai peut-être perdu avec mon histoire de vue ... Une vue est une requête que l'on stocke en base pour pouvoir l'utiliser comme si c'était une table.

Les résultats sont donc accessibles comme si ils étaient contenu dans une table "normale".

Dans cet exemple, je crée une vue contenant la somme des précipitations par jour.

Si ta table météo contient ceci :

La vue va contenir ceci :

lucasdom a écrit:

ne pourrait on pas l'adapter a ce que je veux ? 

Soit j'ai bien compris, et la requête existante n'a rien à voir avec ton besoin ...

Soit je n'ai pas compris ce que tu voulais obtenir, dans ce cas, merci de préciser en donnant un exemple de données et le résultat attendu.

Bonjour

Pas évident du tout d'expliquer avec précision ce que l'on veut faire... j'ai fait un tableau où normalement c'est plus clair :

sur la première le cumul 24h doit prendre en compte les 24 dernière heures

sur la deuxième ligne le cumul prend en compte les 24 dernière heures

ainsi de suite...

de ce fait, les 24 dernières heures peuvent être a cheval sur 2 jours

j’espère que le tableau est compréhensible..

Ok, je n'avais pas saisi

Attention ce que je vais te proposer risque d'être gourmand si tu as beaucoup de données, il faudra bien borner la période pour limiter le nombre d'enregistrement à comparer ...

Le principe est de faire une auto jointure (jointure de la table sur elle-même en utilisant des alias) :

SELECT
	M1.periode,
	M1.releve_pluie,
	SUM( M2.releve_pluie ) AS precipitation_24h
FROM
	meteo M1
		INNER JOIN meteo M2
			ON M1.periode <= M2.periode + INTERVAL 1 DAY
WHERE
	M1.periode BETWEEN '2019-01-01 00:00:00' AND '2019-02-01 00:00:00'

On prend tous les enregistrements de la table météo (aliasée M1) du mois de janvier, que l'on relie à tous les enregistrements de la même table (aliasée M2) dans les 24 suivantes, et on fait la somme des pluies de ces enregsitrements.

Bonjour

j’exécute la requête que tu me donne et comparer à avant j'ai des résultats, par contre ce n'est pas du tout cohérant 

a gauche le cumul à 24h et a droite la pluie..... chaque ligne correspond a un pas de 5 minutes. les valeurs des deux colonnes sont mesurées en millimètres..

Quel est le type de données de la colonne releve_pluie ? DECIMAL ? VARCHAR ? NUMBER ?

On est d'accord que ma requête regarde les 24h suivantes de chaque ligne, c'est à dire que pour la première ligne de ton exemple, les 4158 sont sensés être la somme de 0.2+0+0.2+0+0.6+0.6+0.8+0.6+0.6+ ... jusqu'à 24h après ... à raison d'un enregistrement toutes les 5 minutes, cela fait 288 enregistrements par 24h.

Peux-tu transmettre un dump de ta table pour une période donnée, au moins une semaine (soit 2000 lignes environ) ?

le type de données de la colonne "releve_pluie" est "DOUBLE"

On est d'accord que les 4158 sont censé être la somme de 0.2+0+0.2+0+0.6+0.6+0.8+0.6+0.6 +..... jusqu'à 24h après. Sauf que sur ma table après le dernier 0.6 il n'y avait que des 0 jusqu'à 24h après et de ce fait, à la place de 4158 j'aurais dû avoir 3.8 .

en dessous mon script complet qui lis le fichier csv :

<?php
 $server ="";
$user = "";
$pwd = "";
$db ="";
$con = mysqli_connect($server,$user,$pwd,$db);
if(! $con) { 
exit("Echec de la connexion");
}  
    $req = "SELECT tstamp FROM meteo";
    if ($result = mysqli_query($con,$req))  
        {        
                $NomFich = '022010.csv';                    
        $fichier=@fopen($NomFich,'r');
        if ($fichier)  
			{  			
            $Ligne= fgets($fichier,500);
            $Ligne= fgets($fichier,500);			
            $Fini = false ;
            while (!feof($fichier) and !$Fini)
                  { 
                  $Ligne = fgets($fichier, 500);
                                    
                      $TabLigne      = explode (';',$Ligne);
                      $tstamp        = mktime(substr($TabLigne[1],0,-3),substr($TabLigne[1],3,2),0,substr($TabLigne[0],3,2),substr($TabLigne[0],0,-8),substr($TabLigne[0],6,4));					  
                      $recdateTZ     = substr($TabLigne[0],6,4).substr($TabLigne[0],3,2).substr($TabLigne[0],0,2).substr($TabLigne[1],0,2).substr($TabLigne[1],3,2);
					  $periodeheure       = substr($TabLigne[0],6,4).substr($TabLigne[0],3,2).substr($TabLigne[0],0,2);                      
                      $heure         = $TabLigne[1];
					  $periode  = substr($TabLigne[0],6,4).'-'.substr($TabLigne[0],3,2).'-'.substr($TabLigne[0],0,2).' '.substr($TabLigne[1],0,2).':'.substr($TabLigne[1],3,2);
                      $temp_out      = $TabLigne[2];
                      $hi_temp       = $TabLigne[3];
                      $low_temp      = $TabLigne[4];
                      $out_hum       = $TabLigne[5];
                      $dew_pt        = $TabLigne[6];
                      $wind_speed    = $TabLigne[7];
                      $wind_dir      = $TabLigne[8];
					  $wind_direct   = $TabLigne[8];
					  if ($wind_direct == '---')
           									  {
                                               $wind_direction = '---';
											  }
											  elseif ($wind_direct == 'N')
											  {
                                               $wind_direction = '0';
                                              }
											  elseif ($wind_direct == 'NNE') 
											  {
                                              $wind_direction = '22';
											  } 
											  elseif ($wind_direct == 'NE')
											  {
                                               $wind_direction = '45';
                                              }
											  elseif ($wind_direct == 'ENE')
											  {
                                               $wind_direction = '68';
                                              }
											  elseif ($wind_direct == 'E')
											  {
                                               $wind_direction = '90';
                                              }
											  elseif ($wind_direct == 'ESE')
											  {
                                               $wind_direction = '112';
                                              }
											  elseif ($wind_direct == 'SE')
											  {
                                               $wind_direction = '135';
                                              }
											  elseif ($wind_direct == 'SSE')
											  {
                                               $wind_direction = '158';
                                              }
											  elseif ($wind_direct == 'S') 
											  {
                                              $wind_direction = '180';
											  } 
											  elseif ($wind_direct == 'SSW')
											  {
                                               $wind_direction = '202';
                                              }
											  elseif ($wind_direct == 'SW')
											  {
                                               $wind_direction = '225';
                                              }
											  elseif ($wind_direct == 'WSW')
											  {
                                               $wind_direction = '248';
                                              }
											  elseif ($wind_direct == 'W')
											  {
                                               $wind_direction = '270';
                                              }
											  elseif ($wind_direct == 'WNW')
											  {
                                               $wind_direction = '292';
                                              }
											  elseif ($wind_direct == 'NW')
											  {
                                               $wind_direction = '315';
                                              }
											  elseif ($wind_direct == 'NNW')
											  {
                                               $wind_direction = '338';
                                              }
                      $wind_run      = $TabLigne[9];
                      $hi_speed      = $TabLigne[10];
                      $hi_dir        = $TabLigne[11];
                      $wind_chill    = $TabLigne[12];
                      $heat_index    = $TabLigne[13];
                      $thw_index     = $TabLigne[14];
                      $thsw_index    = $TabLigne[15];
                      $bar           = $TabLigne[16];
					  $qry="SELECT bar, periode  FROM meteo  ORDER BY periode DESC LIMIT 1"; 
                                           if ($result = mysqli_query($con,$qry)) {
                                               while ($donnees = mysqli_fetch_object($result))
                                                {
                                                 $cont=$donnees ;
                                                 $pression_actuel = $cont->bar;
                                                 $date = $cont->periode;					
                                                 }
						                          mysqli_free_result($result);
                                                 }                
				
                                      $qry="SELECT bar FROM meteo WHERE periode <= '".$date."' - INTERVAL 3 HOUR ORDER BY periode DESC LIMIT 1"; 
                                           if ($result = mysqli_query($con,$qry)) {
                                              while ($donnees = mysqli_fetch_object($result))
                                             {
                                              $cont=$donnees ;
                                              $pression_3h = $cont->bar; 												
                                             }
						                     mysqli_free_result($result);
                                             }
								            $ecart = $pression_actuel - $pression_3h;
					  $ecart_3h = round($ecart,1);
					  
					  $qry="SELECT
                            M1.periode,
                            M1.releve_pluie,
                            SUM( M2.releve_pluie ) AS precipitation_24h
                            FROM    meteo M1
                            INNER JOIN meteo M2
                            ON M1.periode <= M2.periode + INTERVAL 1 DAY
                            WHERE M1.periode BETWEEN '2010-02-04 00:05:00' AND '2010-02-07 00:00:00'"; 
                            if ($result = mysqli_query($con,$qry)) {
                                              while ($donnees = mysqli_fetch_object($result))
                                             {
                                              $cont=$donnees ;
                                              $cumul_24h = $cont->precipitation_24h; 												
                                             }
						                     mysqli_free_result($result);
                                             }               
					  
					  $cumul_rain_24 = round($cumul_24h,1);
					  $releve_pluie  = $TabLigne[17];					   					  
                      $rain_rate     = $TabLigne[18];
                      $solar_rad     = $TabLigne[19];
                      $solar_energy  = $TabLigne[20];
                      $hi_solar_rad  = $TabLigne[21];
                      $uv_index      = $TabLigne[22];
                      $uv_dose       = $TabLigne[23];
                      $hi_uv         = $TabLigne[24];
                      $heat_dd       = $TabLigne[25];
                      $cool_dd       = $TabLigne[26];
                      $in_temp       = $TabLigne[27];
                      $in_dew        = $TabLigne[29];
                      $in_heat       = $TabLigne[30];
					  $emc           = $TabLigne[31];
					  $air_density   = $TabLigne[32];
					  $temp_2nd      = $TabLigne[33];
                      $et            = $TabLigne[34];
                      $soil_moist1   = $TabLigne[35];
					  $soil_moist2   = $TabLigne[36];
					  $soil_moist3   = $TabLigne[37];
					  $soil_moist4   = $TabLigne[38];
					  $soil_temp1    = $TabLigne[39];
					  $soil_temp2    = $TabLigne[40];
					  $soil_temp3    = $TabLigne[41];
					  $soil_temp4    = $TabLigne[42];								 
					  $leaf_wet1     = $TabLigne[43];                      
                      $wind_samp     = $TabLigne[44];
                      $wind_tx       = $TabLigne[45];
                      $iss_recept    = $TabLigne[46];
                      $arc_int       = $TabLigne[47];					  
                      if (strlen($tstamp) > 9)
					 					  
                      {					  
					  $enregist= "INSERT INTO meteo
                                            (tstamp,recdateTZ,periodeheure,heure,periode,temp_out,hi_temp,low_temp,out_hum,dew_pt,wind_speed,wind_dir,wind_direction,wind_run,
					   hi_speed,hi_dir,wind_chill,heat_index,thw_index,thsw_index,bar,ecart_3h,cumul_rain_24,releve_pluie,rain_rate,solar_rad,solar_energy,
					   hi_solar_rad,uv_index,uv_dose,hi_uv,heat_dd,cool_dd,in_temp,in_dew,in_heat,emc,air_density,temp_2nd,
					   et,soil_temp1,soil_temp2,soil_temp3,soil_temp4,soil_moist1,soil_moist2,soil_moist3,soil_moist4,
					   leaf_wet1,wind_samp,wind_tx,iss_recept,arc_int)
                       VALUES
                      ('$tstamp','$recdateTZ','$periodeheure','$heure','$periode','$temp_out','$hi_temp','$low_temp','$out_hum','$dew_pt','$wind_speed','$wind_dir','$wind_direction','$wind_run',
					   '$hi_speed','$hi_dir','$wind_chill','$heat_index','$thw_index','$thsw_index','$bar','$ecart_3h','$cumul_rain_24','$releve_pluie','$rain_rate','$solar_rad','$solar_energy',
					   '$hi_solar_rad','$uv_index','$uv_dose','$hi_uv','$heat_dd','$cool_dd','$in_temp','$in_dew','$in_heat','$in_emc','$in_air_density','$temp_2nd',
					   '$et','$soil_temp1','$soil_temp2','$soil_temp3','$soil_temp4','$soil_moist1','$soil_moist2','$soil_moist3','$soil_moist4','$leaf_wet1','$wind_samp','$wind_tx','$iss_recept','$arc_int')";
                      
                                 $result = mysqli_query($con,$enregist);
								if(mysqli_error($con)) echo mysqli_errno($con) . ": " . mysqli_error($con) . "\n";
                                $Date=substr($recdateTZ,0,4).'-'.substr($recdateTZ,4,2).'-'.substr($recdateTZ,6,2).' à '.$horraire; 								 
                                if ($result)
                        {
                        echo 'DONNEES DU '.$Date.' ENREGISTREES.<br>';
                        } 
                        else
                        {
                        echo 'PROBLEME LES DONNEES DU '.$Date.' NON PAS ETE ENREGISTREES.<br>';
                        }
					  }	
                    }
                                
                
            echo "<br><br> TRAITEMENT TERMINE"  ;
            }
            else
            {
            echo $NomFich.' INTROUVABLE';
            }       
        }
        else
        {
        echo "PROBLEME AVEC LA TABLE";         
        }
	mysqli_close($con);	 
?>

-
Edité par lucasdom 15 décembre 2019 à 10:38:37

Je t'ai fait écrire tout cela pour rien ... La requête joint avec tous les enregistrements suivants, pas seulement ceux des dernières 24h ...

Il faut rajouter une condition dans la jointure :

SELECT
	M1.periode,
	M1.releve_pluie,
	SUM( M2.releve_pluie ) AS precipitation_24h
FROM
	meteo M1
		INNER JOIN meteo M2
			ON M1.periode < M2.periode
			AND M2.periode <= M1.periode + INTERVAL 1 DAY
WHERE
	M1.periode BETWEEN '2019-01-01 00:00:00' AND '2019-02-01 00:00:00'

ça devrait aller mieux ...

les valeurs obtenus restent incohérente Benzouye

comme tu vois sur la colonne de droite, sur le 1er 0.6  j'ai 0 sur le cumul 24h puis sur le 2eme 0.6 je passe à 169.8 de cumul sur 24h.

Je ne vais pas dire que j'ai fait exprès pour voir si tu suivais, mais tu ne suivais pas ...

J'ai mis une fonction SUM sans clause GROUP BY ...

SELECT
    M1.periode,
    M1.releve_pluie,
    SUM( M2.releve_pluie ) AS precipitation_24h
FROM
    meteo M1
        INNER JOIN meteo M2
            ON M1.periode < M2.periode
            AND M2.periode <= M1.periode + INTERVAL 1 DAY
WHERE
    M1.periode BETWEEN '2019-01-01 00:00:00' AND '2019-02-01 00:00:00'
GROUP BY
    M1.periode,
    M1.releve_pluie

On est mieux là ?

j'essai de suivre  , j'ai essayé ta dernière solution mais apparemment ça ne fonctionne pas ..le cumul à 24h ne se fait pas (cumul 24h colonne de gauche) :

Par contre je viens d'essayer avec une autre requête qui après contrôle a l'air de faire ses preuve .

$sql="select max(tstamp) from meteo";
     $query = mysqli_query($con,$sql);                  
     $list=mysqli_fetch_array($query);    
// On détermine le stop et le start de façon à récupérer dans la prochaine requête que les données des dernières xx heures
$stop=$list[0];
$start=$stop-(86400);

$sql = "SELECT tstamp, SUM(releve_pluie) as precipitation_24h FROM meteo where tstamp >= '$start' and  tstamp <= '$stop' ORDER BY 1";					  
      if ($result = mysqli_query($con,$sql)) {
                                             while ($donnees = mysqli_fetch_object($result))
                                             {
                                              $cont=$donnees ;
                                              $cumul_24h = $cont->precipitation_24h; 												
                                             }
						qli_free_result($result);
                                             }

on vois colonne de gauche que le cumul se fait, j'ai fait le contrôle plusieurs fois