IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PHP & Base de données Discussion :

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


Sujet :

PHP & Base de données

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Homme Profil pro
    Lycéen
    Inscrit en
    Octobre 2023
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 19
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Lycéen

    Informations forums :
    Inscription : Octobre 2023
    Messages : 37
    Par défaut Requête calcul moyenne sans les valeurs NULL
    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

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 610
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 610
    Billets dans le blog
    10
    Par défaut
    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 : 231
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 !

  3. #3
    Membre averti
    Homme Profil pro
    Lycéen
    Inscrit en
    Octobre 2023
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 19
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Lycéen

    Informations forums :
    Inscription : Octobre 2023
    Messages : 37
    Par défaut
    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???

  4. #4
    Expert confirmé
    Avatar de Séb.
    Profil pro
    Inscrit en
    Mars 2005
    Messages
    5 327
    Détails du profil
    Informations personnelles :
    Âge : 47
    Localisation : France

    Informations professionnelles :
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2005
    Messages : 5 327
    Billets dans le blog
    17
    Par défaut
    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.

  5. #5
    Membre averti
    Homme Profil pro
    Lycéen
    Inscrit en
    Octobre 2023
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 19
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Lycéen

    Informations forums :
    Inscription : Octobre 2023
    Messages : 37
    Par défaut
    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

  6. #6
    Membre Expert
    Avatar de laurentSc
    Homme Profil pro
    Webmaster débutant perpétuel !
    Inscrit en
    Octobre 2006
    Messages
    10 493
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Webmaster débutant perpétuel !
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2006
    Messages : 10 493
    Billets dans le blog
    1
    Par défaut
    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...

  7. #7
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 610
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 610
    Billets dans le blog
    10
    Par défaut
    Bonjour Delba

    Citation Envoyé par Delba146 Voir le message
    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.

  8. #8
    Membre averti
    Homme Profil pro
    Lycéen
    Inscrit en
    Octobre 2023
    Messages
    37
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 19
    Localisation : France, Pas de Calais (Nord Pas de Calais)

    Informations professionnelles :
    Activité : Lycéen

    Informations forums :
    Inscription : Octobre 2023
    Messages : 37
    Par défaut
    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?

  9. #9
    Membre Expert
    Avatar de laurentSc
    Homme Profil pro
    Webmaster débutant perpétuel !
    Inscrit en
    Octobre 2006
    Messages
    10 493
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Webmaster débutant perpétuel !
    Secteur : Industrie

    Informations forums :
    Inscription : Octobre 2006
    Messages : 10 493
    Billets dans le blog
    1
    Par défaut
    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...

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Faire un paréto sans les valeurs nulles
    Par salseropom dans le forum Excel
    Réponses: 3
    Dernier message: 14/04/2020, 14h16
  2. Réponses: 8
    Dernier message: 05/08/2016, 11h27
  3. Faire une moyenne en ignorant les valeurs nulles
    Par Giansolo dans le forum MATLAB
    Réponses: 2
    Dernier message: 08/06/2007, 14h38
  4. order by sans les valeurs NULL
    Par pendragon509 dans le forum Langage SQL
    Réponses: 4
    Dernier message: 21/10/2005, 12h31
  5. Comment gérer les valeur Nulles dans une requête ?
    Par sondo dans le forum Bases de données
    Réponses: 3
    Dernier message: 16/03/2005, 11h02

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo