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 :

Compteur virtuel, additionner deux résultats de compteurs réels


Sujet :

PHP & Base de données

  1. #1
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    43
    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 : 43
    Points : 13
    Points
    13
    Par défaut Compteur virtuel, additionner deux résultats de compteurs réels
    Bonjour,
    Je cherche à obtenir les consommations d'un compteur d'eau "virtuel" en faisant l'addition (ou soustraction suivant le besoin) des consommations de deux compteurs qui ont leur relevés dans une table.

    J'ai une première table 'devicestatus' qui liste les devices compteurs d'eau réels avec les colonnes 'dev_id' et 'dev_name'.
    Une seconde table 'meter' qui fait les enregistrement régulier des relevés de compteurs avec une colonne 'meter_devlog_id' (qui correspond au dev_id'), meter_value (le relevé du compteur, c'est un index qui ne fait que "monter", comme un index de compteur électrique) et 'meter_logdate' (current timestamp de chaque relevé, environ toutes les 15min).

    J'ai réussi, avec une grande aide bien précieuse, à calculer la consommation par jour d'un compteur : CONSO J = VALUE MAX J - VALUE MAX J-1

    Je cherche à faire la même chose pour un compteur "virtuel' : addition de deux compteurs : CONSO J = (VALUE MAX COMPT 1 J - VALUE MAX COMPT1 J-1) + (VALUE MAX COMPT 2 J - VALUE MAX COMPT2 J-1)

    En clair :
    Ma table 'devicestatus' :
    dev_id dev_name
    7 Compteur 7
    8 Compteur 8

    La table 'meter' avec des relevés de compteurs sur 2 jours :
    meter_devlog_id meter_value meter_logdate
    7 11 2021-02-21 08:00:00
    8 16 2021-02-21 08:00:01
    7 12 2021-02-21 22:30:00
    8 19 2021-02-21 22:30:01
    7 15 2021-02-22 08:00:00
    8 29 2021-02-22 08:00:01
    7 20 2021-02-22 22:30:00
    8 30 2021-02-22 22:30:01

    Le résultat des consommation des compteurs 7 ou 8 :
    dev_id logdate conso
    7 21/02/2021 null
    8 21/02/2021 null
    7 22/02/2021 8 (20-12)
    8 22/02/2021 11 (30-19)

    Le résultat que je souhaite obtenir :
    dev_id logdate conso
    7 + 8 21/02/2021 null
    7 + 8 22/02/2021 19 ((20-12)+(30-19))

    Pour faire la consommation d'un compteur, voici ma démarche actuelle (un grand merci à celui qui se reconnaîtr :
    Création d'une vue pour isoler les maximum par jour :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE or REPLACE VIEW V_CONSO_DAY as
          (SELECT meter_devlog_id  as V_CONSO_meter_devlog_id
                 , max(meter_value)    as V_CONSO_meter_value
                 , date(meter_logdate) as V_CONSO_meter_logdate
            FROM `meter` 
            GROUP BY meter_devlog_id 
                   , date(meter_logdate)
            ORDER BY meter_devlog_id
                   , date(meter_logdate)
           )
    ;

    Requête pour obtenir la consommation des 7 derniers jours du compteur 7 :
    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
    19
    20
    21
    22
    23
    24
    25
        SELECT 
            devicestatus.dev_id,
            devicestatus.dev_name as 'dev_name',
            UNIX_TIMESTAMP(CONVERT_TZ(D0.V_CONSO_meter_logdate, '+00:00', @@session.time_zone)) * 1000 as meter_unix_logdate,
            D0.V_CONSO_meter_value - D1.Index_PD as conso_day
        FROM devicestatus
        LEFT JOIN V_CONSO_DAY D0 
            ON D0.V_CONSO_meter_devlog_id = devicestatus.dev_id
        LEFT JOIN
            (
                SELECT 
                    V_CONSO_meter_devlog_id,
                    V_CONSO_meter_logdate,
                    V_CONSO_meter_value as Index_PD
                FROM V_CONSO_DAY
            ) D1
            ON D1.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
            AND D1.V_CONSO_meter_logdate = (
                SELECT max(D2.V_CONSO_meter_logdate)
                FROM V_CONSO_DAY D2
                WHERE D2.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
                AND D2.V_CONSO_meter_logdate < D0.V_CONSO_meter_logdate
            )
        WHERE D0.V_CONSO_meter_devlog_id = 7
          AND D0.V_CONSO_meter_logdate > (NOW() - INTERVAL 7 DAY)

    J'ai tenté de modifier cette requête mais sans succès, je n'ai clairement pas les compétences.
    Si quelqu'un veut bien m'aider.

  2. #2
    Membre habitué
    Homme Profil pro
    Développeur Web
    Inscrit en
    avril 2014
    Messages
    188
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France

    Informations professionnelles :
    Activité : Développeur Web

    Informations forums :
    Inscription : avril 2014
    Messages : 188
    Points : 148
    Points
    148
    Par défaut
    Bonjour,

    Je n'ai pas pris en compte ce que tu as indiqué car le problème ne me semble pas aussi compliqué que ça et tu peux le faire en empilant des colonnes cumulées les une dans les autres :
    Le résultat est à l'avant dernière colonne valeur et normalement ça doit fonctionner

    Par contre j'ai écrit deux fois la même table dérivée dans R1 et dans R2 car je ne sais pas comment éviter de réécrire deux fois la même requête pour l'utiliser dans deux tables dérivées, si quelqu'un sait merci d'avance... cela économisera 1 grosse ligne.
    Je pense qu'avec ça tu peux avancer

    il faut remplacer test2 par ta table
    date par ton champ date
    b par ton champ index compteur
    Par contre ça somme tous les compteurs si tu en veux que 2 il faut ajouter la condition et plus si besoin...

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT R1.cumul_t1 as cumul_t0,
    R2.cumul_t1 as cumul_t1,
    -R1.cumul_t1+R2.cumul_t1 as valeur,
    SUBSTRING(R2.date,1,10) FROM 
    (SELECT SUM(max_c) as cumul_t1,SUBSTRING(RR.date,1,10),date FROM (SELECT max(b) as max_c,SUBSTRING(date,1,10),t,date FROM `test2`  group by t,SUBSTRING(date,1,10)) RR group by SUBSTRING(RR.date,1,10) ) R1,
    (SELECT SUM(max_c) as cumul_t1,SUBSTRING(RR.date,1,10),date FROM (SELECT max(b) as max_c,SUBSTRING(date,1,10),t,date FROM `test2`  group by t,SUBSTRING(date,1,10)) RR group by SUBSTRING(RR.date,1,10) ) R2 
    WHERE SUBSTRING(DATE_SUB(R2.date,INTERVAL 1  day),1,10)=SUBSTRING(R1.date,1,10)



    voici le modèle de la table pour exemple :
    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
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    CREATE TABLE `test2` (
      `numero` int(11) NOT NULL,
      `n` int(11) NOT NULL DEFAULT '0',
      `t` int(11) NOT NULL,
      `b` int(11) NOT NULL,
      `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     
    --
    -- Déchargement des données de la table `test2`
    --
     
    INSERT INTO `test2` (`numero`, `n`, `t`, `b`, `date`) VALUES
    (1, 0, 1, 10, '2021-01-01 10:00:00'),
    (2, 0, 1, 15, '2021-01-01 18:00:00'),
    (3, 0, 1, 50, '2021-01-02 18:00:00'),
    (4, 0, 1, 35, '2021-01-02 10:00:00'),
    (5, 0, 2, 1, '2021-01-01 10:00:00'),
    (6, 0, 2, 10, '2021-01-01 18:00:00'),
    (7, 0, 1, 40, '2021-01-02 11:00:00'),
    (8, 0, 1, 45, '2021-01-02 15:00:00'),
    (9, 0, 2, 60, '2021-01-02 18:00:00'),
    (10, 0, 2, 40, '2021-01-02 10:00:00'),
    (11, 0, 1, 70, '2021-01-03 11:00:00'),
    (12, 0, 1, 100, '2021-01-03 18:00:00'),
    (13, 0, 2, 80, '2021-01-03 10:00:00'),
    (14, 0, 2, 120, '2021-01-03 18:00:00'),
    (15, 0, 2, 130, '2021-01-04 10:00:00'),
    (16, 0, 2, 160, '2021-01-04 18:00:00'),
    (17, 0, 1, 120, '2021-01-04 11:00:00'),
    (18, 0, 1, 180, '2021-01-04 18:00:00');
     
    --
    -- Index pour les tables déchargées
    --
     
    --
    -- Index pour la table `test2`
    --
    ALTER TABLE `test2`
      ADD PRIMARY KEY (`numero`);
     
    --
    -- AUTO_INCREMENT pour les tables déchargées
    --
     
    --
    -- AUTO_INCREMENT pour la table `test2`
    --
    ALTER TABLE `test2`
      MODIFY `numero` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;
    COMMIT;


    Résultat :

    cumul_t0 cumul_t1 valeur SUBSTRING(R2.date,1,10)
    25 110 85 2021-01-02
    110 220 110 2021-01-03
    220 340 120 2021-01-04

    Pour faciliter la vérification voici la somme des index max pour chaque compteur t et chaque jour :

    max_c SUBSTRING(date,1,10) t date
    15 2021-01-01 1 2021-01-01 10:00:00
    50 2021-01-02 1 2021-01-02 18:00:00
    100 2021-01-03 1 2021-01-03 11:00:00
    180 2021-01-04 1 2021-01-04 11:00:00
    10 2021-01-01 2 2021-01-01 10:00:00
    60 2021-01-02 2 2021-01-02 18:00:00
    120 2021-01-03 2 2021-01-03 10:00:00
    160 2021-01-04 2 2021-01-04 10:00:00

  3. #3
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    43
    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 : 43
    Points : 13
    Points
    13
    Par défaut
    Bonjour et surtout merci d'avoir pris le temps et proposé une solution quasi parfaite.
    Elle est totalement fonctionnelle mais sauf erreur de ma part le calcul réalisé est somme des MAX du J + somme des MAX de J-1:

    CONSO J = (VALUE MAX COMPT_1 J + VALUE MAX COMPT_2 J) + (VALUE MAX COMPT_1 J-1 + VALUE MAX COMPT_2 J-1)

    et non

    CONSO J = (VALUE MAX COMPT 1 J - VALUE MAX COMPT1 J-1) + (VALUE MAX COMPT 2 J - VALUE MAX COMPT2 J-1)

    Le résultat est le même mais comment faire si au lieu d'une addition, il faut faire une soustraction ?

    Voici deux cas de figure :

    1) Un compteur 7 et un Compteur 8 alimentent à eux deux une même machine. Pour connaître la conso de la machine, je vais créer un compteur virtuel via une page web : Sélection du compteur 7 ; Sélection de l'opérande "+", Sélection du compteur 8.
    2) Un compteur 7 installé en alimentation générale alimente une machine équipé du compteur 8, et une seconde qui n'est pas équipée de compteur. Pour connaître la conso de cette seconde machine, je vais créer un compteur virtuel via une page web : Sélection du compteur 7 ; Sélection de l'opérande "-", Sélection du compteur 8
    Les compteurs virtuel et les sélections effectuées seront stockées dans une table pour exécuter la fameuse requête.

    J'ai testé avec la table suivante :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    CREATE TABLE `meter` (
      `meter_devlog_id` int(11) NOT NULL,
      `meter_value` bigint(20) NOT NULL,
      `meter_logdate` datetime NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    Voici des relevés de compteurs :
    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` (`meter_devlog_id`, `meter_value`, `meter_logdate`) VALUES
    (7, 10, '2021-02-15 10:00:00'),
    (8, 10, '2021-02-15 10:00:00'),
    (9, 17, '2021-02-15 10:10:00'),
    (7, 20, '2021-02-15 22:55:00'),
    (8, 12, '2021-02-15 22:55:00'),
    (9, 21, '2021-02-15 22:55:00'),
    (7, 25, '2021-02-16 13:00:00'),
    (8, 15, '2021-02-16 13:00:00'),
    (9, 26, '2021-02-16 13:00:00'),
    (7, 30, '2021-02-16 23:00:00'),
    (8, 18, '2021-02-16 23:00:00'),
    (9, 31, '2021-02-16 23:00:00'),
    (7, 40, '2021-02-17 11:00:00'),
    (8, 25, '2021-02-17 11:00:00'),
    (9, 37, '2021-02-17 11:00:00'),
    (7, 65, '2021-02-17 21:00:00'),
    (8, 30, '2021-02-17 21:00:00'),
    (9, 46, '2021-02-17 21:00:00');

    Avec la requête réutilisée à l'identique en adaptant les noms de colonnes, et en ajoutant la sélection des compteurs 7 et 8 :
    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
    41
    42
    SELECT
        R1.cumul_t1 as cumul_t0,
        R2.cumul_t1 as cumul_t1,
        -R1.cumul_t1+R2.cumul_t1 as resultat,
        SUBSTRING(R2.meter_logdate,1,10) as date_de_conso
    FROM
        (SELECT
            SUM(max_value) as cumul_t1,
            SUBSTRING(RR.meter_logdate,1,10),
            meter_logdate
            FROM
                (SELECT
                    max(meter_value) as max_value,
                    SUBSTRING(meter_logdate,1,10),
                    meter_devlog_id,
                    meter_logdate
                    FROM `meter`
                    where meter_devlog_id = 7 OR meter_devlog_id = 8
                    group by
                        meter_devlog_id,
                        SUBSTRING(meter_logdate,1,10)
                    ) RR
            group by SUBSTRING(RR.meter_logdate,1,10)
            ) R1,
        (SELECT
            SUM(max_value) as cumul_t1,
            SUBSTRING(RR.meter_logdate,1,10),
            meter_logdate
            FROM 
                (SELECT
                    max(meter_value) as max_value,
                    SUBSTRING(meter_logdate,1,10),
                    meter_devlog_id,meter_logdate
                    FROM `meter`
                    where meter_devlog_id = 7 OR meter_devlog_id = 8
                    group by
                        meter_devlog_id,
                        SUBSTRING(meter_logdate,1,10)
                    ) RR
            group by SUBSTRING(RR.meter_logdate,1,10)
            ) R2 
    WHERE SUBSTRING(DATE_SUB(R2.meter_logdate,INTERVAL 1  day),1,10)=SUBSTRING(R1.meter_logdate,1,10)
    j'obtiens bien le bon résultat en addition :
    cumul_t0 cumul_t1 resultat date_de_conso
    32 48 16 2021-02-16
    48 95 47 2021-02-17

    Mais comment faire dans le cas de la soustraction ?
    C'est pourquoi je me demande si ce n'est pas mieux de commencer par calculer chaque conso de compteur pour ENSUITE soit faire une addition, soit faire une soustraction, cecpermet d'avoir une requête à 99% identique.

    Idéalement, il faudrait plutôt :
    conso_c1 conso_c2 resultat date_de_conso
    10 6 16 2021-02-16
    35 12 47 2021-02-17

    et le résultat en soustraction serait :
    conso_c1 conso_c2 resultat date_de_conso
    10 6 4 2021-02-16
    35 12 23 2021-02-17

    L'avantage de votre solution c'est que c'est totalement adaptable à plusieurs compteurs.
    J'aurais les cas 7 + 8 , 7 - 8 et éventuellement des cas à 3 compteurs (mais je pense que ca sera pour bien plus tard)

  4. #4
    Membre habitué
    Homme Profil pro
    Développeur Web
    Inscrit en
    avril 2014
    Messages
    188
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France

    Informations professionnelles :
    Activité : Développeur Web

    Informations forums :
    Inscription : avril 2014
    Messages : 188
    Points : 148
    Points
    148
    Par défaut
    Non relis tu verras y a pas d'erreur et cela correspond à ce que tu cherches.

  5. #5
    Membre habitué
    Homme Profil pro
    Développeur Web
    Inscrit en
    avril 2014
    Messages
    188
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France

    Informations professionnelles :
    Activité : Développeur Web

    Informations forums :
    Inscription : avril 2014
    Messages : 188
    Points : 148
    Points
    148
    Par défaut
    Je cherche par contre activement une solution pour éviter de réécrire deux fois la même sous requête dans une requête principale. L'utilisation des tables dérivées ne permet pas apparemment de réutiliser la table temporaire comme une simple variable "@var". A prioiri je pense que Mysql est capable de détecter les sous requêtes en doublon et gagne du temps à ce niveau là , mais au moins cela permettrait de gagner de nombreuses lignes et serait vraiment plus simple à lire.

    Petite remarque, cette requête bien que plus performante qu'un algorithme php à mon avis, peut malgré tout prendre de nombreuses secondes lorsque tu as une table avec des centaines de milliers de lignes, tu as peut être intérêt si ce type de requête doit être répétée très souvent à stocker tes données et index différemment évitant par exemple de calculer toujours les mêmes sommes, cela simplifiera à la fois la requête principale mais aussi permettra de gagner énormément de temps pour afficher les résultats. Par contre si ce calcul n'est effectué qu'une fois de temps de temps, tu peux te permettre de faire attendre l'utilisateur, cela permettant de gagner énormément de place sur tes tables en contre partie. Je connais bien la métrologie étant ingénieur et il m'arrive de me poser exactement le même type de problème.

  6. #6
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    43
    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 : 43
    Points : 13
    Points
    13
    Par défaut
    Citation Envoyé par xounet Voir le message
    Non relis tu verras y a pas d'erreur et cela correspond à ce que tu cherches.
    Bonjour, je dois passer à côté. J'ai beau tourner dans tous les sens, je n'arrive pas à faire la soustraction. A chaque fois le résultat n'est pas le bon.
    Je n'ai pas précisé mais mon niveau de compétence est proche de 0

    J'ai tenté de modifier la ligne sous plusieurs forme mais avec une soustraction, ca ne veut pas.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
        -R1.cumul_t1+R2.cumul_t1 as resultat,
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    Petite remarque, cette requête bien que plus performante qu'un algorithme php à mon avis, peut malgré tout prendre de nombreuses secondes lorsque tu as une table avec des centaines de milliers de lignes, tu as peut être intérêt si ce type de requête doit être répétée très souvent à stocker tes données et index différemment évitant par exemple de calculer toujours les mêmes sommes, cela simplifiera à la fois la requête principale mais aussi permettra de gagner énormément de temps pour afficher les résultats. Par contre si ce calcul n'est effectué qu'une fois de temps de temps, tu peux te permettre de faire attendre l'utilisateur, cela permettant de gagner énormément de place sur tes tables en contre partie. Je connais bien la métrologie étant ingénieur et il m'arrive de me poser exactement le même type de problème.
    A raison d'un relevé toute les 15 min par compteur et environ 10 à 20 compteurs selon les cas, les requêtes serviront à afficher un tableau de bilan de consommation : Aujourd'hui / hier / Semaine / Mois / Année. Consulté 2 à 3 fois par jour au maximum.
    Je n'ai aucune expérience et découvre tout juste le code (je suis dessinateur industriel) donc je ne sais pas si il faut stocker les résultats pour aller plus vite ou rester à la requête, en effet plus simple à gérer au niveau DB je pense.

  7. #7
    Membre habitué
    Homme Profil pro
    Développeur Web
    Inscrit en
    avril 2014
    Messages
    188
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : France

    Informations professionnelles :
    Activité : Développeur Web

    Informations forums :
    Inscription : avril 2014
    Messages : 188
    Points : 148
    Points
    148
    Par défaut
    Ok j'avais pas vu que tu as deux cas de figure je comprends ta remarque.
    Pour cela je te laisse chercher un peu c'est pas compliqué du tout, je pense que t'as gagné déjà pas mal de temps avec la requête que j'ai transmise qui fonctionne bien... attention tout de même à bien comprendre le calcul en fonction des dates pour avoir le cumul souhaité !
    Si tu as trouvé une réponse pour ma question précédente je serais très intéressé...

  8. #8
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    43
    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 : 43
    Points : 13
    Points
    13
    Par défaut
    Pas de soucis, je vais chercher même si ce n'est pas gagné ^^
    Je pense qu'il faut :
    calculer les consos (MAX J - MAX J-1) du device = 7 et les mettre dans un premier résultat 'conso_J_dev1'
    Calculer les consos (MAX J - MAX J-1) du device = 8 et les mettre dans un second résultat 'conso_J_dev2'
    Au SELECT de tête, ajouter
    Conso_J_dev1 - Conso_J_dev2 as Conso_J_sub
    Conso_J_dev1 + Conso_J_dev2 as Conso_J_add
    ainsi j'aurai les deux résultats mais alourdirai la requête (ou alors je remplacerai le - ou le + par une variable qui correspondra au choix fait à la création du virtuel (mais qui nécessitera aussi une requête pour le connaître)

    C'est clair dans ma tête mais pas avec le code

    Pour la question, oui si j'ai une piste je fait signe, mais ne pas s'attendre à des miracles je suis un pure noob.

  9. #9
    Membre à l'essai
    Homme Profil pro
    Dessinateur industriel
    Inscrit en
    février 2021
    Messages
    43
    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 : 43
    Points : 13
    Points
    13
    Par défaut
    Après des heures et des heures, j'ai trouvé un peu au hasard mais uniquement en passant par une vue. J'aimerai bien qu'on me corrige pour faire plus simple. Je pensais mettre le filtre de sélection des compteurs en fin de requête avec un WHERE ...=7 AND ...=8 mais je n'ai pas réussi.

    Création de la table :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    CREATE TABLE `meter` (
      `meter_devlog_id` int(11) NOT NULL,
      `meter_value` bigint(20) NOT NULL,
      `meter_logdate` datetime NOT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

    Jeu de données :
    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
    19
    INSERT INTO `meter` (`meter_devlog_id`, `meter_value`, `meter_logdate`) VALUES
    (7, 10, '2021-02-15 10:00:00'),
    (8, 10, '2021-02-15 10:00:00'),
    (9, 17, '2021-02-15 10:10:00'),
    (7, 20, '2021-02-15 22:55:00'),
    (8, 12, '2021-02-15 22:55:00'),
    (9, 21, '2021-02-15 22:55:00'),
    (7, 25, '2021-02-16 13:00:00'),
    (8, 15, '2021-02-16 13:00:00'),
    (9, 26, '2021-02-16 13:00:00'),
    (7, 30, '2021-02-16 23:00:00'),
    (8, 18, '2021-02-16 23:00:00'),
    (9, 31, '2021-02-16 23:00:00'),
    (7, 40, '2021-02-17 11:00:00'),
    (8, 25, '2021-02-17 11:00:00'),
    (9, 37, '2021-02-17 11:00:00'),
    (7, 65, '2021-02-17 21:00:00'),
    (8, 30, '2021-02-17 21:00:00'),
    (9, 46, '2021-02-17 21:00:00');

    Création de la Vue :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE or REPLACE VIEW V_CONSO_DAY as
          (SELECT meter_devlog_id  as V_CONSO_meter_devlog_id
                 , max(meter_value)    as V_CONSO_meter_value
                 , date(meter_logdate) as V_CONSO_meter_logdate
            FROM `meter` 
            GROUP BY meter_devlog_id 
                   , date(meter_logdate)
            ORDER BY meter_devlog_id
                   , date(meter_logdate)
           )
    ;

    Requête :
    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
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    SELECT 
        D10.V_CONSO_meter_devlog_id as 'id_dev1',
        D20.V_CONSO_meter_devlog_id as 'id_dev2',
        D10.V_CONSO_meter_logdate as 'datelog_dev1',
        D20.V_CONSO_meter_logdate as 'datelog_dev1',
        D10.V_CONSO_meter_value - D11.Index_PD1 as conso_day_dev1,
        D20.V_CONSO_meter_value - D21.Index_PD2 as conso_day_dev2,
        (D10.V_CONSO_meter_value - D11.Index_PD1) + (D20.V_CONSO_meter_value - D21.Index_PD2) as result_add,
        (D10.V_CONSO_meter_value - D11.Index_PD1) - (D20.V_CONSO_meter_value - D21.Index_PD2) as result_sub
    FROM devicestatus
    LEFT JOIN V_CONSO_DAY D10 
        ON D10.V_CONSO_meter_devlog_id = 7
    LEFT JOIN
        	(SELECT 
                V_CONSO_meter_devlog_id,
                V_CONSO_meter_logdate,
                V_CONSO_meter_value as Index_PD1
            FROM V_CONSO_DAY
        	) D11
        ON D11.V_CONSO_meter_devlog_id = D10.V_CONSO_meter_devlog_id
        AND D11.V_CONSO_meter_logdate =
        	(SELECT max(D12.V_CONSO_meter_logdate)
            FROM V_CONSO_DAY D12
            WHERE D12.V_CONSO_meter_devlog_id = D10.V_CONSO_meter_devlog_id
            AND D12.V_CONSO_meter_logdate < D10.V_CONSO_meter_logdate
        	)
    LEFT JOIN V_CONSO_DAY D20 
        ON D20.V_CONSO_meter_devlog_id = 8
    LEFT JOIN
        	(SELECT 
                V_CONSO_meter_devlog_id,
                V_CONSO_meter_logdate,
                V_CONSO_meter_value as Index_PD2
            FROM V_CONSO_DAY
        	) D21
        ON D21.V_CONSO_meter_devlog_id = D20.V_CONSO_meter_devlog_id
        AND D21.V_CONSO_meter_logdate =
        	(SELECT max(D22.V_CONSO_meter_logdate)
            FROM V_CONSO_DAY D22
            WHERE D22.V_CONSO_meter_devlog_id = D20.V_CONSO_meter_devlog_id
            AND D22.V_CONSO_meter_logdate < D20.V_CONSO_meter_logdate
        	)
    WHERE D10.V_CONSO_meter_logdate = D20.V_CONSO_meter_logdate
    GROUP BY D10.V_CONSO_meter_logdate

    Résultat :
    id_dev1 id_dev2 datelog_dev1 datelog_dev1 conso_day_dev1 conso_day_dev2 result_add result_sub
    7 8 15/02/2021 15/02/2021 NULL NULL NULL NULL
    7 8 16/02/2021 16/02/2021 10 6 16 4
    7 8 17/02/2021 17/02/2021 35 12 47 23

    S'il vous plaît, avez-vous une idée pour faire plus simple ?

Discussions similaires

  1. [XL-2003] Additionner deux dates pour résultat en mois et jours
    Par iIncoming dans le forum Excel
    Réponses: 4
    Dernier message: 21/01/2013, 15h04
  2. Additionner deux champs dans une requête
    Par eddyG dans le forum Access
    Réponses: 5
    Dernier message: 30/05/2006, 19h00
  3. EXCEL : Additionner deux groupes de lignes
    Par repié dans le forum Macros et VBA Excel
    Réponses: 6
    Dernier message: 22/12/2005, 21h59
  4. Additionner deux variables....
    Par Guillaume602 dans le forum Débuter
    Réponses: 5
    Dernier message: 16/11/2005, 16h00
  5. Comment avoir une fonction à deux résultats ?
    Par xenos dans le forum Langage
    Réponses: 9
    Dernier message: 02/10/2005, 11h32

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