Requête calcul moyenne sans les valeurs NULL

**Delba146** · 07/12/2023, 15h19

Bonjour,

Je travaille sur un projet de station meteo des capteurs branchés qui remontent des données vers une base SQL via un ESP32 et ensuite j'exploite cela pour un affichage sur une page html.

Je reçois des infos de la température pression humidité toutes les 10mn et je peux donc faire une moyenne calculer les max les min, ça marche
Je reçois également des valeurs de particules d'air mais toutes les 30mn ce qui signifie que dans ma BD j'ai 2 enregistrements sur 3 sans valeurs

Donc ma moyenne des particules se fait sur les valeurs réelles (un nombre) mais prend en compte les valeurs null qui sont considérées à 0 j'ai donc essayé avec IS NOT NULL mais maintenant le resultat de la moyenne est à 0.

Voici la fonction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
function avgReadingpm($value) {
  global $servername, $username, $password, $dbname;
  $value = (float)$value;
  var_dump($value);
   // Create connection
   $conn = new mysqli($servername, $username, $password, $dbname);
   // Check connection
   if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
   }
 
   $sql = "SELECT AVG(CAST(" . $value . " AS SIGNED)) AS avg_amount FROM SensorData WHERE (" . $value . "  IS NOT NULL ) AND (reading_time BETWEEN NOW() - INTERVAL 6 HOUR AND NOW()) ";
 
 
    if ($result = $conn->query($sql)) {
     return $result->fetch_assoc();
   }
   else {
     return false;
   }
   $conn->close();
 }

Pour info le retour de var_dump($value) est float(0) lorsqu'il n'y a pas de valeurs
Petite info supplémentaire j'ai suivi un tuto pour la création de ma BD et les valeurs sont en varchar.

Merci

**escartefigue** · 08/12/2023, 09h48

Bonjour,

Les occurrences de colonnes marquées "null" ne sont pas prises en compte dans les calculs d'agrégat

La preuve, avec ce jeu d'essais :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table T1
      (  T1IDT     integer       primary key
       , T1VAL     decimal(7,2)  
      )
;
insert into T1(T1IDT, T1VAL)
values (1, 200)
     , (2, 100)
     , (3, null)
     , (4, 300)
;
select * from T1
;
select avg(T1VAL)                as MOY
     , avg(coalesce(T1VAL, 0))   as MOY0
     , max(T1VAL)                as MX
     , MIN(T1VAL)                as MN
from T1

Le résultat est :

Nom : Sans titre.png
Affichages : 249
Taille : 3,9 Ko

On voit bien la différence entre MOY (calcul d'agrégat qui ne tient pas compte des "null") et MOY0 (calcul d'agrégat, dans lequel les colonnes marquées "null" sont remplacées par la valeur zéro)

Par ailleurs, des valeurs issues de capteurs ne devraient pas être de type FLOAT, encore moins de type VARCHAR

!
Si les valeurs sont toujours entières, alors de l'INTEGER (small, int ou big selon le max possible), sinon, il faut utiliser du DECIMAL.
Le FLOAT présente le défaut d'avoir un montant décimal approximatif, alors que le VARCHAR est inadapté pour les calculs, à réserver pour des chaînes de caractères !

**Delba146** · 09/12/2023, 11h04

Bonjour escartefigue,

Merci pour ta réponse à te lire je me demande si le fond de mon pb ce n'est pas la construction de ma BD, à l'origine j'ai suivi un tuto que voici :

https://randomnerdtutorials.com/clou...esp32-esp8266/
Avec un BD construite comme cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
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

Etant débutant j'ai suivi le tuto à la lettre, ma première question dois je créer une autre BD avec des données en DECIMAL, ou puis je continuer ainsi?

J'ai essayé de modifier la BD et passer de varchar à Decimal mais j'ai ce message :
Erreur de requête:
#1264 - Out of range value for column 'pm25' at row 534

