Calcul moyenne base de données

17 nov. 2023, 19:49

Bonjour,

Je suis lancé dans un projet ambitieux, une station météo.
Etant débutant j'ai travaillé avec des tutos à ce stade j'ai mon ESP32 qui remonter des données (temp/ hum...) dans une BD et ensuite j'affiche les infos sur une page web
Déjà à ce stade c'est pas mal pour moi :
Maintenant je voudrai pouvoir afficher des moyennes de la journée en cours et sur les 7 derniers jours.

J'ai déjà cela qui me calcule sur les x iéme dernières valeurs

function avgReading($limit, $value) {
global $servername, $username, $password, $dbname;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT AVG(" . $value . ") AS avg_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit " . $limit . ") AS avg";
if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}

Et mon affichage se fait avec :

$avg_tempr = avgReading($readings_count, 'value4');

et

<td><?php echo round($avg_temp['avg_amount'], 1); ?> °C</td>

Voici ma structure de BD :

CREATE TABLE SensorData (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sensor VARCHAR(30) NOT NULL,
location VARCHAR(30) NOT NULL,
value1 VARCHAR(10),
value2 VARCHAR(10),
value3 VARCHAR(10),
reading_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

17 nov. 2023, 21:38

Salut FredD, bienvenue sur le forum !

Pour faire la moyenne des 7 derniers jours, tu doit sélectionner les stats qui ont moins ou égal à 7 jours, pour cela il te faudrait une nouvelle colonne dans ta table SQL SensorData, par exemple, "jour" de type "DATE".

Ensuite, inscrire cette valeur à chaque fois que tu entre de nouvelle valeurs dans ta table, puis de faire une requête avec l'ajout de la condition de date, comme (à adapter) :

SELECT * FROM SensorData WHERE jour >= NOW() - INTERVAL 7 DAY

Ta requête serait :

SELECT AVG(" . $value . ") AS avg_amount 
FROM SensorData 
WHERE jour >= NOW() - INTERVAL 7 DAY 
ORDER BY reading_time DESC LIMIT " . $limit . "

17 nov. 2023, 23:44

Bonjour two3d
Merci pour ta réponse.

Je dois créer une colonne jour de type date, OK ça je sais faire par contre quand tu parles d'inscrire cette valeur je dois inscrire le jour de l'année avec ceci $jour= date(‘z’) par exemple ?
Pour le moment j'utilise cette fonction pour insérer mes valeurs

function insertReading($sensor, $location, $value1, $value2, $value3, $value4, $pm25, $pm10) {
global $servername, $username, $password, $dbname;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "INSERT INTO SensorData (sensor, location, value1, value2, value3, value4, pm25, pm10)

VALUES ('" . $sensor . "', '" . $location . "', '" . $value1 . "', '" . $value2 . "', '" . $value3 . "','" . $value4 . "', '" . $pm25 . "', '" . $pm10 ."')";

if ($conn->query($sql) === TRUE) {
return "New record created successfully";
}
else {
return "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
}

18 nov. 2023, 00:02

il y a déjà le champ reading_time et https://dev.mysql.com/doc/refman/8.0/en ... tions.html

18 nov. 2023, 00:03

Si quand tu insère des données, ce sont les données du jour actuel, tu peux faire simplement :

INSERT INTO.... SET jour = NOW()

Sinon, mettre une date spécifique, comme :

INSERT... jour = '2023-23-11'

EDIT : bien vu or1, j'avais pas fait attention

18 nov. 2023, 20:41

Bonjour two3d,

Je continue à travailler sur ta proposition :
Je suis toujours un peu perdu par jour il s'agit d'une colonne à créer ou une extraction, pour info dans ma BD j'ai déjà un champ champ reading_time

Pour la seconde partie j'ai retranscris ta proposition de la façon suivante mais la syntaxe ne doit pas être ok :

function avgReadingB($value) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT AVG(" . $value . ") AS avg_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit WHERE jour >= NOW() - INTERVAL 7 DAY . $limit . ") AS avg";

if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}

18 nov. 2023, 21:43

Utilise la balise PHP du forum afin qu'on puisse voir plus clair dans ton code s'il te plaît.

Oui, pour la colonne reading_time, lis les précédents messages, or1 s'en est aperçu et me la fait remarquer.

19 nov. 2023, 17:04

Désolé voici le code complet :

Code : Tout sélectionner

function avgReadingB($value) {
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT AVG(" . $value . ") AS avg_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit WHERE jour >= NOW() - INTERVAL 7 DAY . $limit . ") AS avg";

if ($result = $conn->query($sql)) {
return $result->fetch_assoc();
}
else {
return false;
}
$conn->close();
}

19 nov. 2023, 17:18

Merci, il te manque un guillemet à

 INTERVAL 7 DAY . $limit . " //devant la concaténation (le .) de $limit

correct :

 INTERVAL 7 DAY " . $limit . "

Mais $limit dans ta function existe null part...

20 nov. 2023, 22:08

Bonjour,
Je viens te tenter cela mais j'ai un soucie de syntaxe, j'ai fait différent test mais je pense passer à côté de quelque chose dans la syntaxe.

Code : Tout sélectionner


function avgReading($value) {
    global $servername, $username, $password, $dbname;
     // Create connection
     $conn = new mysqli($servername, $username, $password, $dbname);
     // Check connection
     if ($conn->connect_error) {
       die("Connection failed: " . $conn->connect_error);
     }
    
     $sql = "SELECT AVG(" . $value . ") AS avg_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit WHERE date BETWEEN  >= NOW() - INTERVAL 7 DAY ) AS avg";


     if ($result = $conn->query($sql)) {
       return $result->fetch_assoc();
     }
     else {
       return false;
     }
     $conn->close();
   }

Voici le code erreur :

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE date BETWEEN >= NOW() - INTERVAL 7 DAY ) AS avg' at line 1 in /storage/ssd4/987/21478987/public_html/esp-database.php:178 Stack trace: #0 /storage/ssd4/987/21478987/public_html/esp-database.php(178): mysqli->query() #1 /storage/ssd4/987/21478987/public_html/esp-weather-station.php(39): avgReading() #2 {main} thrown in /storage/ssd4/987/21478987/public_html/esp-database.php on line 178

20 nov. 2023, 22:41

Essaye la requête que je t'avais passé s'il te plaît. Ou dis nous ce que tu souhaite faire comme requête si c'est autre chose que de récupérer les 7 derniers jours.

20 nov. 2023, 23:21

Comme j'expliquais précédemment je suis novice dont je pars d'un exemple et j'essaye de l'adapter à mon besoin.

donc je suis parti de cette requête :

Code : Tout sélectionner

$sql = "SELECT AVG(" . $value . ") AS avg_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit " . $limit . ") AS avg";

Ma requête initiale permet de faire une moyenne sur les $limit valeurs (nn nombre pouvant aller de 1 à 100 valeurs) moi ce que je souhaiterai faire c'est faire la moyenne des moyennes des 7 derniers jours.

Par contre je ne sais pas exploiter ta proposition :

Code : Tout sélectionner

SELECT AVG(" . $value . ") AS avg_amount 
FROM SensorData 
WHERE jour >= NOW() - INTERVAL 7 DAY 
ORDER BY reading_time DESC LIMIT " . $limit . "

Donc je suis un vrai novice mais intéressé d'apprendre

Pour info ma dernière tentative :

Code : Tout sélectionner

function avgReadingB($value) {
    global $servername, $username, $password, $dbname;
     // Create connection
     $conn = new mysqli($servername, $username, $password, $dbname);
     // Check connection
     if ($conn->connect_error) {
       die("Connection failed: " . $conn->connect_error);
     }
    
     $sql = "SELECT AVG(" . $value . ") AS avg_amount FROM (SELECT " . $value . " FROM SensorData order by reading_time desc limit WHERE reading_time BETWEEN  >= NOW() - INTERVAL 7 DAY ) AS avg";


     if ($result = $conn->query($sql)) {
       return $result->fetch_assoc();
     }
     else {
       return false;
     }
     $conn->close();
   }

sachant que $valeur correspond à une de mes colonnes (temp ou pression) et reading_time la colonne timestamp au format 2023-11-04 09:35:21

20 nov. 2023, 23:32

SELECT AVG(" . $value . ") AS avg_amount 
FROM SensorData 
WHERE reading_time >= NOW() - INTERVAL 7 DAY

21 nov. 2023, 00:27

Avec ta dernière proposition je n'ai plus d'erreur de syntaxe déjà gros progrès et merci.
j'ai encore 2 questions:
- Il n'y a plus ORDER by parce que la requete est basée sur WHERE reading_time >= NOW() - INTERVAL 7 DAY donc plus besoin d'ordonner, pour vérifier que j'ai bien compris
- Je vais vérifier avec mon ami excel si cela fait le bon calcul par contre de cette façon est ce une moyenne de toutes les mesures sur les 7 derniers jours ou ce que je recherche une moyenne des moyennes des 7 derniers jours, je sais je suis un peu pointilleux mais c'est dans le cas ou un jour je n'ai le même nb de mesures et dans ce cas c'est différent.

21 nov. 2023, 10:33

Super, voilà quelques infos sur la fonction AVG d'SQL : https://sql.sh/fonctions/agregation/avg

ORDER BY et LIMIT n'est en effet plus utile car tu sélectionne seulement les 7 derniers jours.

Calcul moyenne base de données

Qui est en ligne