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

Requêtes MySQL Discussion :

Calcul consommation Jour et Heure depuis un index [MySQL-5.7]


Sujet :

Requêtes MySQL

  1. #1
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    54
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Dessinateur industriel
    Secteur : Industrie

    Informations forums :
    Inscription : février 2021
    Messages : 54
    Points : 16
    Points
    16
    Par défaut Calcul consommation Jour et Heure depuis un index
    Bonjour, totalement débutant je cherche à afficher graphiquement la consommation d'un compteur d'eau en la calculant à partir d'un index.
    L'index du compteur est transmis de façon régulière (5 à 10 min) dans une table 'meter' qui collecte chaque transmission d'index des différents compteurs : 'DeviceRowID', 'Value', 'Usage', 'Date'. Donc une nouvelle "ligne" à chaque acquisition.

    Je souhaite
    Un 1er graphique avec la consommation par heure des 7 derniers jours. Pour le graphique, il faudrait avoir l'heure au format type xx:00:00
    Un 2nd graphique avec la consommation par jour des 7 derniers jours.

    Prendre la valeur MAX de l'index - le MAX de la valeur précédente (ou de l'heure précédente ou de la journée précédente) permet d'avoir toutes les consommation et aussi l'actuelle.

    J'ai réussi à calculer la consommation entre 2 relevés :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT m.DeviceRowID, MAX(m2.Date), m.Value - MAX(m2.Value)
    FROM meter m
    LEFT JOIN meter m2
    	ON m2.DeviceRowID = m.DeviceRowID
    	AND m2.Date < m.Date
    GROUP BY m.DeviceRowID, m.Date, m.Value;
    Mais je n'arrive pas à faire la même chose pour une heure ou pour un jour :

    Consommation du J = Index max de J - Index max de J-1
    Les consommation

    Consommation de HH:00:00 = Index max de H - Index max de H-1

    Si quelqu'un pouvait m'aider à réaliser les deux requêtes et surtout à les comprendre.
    Merci beaucoup.

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 258
    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 : 7 258
    Points : 23 079
    Points
    23 079
    Billets dans le blog
    2
    Par défaut
    Bonjour,

    Pour comparer les valeurs d'une colonnes entre plusieurs lignes, il faut utiliser les fonctions LAG ou LEAD.

    voici un exemple dans ce fil de discussion

    https://www.developpez.net/forums/d2...colonne-d-vue/

  3. #3
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    54
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Dessinateur industriel
    Secteur : Industrie

    Informations forums :
    Inscription : février 2021
    Messages : 54
    Points : 16
    Points
    16
    Par défaut
    Bonjour, merci pour la réponse.
    J'ai regardé l'exemple et le détail des fonctions mais il faut indiquer une valeur de offset. Le problème est que je ne le maîtrise pas car la fréquence de relevé d'un compteur peut être variable.
    C'est pour cela que je pensait plutôt à la valeur MAX d'une journée moins MAX de la précédente. Ne sachant pas combien il y a de lignes entre les deux. C'est pareil pour la consommation par heure.

  4. #4
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 258
    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 : 7 258
    Points : 23 079
    Points
    23 079
    Billets dans le blog
    2
    Par défaut
    Est-ce que ceci convient

    Avec ce jeu d'essai :
    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
    with MS_mesure(MS_id, MS_date, MS_heure, MS_val) as
        (select 001, '2021-01-01', '08:00:17', 150   union all
         select 002, '2021-01-01', '08:55:40', 120   union all
         select 003, '2021-01-01', '10:12:26', 045   union all
         select 004, '2021-01-01', '12:00:03', 210   union all
         select 005, '2021-01-01', '17:30:24', 180   union all
         select 006, '2021-01-01', '22:11:44', 080   union all
         select 007, '2021-01-02', '04:06:55', 012   union all
         select 008, '2021-01-02', '09:18:00', 130   union all
         select 009, '2021-01-02', '13:45:23', 040   union all
         select 010, '2021-01-02', '18:45:50', 225   union all
         select 011, '2021-01-02', '23:07:58', 144   union all
         select 012, '2021-01-03', '00:06:14', 020   union all
         select 013, '2021-01-03', '03:38:16', 055   union all
         select 014, '2021-01-03', '07:00:31', 120   union all
         select 015, '2021-01-03', '08:50:30', 120
    et cette 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
    20
    21
    select M0.MS_date
         , sum(M0.MS_val) as Conso_Jour
         , max(M0.MS_val) as Max_Jour
         , Conso_P        as Conso_prec
         , Max_P          as Max_prec
         , sum(M0.MS_val) - M1.Conso_P  as ecart
    from MS_mesure M0
    left join
         (select MS_date
               , sum(MS_val) as Conso_P
               , max(MS_val) as Max_P
          from MS_mesure
          group by MS_date
         ) M1
      on M1.MS_date =
        (select max(M2.MS_date)
         from MS_mesure M2
         where M2.MS_date<M0.MS_date)     
    group by M0.MS_date
           , M1.Conso_p
           , M1.Max_P
    J'obtiens :
    MS_date Conso_Jour Max_Jour Conso_prec Max_prec ecart
    2021-01-01 785 210
    2021-01-02 551 225 785 210 -234
    2021-01-03 315 120 551 225 -236

  5. #5
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    54
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Dessinateur industriel
    Secteur : Industrie

    Informations forums :
    Inscription : février 2021
    Messages : 54
    Points : 16
    Points
    16
    Par défaut
    Merci beaucoup pour la proposition de code !
    Ce n'est pas totalement cela mais le fond est bien là.

    La première colonne "DeviceRowID" n'a pas une valeur unique, c'est l'identifiant du compteur, on le retrouve donc à chaque fois qu'il émet son index.
    Et pour la "Value', je n'ai pas besoin de faire la somme des valeurs de la journée car c'est un index qui est émit, donc il ne va qu'en augmentant ou en restant à sa valeur si jamais il n'y a aucune consommation.

    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
    INSERT INTO `meter` (`DeviceRowID`, `Value`, `Usage`, `Date`) VALUES
    (7, 971198, 0, '2021-01-25 22:45:15'),
    (8, 477042, 0, '2021-01-25 22:45:21'),
    (9, 569808, 0, '2021-01-25 22:45:32'),
    (7, 972179, 0, '2021-01-25 23:50:00'),
    (8, 477524, 0, '2021-01-25 23:50:00'),
    (9, 570384, 0, '2021-01-25 23:50:00'),
    (7, 972179, 0, '2021-01-26 01:15:10'),
    (8, 477524, 0, '2021-01-26 01:15:23'),
    (9, 571384, 0, '2021-01-26 01:15:32'),
    (7, 973324, 0, '2021-01-26 22:45:15'),
    (8, 477948, 0, '2021-01-26 22:45:21'),
    (9, 572808, 0, '2021-01-26 22:45:32'),
    (7, 973754, 0, '2021-01-27 03:50:30'),
    (8, 478848, 0, '2021-01-27 03:51:23'),
    (9, 573384, 0, '2021-01-27 03:52:11'),
    (7, 974100, 0, '2021-01-27 21:12:10'),
    (8, 478848, 0, '2021-01-27 21:12:23'),
    (9, 573984, 0, '2021-01-27 21:12:32'),
    Dans cet exemple où les index de 3 compteurs d'eau sont collectés, je cherche à obtenir la conso jour du compteur possédant l'ID 7 ce qui devrait donner en réponse :

    DeviceRowID Consommation(écart) Value Value_Prec Date
    7 1145 973324 972179 2021-01-26 00:00:00
    7 0776 974100 973324 2021-01-27 00:00:00

    J'espère être clair dans mes explications.

    Edit :

    J'ai essayé de modifier votre exemple mais je suis clairement incompétent sur ce sujet, la requête n'abouti pas, trop longue :

    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
    select M0.Date
         , max(M0.Value) as Value_Max_Jour
         , Value_P        as Value_prec
         , Max_P          as Value_Max_Jour_P
         , max(M0.Value) - M1.Value_P  as Conso_J
    from meter M0
    left join
         (select Date
               , Value as Value_P
               , max(Value) as Max_P
          from meter
          group by Date
         ) M1
      on M1.Date =
        (select max(M2.Date)
         from meter M2
         where M2.Date<M0.Date)     
    group by M0.Date
           , M1.Value_P
           , M1.Max_P

  6. #6
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 258
    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 : 7 258
    Points : 23 079
    Points
    23 079
    Billets dans le blog
    2
    Par défaut
    OK

    Du coup, plusieurs choses :
    • Comme vous êtes sous MySQL 5.7, les CTE ne sont pas utilisables, pas plus que les fonctions OLAP, ce qui ne facilite pas les choses
    • Votre table utilise des noms réservés SQL (date, value...) ce qui complique encore les choses (usage de la quote inversée obligatoire...).
      Du coup, je crée une vue avec des noms qui m'évitent de m'em#### avec les quotes inversées
    • Dans votre jeu d'essai, les dates sont contiguës, je l'ai donc modifié pour créer un trou, le test sera plus significatif


    Ce qui donne :
    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
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    CREATE TABLE `meter` 
          ( `DeviceRowID` integer
          , `Value`       decimal(7,0)
          , `Usage`       integer
          , `Date`        datetime
          ) engine=INNODB
    ;
    INSERT INTO `meter` (`DeviceRowID`, `Value`, `Usage`, `Date`) 
    VALUES (7, 971198, 0, '2021-01-25 22:45:15'),
           (8, 477042, 0, '2021-01-25 22:45:21'),
           (9, 569808, 0, '2021-01-25 22:45:32'),
           (7, 972179, 0, '2021-01-25 23:50:00'),
           (8, 477524, 0, '2021-01-25 23:50:00'),
           (9, 570384, 0, '2021-01-25 23:50:00'),
           (7, 972179, 0, '2021-01-26 01:15:10'),
           (8, 477524, 0, '2021-01-26 01:15:23'),
           (9, 571384, 0, '2021-01-26 01:15:32'),
           (7, 973324, 0, '2021-01-26 22:45:15'),
           (8, 477948, 0, '2021-01-26 22:45:21'),
           (9, 572808, 0, '2021-01-26 22:45:32'),
           (7, 973754, 0, '2021-01-28 03:50:30'),
           (8, 478848, 0, '2021-01-28 03:51:23'),
           (9, 573384, 0, '2021-01-28 03:52:11'),
           (7, 974100, 0, '2021-01-28 21:12:10'),
           (8, 478848, 0, '2021-01-28 21:12:23'),
           (9, 573984, 0, '2021-01-28 21:12:32')
    ;    
    select * from `meter` 
    ;
    create view MS_EXTR as
          (select `DeviceRowID`  as MS_Ident
                 , max(`Value`)  as MS_Value
                 , date(`Date`)  as MS_Date
            from `meter` 
            group by `DeviceRowID` 
                   , date(`Date`)
            order by `DeviceRowID` 
                   , date(`Date`)
           )
    ;
    Ensuite, cette 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
    20
    select M0.MS_Ident     as "DeviceRowID"
         , M0.MS_Date      as "Date"
         , M0.MS_Value     as "Conso J" 
         , M1.Conso_P      as "Conso P"
         , M0.MS_Value - M1.Conso_P   as "Ecart"
    from   MS_EXTR M0
    left join 
         ( select MS_Ident
                , MS_Date
                , MS_Value as Conso_P
           from MS_EXTR
         ) M1
       on M1.MS_Ident = M0.MS_Ident
      and M1.MS_Date =
         ( select max(M2.MS_Date)
           from   MS_EXTR M2
           where M2.MS_ident=M0.MS_Ident
             and M2.MS_Date <M0.MS_Date
         )
    ;
    Donne :
    DeviceRowID Date Conso J Conso P Ecart
    7 2021-01-25 972179
    7 2021-01-26 973324 972179 1145
    7 2021-01-28 974100 973324 776
    8 2021-01-25 477524
    8 2021-01-26 477948 477524 424
    8 2021-01-28 478848 477948 900
    9 2021-01-25 570384
    9 2021-01-26 572808 570384 2424
    9 2021-01-28 573984 572808 1176

  7. #7
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    54
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Dessinateur industriel
    Secteur : Industrie

    Informations forums :
    Inscription : février 2021
    Messages : 54
    Points : 16
    Points
    16
    Par défaut
    C'est exactement cela, c'est vraiment parfait et vous remercie vraiment énormément. La réponse est extrêmement rapide en plus.
    J'aurai bien aimé réussir à comprendre un minimum :

    Je ne suis qu'au début de mon projet, je ne suis pas du tout développeur mais dessinateur et automaticien industriel et je me permet de vous demander des conseils suite à vos remarques sur la version de MySQL, ainsi que le noms des colonnes.
    Pour faire ce projet et découvrir le 'code' html, css, php, j'ai installé EasyPHP avec mySQL sur mon PC. La finalité c'est d'avoir ceci en local sur un micro PC type Raspberry ou équivalent afin d'avoir un logger local et autonome. Je récupérerai les données d'un récepteur radio via NodeRED en Modbus RS485.


    1) La version de MySQL pourra peut être être plus récente, dans ce cas la requête sera t'elle plus "simple" comme vous le précisez (CTE, OLAP ?)
    Dans cette hypothèse quelle serait sa forme ?

    2) Quels noms donneriez vous pour éviter les noms réservés car il est encore temps de changer..

    3) Pouvez vous m'expliquer la démarche, à quoi sert le create VIEW, que sont les M0, M1 et M2, car je n'ai créé aucun index et cela fonctionne.

    Je le répète, je suis vraiment débutant et espère ne pas trop en demander. Je cherche surtout à comprendre le résultat. Je devrai l'appliquer pour le calcul de consommation à l'heure.
    Merci énormément pour votre aide et votre temps.

  8. #8
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 258
    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 : 7 258
    Points : 23 079
    Points
    23 079
    Billets dans le blog
    2
    Par défaut
    Citation Envoyé par makimax Voir le message
    1) La version de MySQL pourra peut être être plus récente, dans ce cas la requête sera t'elle plus "simple" comme vous le précisez (CTE, OLAP ?)
    Dans cette hypothèse quelle serait sa forme ?
    La version 8 de MySQL a intégré de nombreuses améliorations (avec 15 ans de retard, mais mieux vaut tard que jamais )
    Parmi lesquelles les fonctions OLAP qui permettent de faire des classements et facilitent la vie dans bien des cas.
    A été ajouté également la prise en compte des Common Table Expression (CTE) qui simplifient la création de jeu d'essai, permettent de mutualiser les requêtes imbriquées et permettent de créer des requêtes récursives, ce qui est impossible sans CTE.


    Citation Envoyé par makimax Voir le message
    2) Quels noms donneriez vous pour éviter les noms réservés car il est encore temps de changer.
    La liste des noms réservés est disponible ici : https://sqlpro.developpez.com/cours/motsreserves/
    Quand on utilise un nom réservé, ça rend la syntaxe des requêtes plus pénible ainsi que la recherche dans les scripts de création des objets.
    Pour ma part, en usage privé c'est à dire quand aucune norme de nommage ne m'est imposée, j'utilise systématiquement un préfixe lié à chaque table.
    Exemple : soit une table des clients et une table des commandes, sachant qu'un client peut avoir plusieurs commandes et qu'une commande n'appartient qu'à un et un seul client. Je nomme mes tables et colonnes ainsi (PK soulignées, FK suffixées #)
    CL_CLIENT(CL_ident, CL_nom, CL_prenom, CL_dtnais etc.)
    CO_COMMANDE(CO_ident, CO_DATE, CL_ident#)
    ainsi, d'un seul coup d'oeil, je vois que la colonne FK CL_ident de la table CO_COMMANDE est issue de la table CL_CLIENT
    Et l'ajout de ce préfixe fait que, automatiquement, il n'y a pas de risque d'utiliser un mot réservé SQL, du coup, plus besoin de séparateur (quote inversée) dans les requêtes


    Citation Envoyé par makimax Voir le message
    3) Pouvez vous m'expliquer la démarche, à quoi sert le create VIEW, que sont les M0, M1 et M2, car je n'ai créé aucun index et cela fonctionne.
    Une vue est une couche logique appliquée sur une table.
    Il n'y a pas besoin d'index supplémentaires, les index utilisés sont ceux de la table.
    M0, M1 et M2 sont des alias : quand on utilise plusieurs tables ou vues dans une requête, on peut soit utiliser leur nom complet partout, soit, pour se faciliter la vie, utiliser un raccourci, un "alias", par exemple M0 plutôt que MS_EXTR.
    Dans le cas où une même table ou une même vue est utilisée plusieurs fois dans une même requête, ce qui est le cas dans ma requête, on est obligé d'utiliser des alias, puisque le même nom apparaît plusieurs fois. C'est pourquoi j'ai utilisé successivement l'alias M0 pour la requête principale, M1 pour aller rechercher la valeur précédente et M2 pour vérifier que la valeur extraite est bien celle de la dernière mesure pour la date précédente.


    Pour remercier les contributions qui ont pu vous aider, utilisez le pouce vert prévu à cet effet sous ces réponses

  9. #9
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    54
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Dessinateur industriel
    Secteur : Industrie

    Informations forums :
    Inscription : février 2021
    Messages : 54
    Points : 16
    Points
    16
    Par défaut
    Merci pour toutes ces explications.
    Je vais regarder pour passer en version 8 si possible, et si ça l'est sur la destination finale du projet (en théorie un RPI 3), pour le moment je vais continuer le projet dans cette version 5.7 et votre requête.
    Si c'est possible, pouvez vous me dire où mettre le WHERE DeviceRowID = 7 (ou M0.MS_ident) pour isoler les consommation du compteur 7, j'ai tenté de le mettre en AND à la fin en série avec les 2 conditions sur M2 mais forcément cela ne joue que sur le JOIN, et si je le met au début sur la première sélection après le FROM en fin de ligne 6, j'ai une erreur . Et aussi quelle aurait été la requête en utilisant cette dernière version ?

    Pour les tables et les noms, je pense qu'il vaut mieux que je les renomme car même si le projet est bien avancé, il vaut mieux que ce soit "propre" le plus tôt possible. Je vais utiliser votre méthode car c'est un projet personnel, sans contraintes si ce n'est celle d'apprendre, et rien ne vaut l'expérience et je pense que de ce côté je peut largement m'appuyer sur la vôtre !

    Pour la vue et les alias, c'est plus clair, mais je ne comprends qu'en partie pour M0, M1, M2. C'est finalement la même colonne mais "virtuelle" issue du résultat précédent. Je vais tenter de la reproduire pour la consommation à l'heure et peut être je comprendrais mieux, je l'espère mais ce n'est pas gagné.

    J'ai d'autres requêtes dans mon projets, beaucoup plus simple que j'ai réalisées et qui fonctionne, mais elles mettent plus de temps à être traitées que la vôtre beaucoup plus complexe, est-ce parce que je ne passe pas par les vues / alias ?

    En tous cas énorme et encore merci.

  10. #10
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 258
    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 : 7 258
    Points : 23 079
    Points
    23 079
    Billets dans le blog
    2
    Par défaut
    Bonjour Makimax

    Citation Envoyé par makimax Voir le message
    Si c'est possible, pouvez vous me dire où mettre le WHERE DeviceRowID = 7 (ou M0.MS_ident) pour isoler les consommation du compteur 7
    Les restrictions se codent juste après les jointures, comme ci-dessous :
    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
    select M0.MS_Ident     as "DeviceRowID"
         , M0.MS_Date      as "Date"
         , M0.MS_Value     as "Conso J" 
         , M1.Conso_P      as "Conso P"
         , M0.MS_Value - M1.Conso_P   as "Ecart"
    from   MS_EXTR M0
    left join 
         ( select MS_Ident
                , MS_Date
                , MS_Value as Conso_P
           from MS_EXTR
         ) M1
       on M1.MS_Ident = M0.MS_Ident
      and M1.MS_Date =
         ( select max(M2.MS_Date)
           from   MS_EXTR M2
           where M2.MS_ident=M0.MS_Ident
             and M2.MS_Date <M0.MS_Date
         )
    where M0.MS_Ident=7     
    ;


    Citation Envoyé par makimax Voir le message
    Pour les tables et les noms, je pense qu'il vaut mieux que je les renomme car même si le projet est bien avancé, il vaut mieux que ce soit "propre" le plus tôt possible.
    Oui, en tout cas, à minima, éviter les noms réservés est une sage précaution.
    Il est préférable d'éviter aussi les caractères spéciaux : pas d'accent, de cédille ni de ligature dans les noms
    Pas non plus de combinaison majuscule/minuscule



    Citation Envoyé par makimax Voir le message
    Pour la vue et les alias, c'est plus clair, mais je ne comprends qu'en partie pour M0, M1, M2. C'est finalement la même colonne mais "virtuelle" issue du résultat précédent. Je vais tenter de la reproduire pour la consommation à l'heure et peut être je comprendrais mieux, je l'espère mais ce n'est pas gagné.
    Dans la requête, on lit plusieurs fois la même vue (et donc la même table)
    Une première fois, avec un alias M0, pour rechercher le numéro de device, la date et la conso du jour
    Une deuxième fois, avec un alias M1, grâce à une jointure externe. Cette jointure externe requièrt une requête imbriquée toujours sur cette même vue pour vérifier qu'on est sur la mesure précédente, c'est l'alias M2.
    Comme on utilise 3 fois la même table, il faut affecter un nom différent pour chaque utilisation, sinon, l'interpréteur ne pourrait pas savoir dans laquelle des 3 on récupère le contenu de telle ou telle colonne.



    Citation Envoyé par makimax Voir le message
    J'ai d'autres requêtes dans mon projets, beaucoup plus simple que j'ai réalisées et qui fonctionne, mais elles mettent plus de temps à être traitées que la vôtre beaucoup plus complexe, est-ce parce que je ne passe pas par les vues / alias ?
    Absolument pas. Les performances des requêtes sont liées pour l'essentiel à la volumétrie traitée, à la présence d'index et à l'utilisation de prédicats dits "sargables" pour les critères de jointure et de filtrage.
    Si des requêtes sont trop lentes, il faut ouvrir un nouveau sujet en publiant la requête avec le script DDL de création des tables en jeu et de leurs index (ordres CREATE TABLE et CREATE INDEX).

  11. #11
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 258
    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 : 7 258
    Points : 23 079
    Points
    23 079
    Billets dans le blog
    2
    Par défaut
    En complément

    Citation Envoyé par makimax Voir le message
    Et aussi quelle aurait été la requête en utilisant cette dernière version ?
    Avec les CTE apportées par la V8, quand on veut créer un petit jeu d'essai, c'est assez pratique.
    Ci dessous je crée une CTE que je nomme "TAB0" avec les mêmes noms de colonnes que ceux que vous avez choisi (du coup j'ai conservé aussi bien sûr les quotes inversées). J'aurai pu changer les noms dès ce stade, mais c'est pour faciliter la mise en correspondance entre votre table et ma solution :
    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
    With TAB0 (`DeviceRowID`, `Value`, `Usage`, `Date`) as 
         (select 7, 971198, 0, '2021-01-25 22:45:15'   union all
          select 8, 477042, 0, '2021-01-25 22:45:21'   union all
          select 9, 569808, 0, '2021-01-25 22:45:32'   union all
          select 7, 972179, 0, '2021-01-25 23:50:00'   union all
          select 8, 477524, 0, '2021-01-25 23:50:00'   union all
          select 9, 570384, 0, '2021-01-25 23:50:00'   union all
          select 7, 972179, 0, '2021-01-26 01:15:10'   union all
          select 8, 477524, 0, '2021-01-26 01:15:23'   union all
          select 9, 571384, 0, '2021-01-26 01:15:32'   union all
          select 7, 973324, 0, '2021-01-26 22:45:15'   union all
          select 8, 477948, 0, '2021-01-26 22:45:21'   union all
          select 9, 572808, 0, '2021-01-26 22:45:32'   union all
          select 7, 973754, 0, '2021-01-28 03:50:30'   union all
          select 8, 478848, 0, '2021-01-28 03:51:23'   union all
          select 9, 573384, 0, '2021-01-28 03:52:11'   union all
          select 7, 974100, 0, '2021-01-28 21:12:10'   union all
          select 8, 478848, 0, '2021-01-28 21:12:23'   union all
          select 9, 573984, 0, '2021-01-28 21:12:32'
         )

    Après cette clause "WITH", je peux faire des requêtes sur cette CTE comme s'il s'agissait d'une table et je peux donc aussi faire d'autres CTE, qui s'appuient sur cette première CTE "TAB0", c'est ce que je fais ci-dessous pour construire la solution :
    - TAB1 me permet de ne garder que la dernière valeur pour chaque jour et chaque device
    - TAB2 me permet, gràce à la fonction row_number, d'attribuer un rang à chaque journée
    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
       , TAB1(MS_ident, MS_value, MS_date) as
        (select `DeviceRowID`
              , max(`Value`)
              , DATE(`Date`)
         from TAB0
         group by  `DeviceRowID`
                ,  DATE(`Date`)
     
        )
       , TAB2(MS_ident, MS_value, MS_date, MS_rn) as
        (select MS_ident
              , MS_value
              , MS_date
              , row_number()
                over(partition by MS_ident
                     order by MS_date asc)
         from TAB1
        )
    Le code commence par une virgule, car c'est la suite de la clause "WITH" (with TAB0, TAB1, TAB2)

    Immédiatement après avoir construit mes CTE, je code ma requête pour produire le résultat attendu, la recherche de la journée précédente est facilitée par la présence du rang calculé dans TAB2 :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select TJ.MS_ident  as   "DeviceRowID"
         , TJ.MS_date   as   "Date"
         , TJ.MS_value  as   "Conso J"
         , TP.MS_value  as   "Conso P"
         , TJ.MS_Value - TP.MS_value   as "Ecart"
    from TAB2 TJ
    left join TAB2 TP
       on TP.MS_ident=TJ.MS_ident
      and TP.MS_RN   = TJ.MS_RN-1
    where TJ.MS_Ident=7
    Et le résultat est identique :

    DeviceRowID Date Conso J Conso P Ecart
    7 2021-01-25 972179
    7 2021-01-26 973324 972179 1145
    7 2021-01-28 974100 973324 776

  12. #12
    Membre éclairé
    Femme Profil pro
    Service informatique presque à moi seule (TPE), ex-architecte fonctionnel
    Inscrit en
    août 2017
    Messages
    351
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 53
    Localisation : France, Gard (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Service informatique presque à moi seule (TPE), ex-architecte fonctionnel
    Secteur : Finance

    Informations forums :
    Inscription : août 2017
    Messages : 351
    Points : 885
    Points
    885
    Par défaut
    Je suis curieuse ; s'agit-il de compteurs d'usine ou de réseau ?
    Attention le cas échéant au nombre de roues (tour de compteur) et poses/déposes (mais puisque ce n'est pas un projet professionnel, ce n'est pas trop grave. Sans quoi il eût fallu faire la distinction entre le point du réseau où l'on compte et l'appareil de comptage lui-même).

    Pour un calcul de la consommation horaire ; est-ce que le délai entre deux relevés est très variable ?
    Et le débit important ?
    Si le débit des canalisations varie beaucoup dans la journée, il faudrait peut-être, pour une consommation horaire glissante, rechercher pour chaque relevé l'index à H-1, et ramener la consommation à une valeur strictement horaire par une règle de 3. C'est moins simple qu'une consommation par journée, où une différence de quelques minutes sera peu sensible (surtout aux alentours de minuit...) Tandis qu'une amplitude de 5 minutes en plus ou en moins sur 1 heure dans une plage de forte consommation, ça va jouer...
    Les problèmes sont des opportunités en vêtements de travail. Henry H. Kaiser
    Il n'est pas de problème dont une absence de solution ne finisse par venir à bout. Henri Queuille

  13. #13
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    54
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Dessinateur industriel
    Secteur : Industrie

    Informations forums :
    Inscription : février 2021
    Messages : 54
    Points : 16
    Points
    16
    Par défaut
    Citation Envoyé par escartefigue Voir le message
    En complément

    Avec les CTE apportées par la V8, quand on veut créer un petit jeu d'essai, c'est assez pratique.
    Ci dessous je crée une CTE que je nomme "TAB0" avec les mêmes noms de colonnes que ceux que vous avez choisi (du coup j'ai conservé aussi bien sûr les quotes inversées). J'aurai pu changer les noms dès ce stade, mais c'est pour faciliter la mise en correspondance entre votre table et ma solution :
    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
    With TAB0 (`DeviceRowID`, `Value`, `Usage`, `Date`) as 
         (select 7, 971198, 0, '2021-01-25 22:45:15'   union all
          select 8, 477042, 0, '2021-01-25 22:45:21'   union all
          select 9, 569808, 0, '2021-01-25 22:45:32'   union all
          select 7, 972179, 0, '2021-01-25 23:50:00'   union all
          select 8, 477524, 0, '2021-01-25 23:50:00'   union all
          select 9, 570384, 0, '2021-01-25 23:50:00'   union all
          select 7, 972179, 0, '2021-01-26 01:15:10'   union all
          select 8, 477524, 0, '2021-01-26 01:15:23'   union all
          select 9, 571384, 0, '2021-01-26 01:15:32'   union all
          select 7, 973324, 0, '2021-01-26 22:45:15'   union all
          select 8, 477948, 0, '2021-01-26 22:45:21'   union all
          select 9, 572808, 0, '2021-01-26 22:45:32'   union all
          select 7, 973754, 0, '2021-01-28 03:50:30'   union all
          select 8, 478848, 0, '2021-01-28 03:51:23'   union all
          select 9, 573384, 0, '2021-01-28 03:52:11'   union all
          select 7, 974100, 0, '2021-01-28 21:12:10'   union all
          select 8, 478848, 0, '2021-01-28 21:12:23'   union all
          select 9, 573984, 0, '2021-01-28 21:12:32'
         )
    La TAB0 est bien créé à partir des données de toutes la table j'imagine, pas uniquement une sélection.

    Après cette clause "WITH", je peux faire des requêtes sur cette CTE comme s'il s'agissait d'une table et je peux donc aussi faire d'autres CTE, qui s'appuient sur cette première CTE "TAB0", c'est ce que je fais ci-dessous pour construire la solution :
    - TAB1 me permet de ne garder que la dernière valeur pour chaque jour et chaque device
    - TAB2 me permet, gràce à la fonction row_number, d'attribuer un rang à chaque journée
    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
       , TAB1(MS_ident, MS_value, MS_date) as
        (select `DeviceRowID`
              , max(`Value`)
              , DATE(`Date`)
         from TAB0
         group by  `DeviceRowID`
                ,  DATE(`Date`)
     
        )
       , TAB2(MS_ident, MS_value, MS_date, MS_rn) as
        (select MS_ident
              , MS_value
              , MS_date
              , row_number()
                over(partition by MS_ident
                     order by MS_date asc)
         from TAB1
        )
    Je vois que comme pour la première solution des alias sont aussi créés, facultatifs j'ai l'impression car ils portent le même nom entre les différente TAB
    Cette première partie n'est à réalisé qu'une seule fois alors comme la création de la vue pour la première version ?

    Immédiatement après avoir construit mes CTE, je code ma requête pour produire le résultat attendu, la recherche de la journée précédente est facilitée par la présence du rang calculé dans TAB2 :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select TJ.MS_ident  as   "DeviceRowID"
         , TJ.MS_date   as   "Date"
         , TJ.MS_value  as   "Conso J"
         , TP.MS_value  as   "Conso P"
         , TJ.MS_Value - TP.MS_value   as "Ecart"
    from TAB2 TJ
    left join TAB2 TP
       on TP.MS_ident=TJ.MS_ident
      and TP.MS_RN   = TJ.MS_RN-1
    where TJ.MS_Ident=7
    Cette requête est en effet plus claire que la précédente mais quand même, respect !

    Merci pour les explications plus détaillée de la première solution que je comprends mieux désormais. Comme je vais renommer les colonnes et aussi la vue je pense, cela me permettra de voir si j'arrive à la réécrire donc 1000 fois merci !
    Ce nom de vue et M0, M1, M2, ... peuvent-ils être réutilisées pour une autre requête (sachant que j'ai besoin des même données), car sur ma page des graphiques de conso Jours, je mettrai aussi ceux pour les consos à l'heure et au mois, les requêtes serons donc enchaînées dans le code pour les récupérer respectivement via un echo au format JSON pour Highcharts.

    Pour les requêtes simple qui sont lentes, je vais avancer dans mon projet car c'est peut être sur la partie php que je fais des erreurs, vu que je suis vraiment un débutant total, cela ne m'étonnerai pas. Je le verrai bien en utilisant votre requête qui est très réactive dans PhpMyAdmin, peut être aussi qu'avec EasyPhP ca ralentit plutôt qu'une installation plus sérieuse. J'ai lu aussi que les INDEX sont là pour cela, à moi de me documenter et essayer. Aussi peut-être la longueur des VARCHAR à un impact, bref, c'est un métier !


    @Paraffine :
    Compteurs d'usine ou réseau, quelle différence faites vous. Ce seront des compteurs à différents points de consommation, dans un bâtiment. Les logs se feront uniquement en local donc la fréquence sera à régler sur les émetteurs, suivant l'utilité, je pense que 10 ou 15 min sera parfait, une définition suffisante pour les éventuels analyse à effectuer. La durée entre 2 relevés est donc constante et non variable (si ce n'est un changement de ma part)
    La simple différence d'index à l'heure ou au mois est suffisante car la fréquence de relevé d'index est assez élevé. Je le ferai aussi pour chaque données mais ceci uniquement dans une page de diagnostique afin de visualiser les derniers relevés et voir que la communication se fait bien. On calcul bien un volume, pas un débit.


    @escartefigue : Encore un grand merci.

  14. #14
    Membre éclairé
    Femme Profil pro
    Service informatique presque à moi seule (TPE), ex-architecte fonctionnel
    Inscrit en
    août 2017
    Messages
    351
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 53
    Localisation : France, Gard (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Service informatique presque à moi seule (TPE), ex-architecte fonctionnel
    Secteur : Finance

    Informations forums :
    Inscription : août 2017
    Messages : 351
    Points : 885
    Points
    885
    Par défaut
    Vu la fréquence des relevés, je pensais à des comptages de grosses consommations.

    Citation Envoyé par makimax Voir le message
    La durée entre 2 relevés est donc constante et non variable (si ce n'est un changement de ma part)
    Dans ce cas on peut effectivement déterminer le relevé de l'heure précédente et réutiliser le même mécanisme avec le calcul de rangs, la comparaison se fera toujours sur un rang n-6 ou n-4 ; il faudra intervenir sur la requête en cas de changement du rythme de relève.

    Sans vouloir piétiner les plate-bandes d'Escartefigue : dans la requête cible on n'a pas besoin de TAB0 ; on peut utiliser directement la table des relevés.
    Ici le CTE avec TAB0 a permis de composer un jeu d'essai.
    Avec les CTE, on peut faire une étape préparatoire si besoin, pour filtrer les enregistrements (faut-il exclure certains relevés? certains compteurs?), faire des calculs intermédiaires...

    Les trois parties forment une seule requête ; la portée des alias ne dépasse pas la requête.
    Les problèmes sont des opportunités en vêtements de travail. Henry H. Kaiser
    Il n'est pas de problème dont une absence de solution ne finisse par venir à bout. Henri Queuille

  15. #15
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 258
    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 : 7 258
    Points : 23 079
    Points
    23 079
    Billets dans le blog
    2
    Par défaut
    bonjour,

    Citation Envoyé par Paraffine Voir le message
    Sans vouloir piétiner les plate-bandes d'Escartefigue : dans la requête cible on n'a pas besoin de TAB0 ; on peut utiliser directement la table des relevés.
    Ici le CTE avec TAB0 a permis de composer un jeu d'essai.
    Il n'y a pas de chasse gardée : nous sommes dans un forum, toute contribution est donc la bien venue
    Effectivement, TAB0 me facilite la vie pour créer un jeu d'essai en m'évitant les opérations CREATE TABLE puis INSERT
    Quand la table existe déjà et contient des données, cette étape est donc inutile (sauf à vouloir créer un jeu d'essai particulier)

  16. #16
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    54
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Dessinateur industriel
    Secteur : Industrie

    Informations forums :
    Inscription : février 2021
    Messages : 54
    Points : 16
    Points
    16
    Par défaut
    Bonjour et merci à vous,
    Je vais rester sur la 1ère solution de escartefigue car compatible avec ma version de MySQL actuelle installée par EasyPHP (Devserver 17)

    Citation Envoyé par Paraffine Voir le message
    Vu la fréquence des relevés, je pensais à des comptages de grosses consommations.


    Dans ce cas on peut effectivement déterminer le relevé de l'heure précédente et réutiliser le même mécanisme avec le calcul de rangs, la comparaison se fera toujours sur un rang n-6 ou n-4 ; il faudra intervenir sur la requête en cas de changement du rythme de relève.
    La formule des différences est la plus simple et efficace. Mais je ne comprends pas votre remarque pour les rang car sauf erreur de ma part, pour la requête de calcul au jour, on regarde les dates, pas le nombre de rangs, afin justement d'éviter d'être dépendant du nombre de valeurs dans la journée.


    Sans vouloir piétiner les plate-bandes d'Escartefigue : dans la requête cible on n'a pas besoin de TAB0 ; on peut utiliser directement la table des relevés.
    Ici le CTE avec TAB0 a permis de composer un jeu d'essai.
    J'en prends note dans l'hypothèse ou je viens à l'utiliser et donc avoir la version 8 mais je ne pense pas. Déjà que j'aille au bout de mon projet, je fais avec mon niveau de débutant et ce que je comprends, et heureusement escartefigue m'a bien aidé sinon, il est vrai que face à un blocage, on est vite démoralisé.




    Avec les CTE, on peut faire une étape préparatoire si besoin, pour filtrer les enregistrements (faut-il exclure certains relevés? certains compteurs?), faire des calculs intermédiaires...
    Les trois parties forment une seule requête ; la portée des alias ne dépasse pas la requête.
    Je vais avoir peut être 15 ou 15 compteurs, tous remplissant cette table 'meter' à une fréquence fixe de 10 ou 15 min.
    Mon objectif est une page d'accueil avec un tableau, une ligne par compteur que j'aurai rendu "visible" dans une page de configuration afin de les y retrouver. en colonne j'y indiquerai la consommation du jour, de la veille, de la semaine et du mois.
    Cela fait depuis un bon récapitulatif.
    Et chaque ligne aura son lien vers une page de graphiques de type colonnes (7 derniers jours, 30 derniers jour, sur l'année, consommation à l'heure ou au jour suivant le graphique) et éventuellement un autre en type ligne avec la consommation entre chaque relevé (dernier relevé - le précédent), plutôt pour vérifier que cela fonctionne bien.

    On est bien d'accord que je vais avoir plusieurs requêtes avec à la fin des variantes, par exemple pour les 7 derniers jours :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE M0.MS_Ident = '{$id}' AND M0.MS_Date > (NOW() - INTERVAL 8 DAY)

    @escartefigue : Je vais tenter de faire la consommation par heure en repartant de votre 1ère proposition avec la vue. je préfère essayer mais je ne promet pas que j'y arriverai et donc que je ne vous embêterai plus .
    Ai-je le droit de réutiliser la même vue et les mêmes alias pour une seconde requête qui sera effectuée sur la même page php?

  17. #17
    Membre éclairé
    Femme Profil pro
    Service informatique presque à moi seule (TPE), ex-architecte fonctionnel
    Inscrit en
    août 2017
    Messages
    351
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 53
    Localisation : France, Gard (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Service informatique presque à moi seule (TPE), ex-architecte fonctionnel
    Secteur : Finance

    Informations forums :
    Inscription : août 2017
    Messages : 351
    Points : 885
    Points
    885
    Par défaut Bonjour à tous,
    Citation Envoyé par makimax Voir le message
    Mais je ne comprends pas votre remarque pour les rang car sauf erreur de ma part, pour la requête de calcul au jour, on regarde les date, par le nombre de rangs, afin justement d'éviter d'être dépendant du nombre de valeurs dans la journée.
    Désolée, j'en étais restée à la solution MySQL version 8 avec le over partition, pour un calcul de la consommation horaire.
    Les problèmes sont des opportunités en vêtements de travail. Henry H. Kaiser
    Il n'est pas de problème dont une absence de solution ne finisse par venir à bout. Henri Queuille

  18. #18
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    7 258
    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 : 7 258
    Points : 23 079
    Points
    23 079
    Billets dans le blog
    2
    Par défaut
    Citation Envoyé par makimax Voir le message
    @escartefigue : Je vais tenter de faire la consommation par heure en repartant de votre 1ère proposition avec la vue. je préfère essayer mais je ne promet pas que j'y arriverai et donc que je ne vous embêterai plus .
    Ai-je le droit de réutiliser la même vue et les mêmes alias pour une seconde requête qui sera effectuée sur la même page php?
    Tout à fait

  19. #19
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    54
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : France, Ille et Vilaine (Bretagne)

    Informations professionnelles :
    Activité : Dessinateur industriel
    Secteur : Industrie

    Informations forums :
    Inscription : février 2021
    Messages : 54
    Points : 16
    Points
    16
    Par défaut
    Ma page d'accueil étant un tableau récapitulatif comme ci-dessous :

    Nom Index Conso Aujoud'hui Conso Hier Conso 30 jours Conso 365 jours Batterie Graphique
    Compteur 7 7672843 1161557 lien vers charts.php?id=7
    Compteur 8 3769418 570648 lien vers charts.php?id=8

    S'il vous plaît, pouvez vous me guider vers la bonne méthode à employer pour le compléter car celui-ci intègre des données de 2 tables différentes.
    A ce jour, je l'ai créé après une requête simple sur une seule table "devicestatus' (qui contient les informations de compteurs), pour récuperer l'ID unique, le Nom, la valeur d'index actuelle et l'état de la batterie.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    $stmt = $pdo->prepare('SELECT * FROM devicestatus ORDER BY dev_id');
    $stmt->execute();
    $devices = $stmt->fetchAll(PDO::FETCH_ASSOC);
    J'ai donc fait ensuite une <table>, mis mes titres des colonnes dans le <thead> et pour le contenu <tbody> j'ai commencé par un foreach pour que chaque compteur créer une ligne dans ce tableau.

    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
    <tbody>
        <?php foreach ($devices as $device): ?>
        <tr>
            <td><a href="./devcharts.php?id=<?=$device['dev_id']?>" style='width: 200px' class="btn btn-outline-primary"><?=$device['dev_name']?></a></td>
            <td><?=$device['dev_nvalue']?></td>
            <td></td>
            <td></td>
            <td></td>
            <td></td>
            <td><?=$device['dev_battery_level']?></td>
            <td class="actions">
                   <a href="./devcharts.php?id=<?=$device['dev_id']?>">
                        <i class="fa fa-eye color-muted"></i>
                   </a>
            </td>
        </tr>
    <?php endforeach; ?>
    </tbody>
    Mon problème est que tel que j'ai démarré, je n'ai pas la possibilité de mettre par exemple la valeur de consommation du jour de chaque compteur que je récupère avec la requête de escartefigue

    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
    SELECT 
        M0.MS_Ident as 'meter_device_row_id',
        M0.MS_Date as 'meter_logdate',
        M0.MS_Value as 'Conso J',
        M1.Conso_P as 'Conso P',
        M0.MS_Value - M1.Conso_P as 'Ecart'
    FROM MS_EXTR M0
    LEFT JOIN
        (SELECT 
            MS_Ident,
            MS_Date,
            MS_Value as Conso_P
        FROM MS_EXTR
        ) M1
        ON M1.MS_Ident = M0.MS_Ident
        AND M1.MS_Date = ( SELECT max(M2.MS_Date)
                            FROM MS_EXTR M2
                            WHERE M2.MS_ident = M0.MS_Ident
                            AND M2.MS_Date < M0.MS_Date
        )
            WHERE M0.MS_Date > (NOW() - INTERVAL 1 DAY)");
    J'ai bien les résultats mais ils proviennent d'une seconde requête, il faudrait que je puisse mettre la valeur de consommation de chacun des compteurs le <td></td> le concernant, et faire un autre foreach ne va pas aller du tout. Et même si il y a un moyen, cela veut dire une autre requête pour les mois dans un autre résultat, et année aussi.

    Est-ce que ce ne serait pas plus "simple" d'avoir toutes les informations dans une table (ou vue ?), ce qui voudrait dire qu'il faudrait avoir dans une seule requête les calculs pour la consommation du jour (celle de escartefigue) et aussi ceux du mois et de l'année et en y ajoutant le nom, le niveau de batterie provenant de l'autre table (JOIN ?).

    meter_device_row_id Date Index actuel Index JP Index MP Index AP Conso J Conso M Conso A dev_name dev_battery_level
    7 2021-01-28 974100 973324 900000 800000 776 74100 174100 Compteur 7 255
    8 2021-01-28 478848 477948 400000 300000 900 78848 178848 Compteur 8 255
    9 2021-01-28 573984 572808 500000 400000 1176 73984 173984 Compteur 9 255


    Important de finir par un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    WHERE M0.MS_Date > (NOW() - INTERVAL 1 DAY)
    pour filtrer la ligne la plus récente pour chaque compteur et donc la valeur à jour pour chaque calcul.

    Je ne sais pas si c'est possible, ou si vous avez une autre idée ?

  20. #20
    Membre éclairé
    Femme Profil pro
    Service informatique presque à moi seule (TPE), ex-architecte fonctionnel
    Inscrit en
    août 2017
    Messages
    351
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Âge : 53
    Localisation : France, Gard (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Service informatique presque à moi seule (TPE), ex-architecte fonctionnel
    Secteur : Finance

    Informations forums :
    Inscription : août 2017
    Messages : 351
    Points : 885
    Points
    885
    Par défaut
    Chaque calcul de consommation (du jour, de la veille, sur 30 jours...) peut faire l'objet d'une vue. Et la requête "assemblera" alors les différentes vues pour récupérer tous les morceaux en une seule fois . Il faut éventuellement faire attention au type de jointure (sur compteur et date), dans la mesure où l'on peut ignorer certaines consommations (compteur posé récemment n'ayant pas de consommation annuelle).

    Quant à reparcourir le tableau... ça se ferait plutôt en php en enrichissant $devices avant la gestion d'affichage, mais ce n'est pas la meilleure option. Les bases relationnelles sont faites pour ce boulot !
    Les problèmes sont des opportunités en vêtements de travail. Henry H. Kaiser
    Il n'est pas de problème dont une absence de solution ne finisse par venir à bout. Henri Queuille

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 4 1234 DernièreDernière

Discussions similaires

  1. Réponses: 3
    Dernier message: 09/09/2010, 17h47
  2. Calculer le nombre de jours, d'heures et de minutes entre deux timestamp.
    Par xess91 dans le forum Contribuez / Téléchargez Sources et Outils
    Réponses: 0
    Dernier message: 25/05/2010, 16h34
  3. Calculer le jour du passage en heure d'été en java
    Par saladin79 dans le forum Général Java
    Réponses: 7
    Dernier message: 10/06/2009, 15h01
  4. Calculer les heures en jours et les jours en heures dans 2 combobox
    Par philoflore dans le forum Macros et VBA Excel
    Réponses: 2
    Dernier message: 18/07/2008, 19h09
  5. calcul sur date et heure indexées
    Par jimbo13300 dans le forum VBA Access
    Réponses: 6
    Dernier message: 26/06/2007, 14h05

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