Ligne 533 ||NULL ||2023-11-05 14:17:26
Ligne 534 || 701.30 ||730.70 ||2023-11-05 14:17:58

A partir de la ligne 534 j'ai commencé à recevoir des valeurs avant c'était NULL, c'est une impresssion ou je vais devoir supprimer ma BD et la refaire en decimal???

**Séb.** · 09/12/2023, 14h14

Etant débutant j'ai suivi le tuto à la lettre, ma première question dois je créer une autre BD avec des données en DECIMAL, ou puis je continuer ainsi?

Il faut typer correctement tes données. C'est un prérequis. Sinon tu auras toujours des problèmes, et plus tu avanceras, plus ce sera difficile à redresser.

J'ai essayé de modifier la BD et passer de varchar à Decimal mais j'ai ce message :
Erreur de requête:
#1264 - Out of range value for column 'pm25' at row 534

Ligne 533 ||NULL ||2023-11-05 14:17:26
Ligne 534 || 701.30 ||730.70 ||2023-11-05 14:17:58

Donne ta requête et un échantillon de données.

A partir de la ligne 534 j'ai commencé à recevoir des valeurs avant c'était NULL, c'est une impresssion ou je vais devoir supprimer ma BD et la refaire en decimal???

Surtout ne supprime rien. Il faut simplement correctement typer ta colonne et éventuellement préparer la conversion.

**Delba146** · 09/12/2023, 18h37

Bonjour Seb
Je ne sais pas tu me parles de la requête pour modifier ma BD dans phpmyadmin ou ma requête php qui fait le calcul de la moyenne ??

Pour la requête pour modifier la BD je fais simplement structure/modifier et je change varchar(10) par DECIMAL(5,2) que phpmyadmin traduit en

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE `SensorData` CHANGE `pm25` `pm25` DECIMAL(5,2) NULL DEFAULT NULL;

avec ce retour :
Erreur de requête:
#1292 - Truncated incorrect DECIMAL value: ''

----------------------------------------------------------
Pour la partie php que me fait les moyennes voici

La structure de la BD :
Base de données id21478987_bddatameteo
Structure de la table SensorData
Colonne Type Null Valeur par défaut Commentaires
id int(6) Non
sensor varchar(30) Non
location varchar(30) Non
value1 varchar(10) Oui NULL
value2 varchar(10) Oui NULL
value3 varchar(10) Oui NULL
value4 varchar(10) Oui NULL
pm25 varchar(10) Oui NULL
pm10 varchar(10) Oui NULL
reading_time timestamp Non current_timestamp()
Déchargement des données de la table SensorData

Un échantillon de données :

Id sesnor location Value1 Value2 Value3 Value4 Pm25 Pm10 Reading_time
14084 BME280DHTAIR Office 7.91 89.88 1003.19 7.03 271.30 280.40 2023-12-09 08:08:52
14085 BME280DHTAIR Office 7.92 89.89 1003.19 7.03 2023-12-09 08:08:54
14086 BME280DHTAIR Office 7.91 89.93 1003.10 7.03 2023-12-09 08:10:55
14087 BME280DHTAIR Office 7.91 89.93 1002.96 7.03 269.82 278.87 2023-12-09 08:13:56

Les valeurs pm25 et pm10 sont reçue toutes les 30mn les autres toutes les 10mn

Dans l'ordre en premier je récupère toutes les données de la façon suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 if (isset($_GET["readingsCount"])){
      $data = $_GET["readingsCount"];
      $data = trim($data);
      $data = stripslashes($data);
      $data = htmlspecialchars($data);

Ensuite je lance une onction pour le calcul de la moyenne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 $avg_pm25 =  avgReadingpm('pm25');

Et voici la fonction avec la requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
function avgReadingpm($value,) {
  global $servername, $username, $password, $dbname;
  $value = (float)$value;
  var_dump($value);
 
   $conn = new mysqli($servername, $username, $password, $dbname);
 
   if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
   }
       $sql = "SELECT AVG(CAST(" . $value . " AS SIGNED)) AS avg_amount FROM SensorData WHERE (" . $value . "  IS NOT NULL ) AND (reading_time BETWEEN NOW() - INTERVAL 6 HOUR AND NOW()) ";
    if ($result = $conn->query($sql)) {
     return $result->fetch_assoc();
   }
   else {
     return false;
   }
   $conn->close();
 }

Dis moi si je t'ai donné les bonnes infos

**laurentSc** · 09/12/2023, 19h57

Bonjour,
je pense que Séb parlait au post #4 de requête SQL. Tu as donné la DDL de la table au post #3. Mais elle n'est pas cohérente avec la structure que tu donnes au post #5. Par contre, pour faciliter les tests, pourrais-tu donner l'échantillon de données sous forme SQL : INSERT INTO SensorData (id, sensor, location, value1, value2, value3, value4, pm25, pm10, reading_time) VALUE("14084", "BME280DHTAIR", "Office", "7.91", "89.88", "1003.19", "7.03", "271.30", "280.40", "2023-12-09", "08:08:52"); etcMon exemple va pas vu qu'il compte 10 colonnes et 11 valeurs. A toi de corriger...

Je suis plus là ce soir...

**escartefigue** · 11/12/2023, 10h07

Bonjour Delba

Envoyé par Delba146

Bonjour escartefigue,

Merci pour ta réponse à te lire je me demande si le fond de mon pb ce n'est pas la construction de ma BD, à l'origine j'ai suivi un tuto que voici :

https://randomnerdtutorials.com/clou...esp32-esp8266/
Avec un BD construite comme cela :

Ce genre de tuto contient tout ce qu'il ne faut pas faire en matière de modélisation d'une BDD !
D'une part, comme je l'ai mentionné plus haut, le typage des colonnes est inadapté, une mesure météorologique dans une colonne de type varchar est une hérésie.
D'autre part, mettre dans une même table 4 occurrences de valeurs est une autre hérésie : ça ne convient que si tout capteur effectue systématiquement 4 et seulement 4 types de mesures.
Et comme dans le script fourni, il y a 4 mesures pour un même timestamp, il faut en plus que ces 4 mesures soient faites exactement à la même nanoseconde...
Or, un capteur de température ne mesurera que des températures, un capteur de pression que des pressions, un anémomètre que des vitesses...
Une base de données relationnelle n'est pas un tableau excel, les colonnes ne doivent pas être répétées, s'il y a plusieurs mesures, on multiplie les lignes, pas les colonnes.

**Delba146** · 11/12/2023, 16h15

Bonjour,
Merci je vais intégrer vos propositions mais j'ai besoin de temps pour tout comprendre, effectivement les tutos sont loin d'être parfait mais bon ça me permet d'appendre au fur et à mesure.
Par contre je reviens sur la remarque de escartefigue :

"Une base de données relationnelle n'est pas un tableau excel, les colonnes ne doivent pas être répétées, s'il y a plusieurs mesures, on multiplie les lignes, pas les colonnes."
Je ne suis pas certain de comprendre aujourd'hui j'ai une table qui contient plusieurs natures de mesures certaines faites toutes les 2mn certaines toutes les 5mn mais à chaque fois je crée une nouvelle ligne, est ce une erreur?

**laurentSc** · 11/12/2023, 16h36

Bonjour,
je ne sais pas ce que pensent les experts (les vrais

), mais je pense qu'il faut déjà mettre au point le modèle de bdd...A mon avis, cela va conduire à la création de 2 tables (et pas une seule), une pour la température pression humidité et une autre pour les valeurs de particules d'air...

Requête calcul moyenne sans les valeurs NULL [MySQL]

PHP & Base de données

Vue hybride

Discussions similaires

Partager

Partager