Calcul consommation Jour et Heure depuis un index

**makimax** · 07/02/2021, 11h40

Bonjour et merci. J'étais déjà mal parti en créant une vue avec tout. Cela devrait être plus clair en effet

Envoyé par Paraffine

... Les bases relationnelles sont faites pour ce boulot !

? Je vais me documenter si jamais il y a une autre méthode.
Que j'arrive déjà a créer les vues et la super Requête !

**makimax** · 07/02/2021, 22h47

Bonjour,
J'ai fait 4 vues (total par Heure / Jour / Mois / Année) et 4 requêtes individuelles, la structure est totalement identique.
Est-ce qu'il ne faudrait pas au final ne faire qu'une seule vue si c'est possible ?

A ce stade, j'ai n'ai pas fait de jointure entre ces tables, ni avec la table "devicestatus" qui contient les ID (dev_id clé commune), dev_name, dev_battery_level, dev_last_update. J'ai donc 4 requêtes ce qui ne me permet pas de remplir mon bilan sur la page d'accueil.

S'il vous plaît, pouvez vous m'aider et me dire comment joindre l'ensemble afin de pouvoir enrichir $devices avec le foreach ?

Voici les vues :
pour la consommation par Heure :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE VIEW MS_CONSO_H as
      (SELECT meter_device_row_id  as MS_DevLogID
             , max(meter_value)  as MS_Value_H
             , DATE_SUB(DATE_SUB(meter_logdate, INTERVAL MINUTE(meter_logdate) MINUTE), INTERVAL SECOND(meter_logdate) SECOND) as MS_Date_H
        FROM `meter` 
        GROUP BY MS_DevLogID
               , MS_Date_H
        ORDER BY MS_DevLogID 
               , MS_Date_H
       )
;

pour la consommation par Jour :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE VIEW MS_CONSO_J as
      (SELECT meter_device_row_id  as MS_DevLogID
             , max(meter_value)  as MS_Value_J
             , date(meter_logdate)  as MS_Date_J
        FROM `meter` 
        GROUP BY MS_DevLogID 
               , MS_Date_J
        ORDER BY MS_DevLogID 
               , MS_Date_J
       )
;

pour la consommation par Mois :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE VIEW MS_CONSO_M as
      (SELECT meter_device_row_id  as MS_DevLogID
             , max(meter_value)  as MS_Value_M
             , DATE_FORMAT(meter_logdate, "%Y-%m")  as MS_Date_M
        FROM `meter` 
        GROUP BY MS_DevLogID
               , MS_Date_M
        ORDER BY MS_DevLogID
               , MS_Date_M
       )
;

pour la consommation par Année :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE VIEW MS_CONSO_A as
      (SELECT meter_device_row_id  as MS_DevLogID
             , max(meter_value)  as MS_Value_A
             , year(meter_logdate)  as MS_Date_A
        FROM `meter` 
        GROUP BY MS_DevLogID
               , MS_Date_A
        ORDER BY MS_DevLogID
               , MS_Date_A
       )
;

Voici les requêtes
pour la consommation par Heure:

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_DevLogID,
    M0.MS_Date_H,
    M0.MS_Value_H as 'Index_HH',
    M1.Index_HP as 'Index_HP',
    M0.MS_Value_H - M1.Index_HP as 'Conso_H'
FROM MS_CONSO_H M0
LEFT JOIN
    (SELECT 
        MS_DevLogID,
        MS_Date_H,
        MS_Value_H as Index_HP
    FROM MS_CONSO_H
    ) M1
    ON M1.MS_DevLogID = M0.MS_DevLogID
    AND M1.MS_Date_H = ( SELECT max(M2.MS_Date_H)
                        FROM MS_CONSO_H M2
                        WHERE M2.MS_DevLogID = M0.MS_DevLogID
                        AND M2.MS_Date_H < M0.MS_Date_H
    )

Voici les requêtes
pour la consommation par Jour:

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_DevLogID,
    M0.MS_Date_J,
    M0.MS_Value_J as 'Index_JJ',
    M1.Index_JP as 'Index_JP',
    M0.MS_Value_J - M1.Index_JP as 'Conso_J'
FROM MS_CONSO_J M0
LEFT JOIN
    (SELECT 
        MS_DevLogID,
        MS_Date_J,
        MS_Value_J as Index_JP
    FROM MS_CONSO_J
    ) M1
    ON M1.MS_DevLogID = M0.MS_DevLogID
    AND M1.MS_Date_J = ( SELECT max(M2.MS_Date_J)
                        FROM MS_CONSO_J M2
                        WHERE M2.MS_DevLogID = M0.MS_DevLogID
                        AND M2.MS_Date_J < M0.MS_Date_J
    )

pour la consommation par Mois:

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_DevLogID,
    M0.MS_Date_M,
    M0.MS_Value_M as 'Index_MM',
    M1.Index_MP as 'Index_MP',
    M0.MS_Value_M - M1.Index_MP as 'Conso_M'
FROM MS_CONSO_M M0
LEFT JOIN
    (SELECT 
        MS_DevLogID,
        MS_Date_M,
        MS_Value_M as Index_MP
    FROM MS_CONSO_M
    ) M1
    ON M1.MS_DevLogID = M0.MS_DevLogID
    AND M1.MS_Date_M = ( SELECT max(M2.MS_Date_M)
                        FROM MS_CONSO_M M2
                        WHERE M2.MS_DevLogID = M0.MS_DevLogID
                        AND M2.MS_Date_M < M0.MS_Date_M
    )

pour la consommation par Anné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
19
20
SELECT 
    M0.MS_DevLogID,
    M0.MS_Date_A,
    M0.MS_Value_A as 'Index_AA',
    M1.Index_AP as 'Index_AP',
    M0.MS_Value_A - M1.Index_AP as 'Conso_A'
FROM MS_CONSO_A M0
LEFT JOIN
    (SELECT 
        MS_DevLogID,
        MS_Date_A,
        MS_Value_A as Index_AP
    FROM MS_CONSO_A
    ) M1
    ON M1.MS_DevLogID = M0.MS_DevLogID
    AND M1.MS_Date_A = ( SELECT max(M2.MS_Date_A)
                        FROM MS_CONSO_A M2
                        WHERE M2.MS_DevLogID = M0.MS_DevLogID
                        AND M2.MS_Date_A < M0.MS_Date_A
    )

Merci pour vos lumières.

**escartefigue** · 08/02/2021, 07h04

bonjour makimax

Quel est le résultat attendu ? Merci de fournir un exemple

**Paraffine** · 08/02/2021, 09h13

Le but est d'alimenter le tableau du post #19 :

Envoyé par makimax

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

**makimax** · 08/02/2021, 12h35

Bonjour escartefigue et paraffine

Tout à fait, mon but est d'alimenter un tableau "bilan" :
Nom : Tableau bilan.PNG
Affichages : 91
Taille : 21,8 Ko

Individuellement, ces 4 requêtes me permettent de générer les graphique Highcharts. Mais pour le bilan, il me faut bien les informations dans une seule requête afin de les lister dans le tableau avec le "foreach".
J'ai tenté de faire une seule vue et une seule requête avec des JOIN LEFT mais je pense que j'ai beaucoup trop compliqué le truc :

Vue :

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
CREATE OR REPLACE VIEW MS_BILAN as
   (SELECT 
           dev_id
			 , meter_device_row_id  as MS_DevLogID
         , dev_name
         , dev_battery_level
         , dev_last_update
         , meter_logdate
     	 , max(meter_value)  as MS_Value_A
     	 , year(meter_logdate)  as MS_Date_A
         , max(meter_value)  as MS_Value_M
         , DATE_FORMAT(meter_logdate, '%Y-%m')  as MS_Date_M
     	 , max(meter_value)  as MS_Value_J
    	 , date(meter_logdate)  as MS_Date_J
     	 , max(meter_value)  as MS_Value_H
    	 , DATE_SUB(DATE_SUB(meter_logdate, INTERVAL MINUTE(meter_logdate) MINUTE), INTERVAL SECOND(meter_logdate) SECOND) as MS_Date_H
    FROM `devicestatus`
	LEFT JOIN
	    (SELECT *
	    FROM `meter` 
	    )
	    `meter` 
		ON meter.meter_device_row_id = devicestatus.dev_id
    GROUP BY dev_id
           , meter_logdate
    ORDER BY MS_DevLogID
           , meter_logdate
    )
;

Requête (qui ne fonctionne pas) :

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
SELECT 
    M0.MS_DevLogID,
    M0.MS_Date_A,
    M0.MS_Value_A as 'Index_AA',
    M1.Index_AP as 'Index_AP',
    M0.MS_Value_A - M1.Index_AP as 'Conso_A',
    M20.MS_DevLogID,
    M20.MS_Date_M,
    M20.MS_Value_M as 'Index_MM',
    M21.Index_MP as 'Index_MP',
    M20.MS_Value_M - M1.Index_MP as 'Conso_M',
    M30.MS_DevLogID,
    M30.MS_Date_J,
    M30.MS_Value_J as 'Index_JJ',
    M31.Index_JP as 'Index_JP',
    M30.MS_Value_J - M1.Index_JP as 'Conso_J',
    M40.MS_DevLogID,
    M40.MS_Date_H,
    M40.MS_Value_H as 'Index_HH',
    M41.Index_HP as 'Index_HP',
    M40.MS_Value_H - M1.Index_HP as 'Conso_H'
FROM MS_BILAN M0
LEFT JOIN
    (SELECT 
        MS_DevLogID,
        MS_Date_A,
        MS_Value_A as Index_AP
    FROM MS_BILAN
    ) M1
    ON M1.MS_DevLogID = M0.MS_DevLogID
    AND M1.MS_Date_A = ( SELECT max(M2.MS_Date_A)
                        FROM MS_BILAN M2
                        WHERE M2.MS_DevLogID = M0.MS_DevLogID
                        AND M2.MS_Date_A < M0.MS_Date_A
    					)
LEFT JOIN
    (SELECT 
        M20.MS_DevLogID,
        M20.MS_Date_M,
        M20.MS_Value_M as Index_MP
    FROM MS_BILAN
    ) M21
    ON M21.MS_DevLogID = M20.MS_DevLogID
    AND M21.MS_Date_M = ( SELECT max(M22.MS_Date_M)
                        FROM MS_BILAN M22
                        WHERE M22.MS_DevLogID = M20.MS_DevLogID
                        AND M22.MS_Date_M < M20.MS_Date_M
    					)
LEFT JOIN
    (SELECT 
        M30.MS_DevLogID,
        M30.MS_Date_J,
        M30.MS_Value_J as Index_JP
    FROM MS_BILAN
    ) M31
    ON M31.MS_DevLogID = M30.MS_DevLogID
    AND M31.MS_Date_J = ( SELECT max(M32.MS_Date_J)
                        FROM MS_BILAN M32
                        WHERE M32.MS_DevLogID = M30.MS_DevLogID
                        AND M32.MS_Date_J < M30.MS_Date_J
    					)
LEFT JOIN
    (SELECT 
        M40.MS_DevLogID,
        M40.MS_Date_H,
        M40.MS_Value_H as Index_HP
    FROM MS_BILAN
    ) M41
    ON M41.MS_DevLogID = M40.MS_DevLogID
    AND M41.MS_Date_H = ( SELECT max(M42.MS_Date_H)
                        FROM MS_BILAN M42
                        WHERE M42.MS_DevLogID = M40.MS_DevLogID
                        AND M42.MS_Date_H < M40.MS_Date_H
    					)

**escartefigue** · 08/02/2021, 13h33

OK,

Je m'y replonge quand je trouve un peu de temps, ce ne sont pas les occupations qui manquent en ce moment

à +

**makimax** · 08/02/2021, 13h38

Merci beaucoup. J'ai passé le week-end dessus sans succès. Enfin si j'ai réussi les 4 vues et requêtes individuelles pour les graphiques. Grâce à votre aide.
Reste ce tableau.
Merci

**escartefigue** · 08/02/2021, 15h24

Le sujet est intéressant, du coup je m'y suis penché rapidement.

Tout d'abord, j'ai enrichi (très peu faute de temps) le jeu d'essai pour avoir quelques éléments sur février :

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
CREATE TABLE `meter` 
      ( meter_device_row_id integer
      , meter_value         integer
      , meter_logdate       datetime
      ) engine=INNODB
;
INSERT INTO `meter` (meter_device_row_id, meter_value, meter_logdate) 
VALUES (7, 971198, '2021-01-25 22:45:15'),
       (8, 477042, '2021-01-25 22:45:21'),
       (9, 569808, '2021-01-25 22:45:32'),
       (7, 972179, '2021-01-25 23:50:00'),
       (8, 477524, '2021-01-25 23:50:00'),
       (9, 570384, '2021-01-25 23:50:00'),
       (7, 972179, '2021-01-26 01:15:10'),
       (8, 477524, '2021-01-26 01:15:23'),
       (9, 571384, '2021-01-26 01:15:32'),
       (7, 973324, '2021-01-26 22:45:15'),
       (8, 477948, '2021-01-26 22:45:21'),
       (9, 572808, '2021-01-26 22:45:32'),
       (7, 973754, '2021-01-28 03:50:30'),
       (8, 478848, '2021-01-28 03:51:23'),
       (9, 573384, '2021-01-28 03:52:11'),
       (7, 974100, '2021-01-28 21:12:10'),
       (8, 478848, '2021-01-28 21:12:23'),
       (9, 573984, '2021-01-28 21:12:32'),
       (7, 144200, '2021-02-01 21:12:10'),
       (8, 136030, '2021-02-01 21:12:23'),
       (9, 112124, '2021-02-01 21:12:32'),
       (7, 151016, '2021-02-03 21:12:10'),
       (8, 178922, '2021-02-03 21:12:23'),
       (9, 114303, '2021-02-03 21:12:32')      
;

Ensuite, j'ai simplifié le problème en ne traitant que les valeurs de J et J-1 comme déjà résolu et ajouté la valeur de M-1.
Le même principe reste à appliquer pour les valeurs de A-1
Pour ce besoin, j'utilise toujours ma vue pour J (que j'ai renommée J0) et une vue pour M-1.
Notez qu'il n'est pas nécessaire d'avoir des noms de colonnes différents dans ces deux vues, puisque les alias différents lèvent tout équivoque :

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
create view MS_CONSO_J as
      (select meter_device_row_id  as MS_Ident
             , max(meter_value)    as MS_Value
             , date(meter_logdate) as MS_Date
        from `meter` 
        group by meter_device_row_id 
               , date(meter_logdate)
        order by meter_device_row_id
               , date(meter_logdate)
       )
;
CREATE view MS_CONSO_M as
      (SELECT meter_device_row_id  as MS_Ident
             , max(meter_value)    as MS_Value
             , DATE_FORMAT(meter_logdate, "%Y-%m")  as MS_Date
        FROM `meter` 
        GROUP BY MS_Ident
               , MS_Date
        ORDER BY MS_Ident
               , MS_Date
       )
;

A partir de là, j'ai ajouté une jointure vers la vue M-1 à partir de ma requête précédente

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
select J0.MS_Ident   
     , J0.MS_Date    
     , J0.MS_Value     as "Conso J" 
     , J1.Conso_P      as "Conso P"
     , case when J0.MS_Value - J1.Conso_P  > 0
            then J0.MS_Value - J1.Conso_P
            else 0
       end as "Ecart"
     , M0.MS_Date      as "M-1"
     , M0.MS_Value     as "Val M-1"
from   MS_CONSO_J J0
left join 
     ( select MS_Ident
            , MS_Date
            , MS_Value as Conso_P
       from MS_CONSO_J
     ) J1
   on J1.MS_Ident = J0.MS_Ident
  and J1.MS_Date =
     ( select max(J2.MS_Date)
       from   MS_CONSO_J J2
       where J2.MS_ident=J0.MS_Ident
         and J2.MS_Date <J0.MS_Date
     )
left join MS_CONSO_M M0
  on M0.MS_ident=J0.MS_ident
 and M0.MS_Date=DATE_FORMAT(DATE_ADD(J0.MS_Date, INTERVAL -1 MONTH), "%Y-%m")
;

Le résultat me semble conforme :

MS_Ident	MS_Date	Conso J	Conso P	Ecart	M-1	Val M-1
7	2021-01-25	972179		0
7	2021-01-26	973324	972179	1145
7	2021-01-28	974100	973324	776
7	2021-02-01	144200	974100	0	2021-01	974100
7	2021-02-03	151016	144200	6816	2021-01	974100
8	2021-01-25	477524		0
8	2021-01-26	477948	477524	424
8	2021-01-28	478848	477948	900
8	2021-02-01	136030	478848	0	2021-01	478848
8	2021-02-03	178922	136030	42892	2021-01	478848
9	2021-01-25	570384		0
9	2021-01-26	572808	570384	2424
9	2021-01-28	573984	572808	1176
9	2021-02-01	112124	573984	0	2021-01	573984
9	2021-02-03	114303	112124	2179	2021-01	57398

A enrichir avec A-1 après vérification par vos soins

**makimax** · 08/02/2021, 17h40

Merci pour la rapidité, je vais regarder cela ce soir en rentrant du travail.
Au premier coup d'oeil, attention la 'meter_value' est forcément croissante car c'est un index de compteur, même en changeant de mois il continu de s'incrémenter.
Je testerai avec des valeurs sur 2 mois pour voir si cela fonctionne entre M-1 et M-2.

Si jamais c'est bon, j'essaierai d'ajouter pour l'année, mais aussi joindre le nom et les quelques autres info de la table 'devicestatus'.
Je vous retiens informé après 20h30 bébé oblige

**escartefigue** · 08/02/2021, 18h53

Envoyé par makimax

Au premier coup d'oeil, attention la 'meter_value' est forcément croissante car c'est un index de compteur, même en changeant de mois il continu de s'incrémenter.

Oui c'est vrai j'avais oublié ce détail, mais il ne devrait changer que le jeu d'essai, pas la requête.
Il faudra aussi ajouter une restriction sur la date, pour prendre par exemple le relevé du jour, là je sélectionne tout.

Je n'ai pas voulu trop peaufiner avant de savoir si ça correspond au besoin (et j'avoue avoir un peu fait ça entre la poire et le fromage

)

**makimax** · 08/02/2021, 19h08

Je viens d'essayer, il faudrait les mêmes colonnes pour le Mois que pour le Jour à savoir Max du mois M (qui est finalement le même que Max du J) - Max du mois précédent : l'équivalent de ce que vous avez appellé Conso J et Conso P, qui sont en fait des Index compteur et non pas des consommation. Et qui donnerai Ecart M (qui est la consommation, la différence des deux).

En plus simple, j'ai l'impression qu'il manque juste l'écart en fait

Voici une table avec des valeurs plus simples, chaque compteur ne fait que croître

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
CREATE TABLE `meter` 
      ( meter_device_row_id integer
      , meter_value         integer
      , meter_logdate       datetime
      ) engine=INNODB
;
INSERT INTO `meter` (meter_device_row_id, meter_value, meter_logdate) 
VALUES (7, 7011, '2021-01-25 22:45:15'),
       (8, 8012, '2021-01-25 22:45:21'),
       (9, 9013, '2021-01-25 22:45:32'),
       (7, 7024, '2021-01-25 23:50:00'),
       (8, 8025, '2021-01-25 23:50:00'),
       (9, 9026, '2021-01-25 23:50:00'),
       (7, 7037, '2021-01-26 01:15:10'),
       (8, 8038, '2021-01-26 01:15:23'),
       (9, 9039, '2021-01-26 01:15:32'),
       (7, 7040, '2021-01-26 22:45:15'),
       (8, 8041, '2021-01-26 22:45:21'),
       (9, 9042, '2021-01-26 22:45:32'),
       (7, 7053, '2021-01-28 03:50:30'),
       (8, 8054, '2021-01-28 03:51:23'),
       (9, 9055, '2021-01-28 03:52:11'),
       (7, 7066, '2021-01-28 21:12:10'),
       (8, 8067, '2021-01-28 21:12:23'),
       (9, 9068, '2021-01-28 21:12:32'),
       (7, 7079, '2021-02-01 21:12:10'),
       (8, 8070, '2021-02-01 21:12:23'),
       (9, 9071, '2021-02-01 21:12:32'),
       (7, 7082, '2021-02-03 21:12:10'),
       (8, 8083, '2021-02-03 21:12:23'),
       (9, 9084, '2021-02-03 21:12:32')      
;

Qui devrait donner :
Nom : Bilan_Ecart_M.JPG
Affichages : 84
Taille : 70,0 Ko

PS : Comment faire pour créer un tableau dans le forum depuis un résultat ou un excel ?

**makimax** · 08/02/2021, 19h10

Envoyé par escartefigue

... (et j'avoue avoir un peu fait ça entre la poire et le fromage

)

Bon appétit !

Envoyé par escartefigue

Je n'ai pas voulu trop peaufiner avant de savoir si ça correspond au besoin

Cela semble vraiment convenir, je vais rentrer plus de date afin de voir si cela fonctionne sur les mois précédents, je ferai ainsi l'année ensuite.
Et essayer de joindre le dev_name, dev_battery_level de la table 'devicestatus'.

Merci beaucoup, je vous ferai des screens de l'avancé du projet si cela vous dit

**escartefigue** · 08/02/2021, 19h16

Pour la conversion en tableau, j'utilise un outil très pratique de Winjerome (merci encore à lui

, c'est vraiment beaucoup plus simple avec que sans)

C'est ici : https://winjerome.developpez.com/outils/bb-tableau/

Envoyé par makimax

Merci beaucoup, je vous ferai des screens de l'avancé du projet si cela vous dit

Bien volontiers, bon courage pour la suite

**makimax** · 09/02/2021, 01h28

J'ai créé les 3 vues (heure/jour/mois)

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
CREATE VIEW MS_CONSO_J as
      (SELECT meter_device_row_id  as MS_DevLogID
             , max(meter_value)    as MS_Value
             , date(meter_logdate) as MS_Date
        FROM `meter` 
        GROUP BY meter_device_row_id 
               , date(meter_logdate)
        ORDER BY meter_device_row_id
               , date(meter_logdate)
       )
;
CREATE VIEW MS_CONSO_M as
      (SELECT meter_device_row_id as MS_DevLogID
             , max(meter_value) as MS_Value
             , date_format(meter_logdate, '%Y-%m') as MS_Date
        FROM `meter`
        GROUP BY MS_DevLogID
               , MS_Date
        ORDER BY MS_DevLogID
               , MS_Date
       )
;
CREATE VIEW MS_CONSO_A as
      (SELECT meter_device_row_id as MS_DevLogID
             , max(meter_value) as MS_Value
             , year(meter_logdate) as MS_Date
        FROM `meter`
        GROUP BY MS_DevLogID
               , MS_Date
        ORDER BY MS_DevLogID
               , MS_Date
       )
;

et la requête avec jointure :

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
43
44
45
46
47
48
49
50
SELECT 
       devicestatus.dev_id
     , devicestatus.dev_name
     , devicestatus.dev_battery_level
     , devicestatus.dev_last_update
     , J0.MS_DevLogID   
     , J0.MS_Date    
     , J0.MS_Value as 'MS_Index_JJ' 
     , J1.Index_JP as 'MS_Index_JP'
     , CASE WHEN J0.MS_Value - J1.Index_JP  > 0
            THEN J0.MS_Value - J1.Index_JP
            ELSE 0
       END as 'MS_Consommation_J'
     , M0.MS_Date as 'MS_Date_M'
     , M0.MS_Value as 'MS_Index_MP'
     , J0.MS_Value - M0.MS_Value as 'Consommation_M'
     , A0.MS_Date as 'MS_Date_A'
     , A0.MS_Value as 'MS_Index_AP'
     , J0.MS_Value - A0.MS_Value as 'Consommation_A'
FROM MS_CONSO_J J0
LEFT JOIN 
     (SELECT MS_DevLogID
            , MS_Date
            , MS_Value as Index_JP
       FROM MS_CONSO_J
     ) J1
   ON J1.MS_DevLogID = J0.MS_DevLogID
  AND J1.MS_Date =
     ( SELECT max(J2.MS_Date)
       FROM   MS_CONSO_J J2
       WHERE J2.MS_DevLogID=J0.MS_DevLogID
         AND J2.MS_Date <J0.MS_Date
     )
LEFT JOIN MS_CONSO_M M0
    ON M0.MS_DevLogID=J0.MS_DevLogID
    AND M0.MS_Date=DATE_FORMAT(DATE_ADD(J0.MS_Date, INTERVAL -1 MONTH), '%Y-%m')
LEFT JOIN MS_CONSO_A A0
    ON A0.MS_DevLogID=J0.MS_DevLogID
    AND A0.MS_Date=DATE_FORMAT(DATE_ADD(J0.MS_Date, INTERVAL -1 YEAR), '%Y')
LEFT JOIN
    ( SELECT
            dev_id,
            dev_name,
            dev_battery_level,
            dev_last_update
        FROM
        `devicestatus` 
    )
    `devicestatus` 
    ON J0.MS_DevLogID = devicestatus.dev_id

Pour remplir le tableau de bilan de la page d'accueil, qui a une ligne par compteur (généré par le "foreach" dans le php), dans mon idée de départ, il faut que la requête liste les bonnes informations dans une seule et unique ligne PAR compteur.

Pour cela, je peut ajouter un filtre sur la date du jour à la fin de la requête :

Code :

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

WHERE date(J0.MS_Date) = DATE( NOW())

Cela fonctionne, mais gros problème si il n'y a pas de relevé de compteur, la ligne est vide, et même pire, elle n'apparaîtra pas.

Je me demande si la méthode est la bonne au final.
L'idéal serait que les lignes de ce tableau soit liée directement à la liste des compteurs, et qu'ensuite chaque valeurs de consommation jour / mois / année lui soit rapportée, et que s'il n'y en a pas pour le jour par exemple (ou éventuellement le mois entier), que la cellule soit vide, ou null.

S'il vous plaît, avez vous une idée ?

**Paraffine** · 09/02/2021, 08h01

Envoyé par makimax

Pour remplir le tableau de bilan de la page d'accueil, qui a une ligne par compteur (généré par le "foreach" dans le php), dans mon idée de départ, il faut que la requête liste les bonnes informations dans une seule et unique ligne PAR compteur.

Pour cela, je peut ajouter un filtre sur la date du jour à la fin de la requête :

Code :

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

WHERE date(J0.MS_Date) = DATE( NOW())

Cela fonctionne, mais gros problème s'il n'y a pas de relevé de compteur, la ligne est vide, et même pire, elle n'apparaîtra pas.

Cette condition restreint les résultats aux relevés du jour. Elle fait son boulot...

Pour demander le dernier relevé :

Code :

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

WHERE J0.MS_Date = (select MAX(MS_Date) FROM MS_CONSO_J JMAX WHERE JMAX.MS_DevLogID= J0.MS_DevLogID)

Par contre si l'on veut toujours afficher le compteur, y compris en l'absence de tout relevé, il faut prendre comme table "directrice"... celle des compteurs, et pas celle des relevés. Là, le pilier c'est la table MS_CONSO_J, donc hors de MS_CONSO_J, point de salut !

**makimax** · 09/02/2021, 09h52

Bonjour,
Votre proposition si je le comprends bien permet d'afficher la ligne la plus récente, cela permet d'avoir un tableau en théorie complet, mais dont les valeurs risquent de ne pas correspondre au titre (et au besoin) c'est à dire qu'il faudrait une colonne supplémentaire qui serait "DERNIER RELEVE", et que la colonne JOUR deviendrait plutôt CONSO JOUR DE CETTE DATE, idem pour MOIS et ANNEE.
Lors de la mise en service du système, il n'y aurait aucune ligne, jusqu'à avoir au moins un relevé.

Une autre solution :

Avoir une première requête pour générer les lignes tu tableau avec un foreach. ( celle-ci visant la table des compteurs : 'devicestatus') :

Code php :

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

1
2
3
4
$pdo = pdo_connect_mysql();
$stmt = $pdo->prepare("SELECT * FROM devicestatus WHERE dev_used = 1 ORDER BY dev_id");
$stmt->execute();
$devices = $stmt->fetchAll(PDO::FETCH_ASSOC);

Puis lors de la création de la table, dans le <tbody>, et pour chaque <td> mettre les requêtes de escartefigue en version 'individuelle' (req_conso_j ou req_conso_m ou req_conso_a) en ajoutant une condition qui nous assure de n'avoir qu'une seule ligne par compteur en réponse.
Je pourrais alors replacer un foreach et indiquer la valeur de consommation.

Code php :

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
53
54
55
56
57
58
59
60
<table>
    <thead>
        <tr>
            <th>Nom</th>
            <th>Index</th>
            <th>Ce jour</th>
            <th>Ce mois</th>
            <th>Cette année</th>
            <th>Batterie</th>
            <th>Voir graphique</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($devices as $device): ?>
        <tr>
            <td><a href="./devcharts.php?id=<?=$device['dev_id']?>"><?=$device['dev_name']?></a></td>
            <!-- Préparer la prochaine cellule : faire la requête et récupérer les conso J de chaque compteur -->
            <?php
                $stmt_conso_j = $pdo->prepare("
                    SELECT 
                        J0.MS_DevLogID,
                        J0.MS_Date,
                        J0.MS_Value as 'Index_JJ',
                        J1.Index_JP as 'Index_JP',
                        J0.MS_Value - J1.Index_JP as 'Consommation_J'
                    FROM MS_CONSO_J J0
                    LEFT JOIN
                        (SELECT 
                            MS_DevLogID,
                            MS_Date,
                            MS_Value as Index_JP
                        FROM MS_CONSO_J
                        ) J1
                        ON J1.MS_DevLogID = J0.MS_DevLogID
                        AND J1.MS_Date_J = ( SELECT max(J2.MS_Date_J)
                                            FROM MS_CONSO_J J2
                                            WHERE J2.MS_DevLogID = J0.MS_DevLogID
                                            AND J2.MS_Date_J < J0.MS_Date_J
                        )
                        WHERE J0.MS_DevLogID = '{$device['dev_id']}' AND J0.MS_Date = date(NOW())
                        ");
                $stmt_conso_j->execute();
                $rep_consos_jours = $stmt_conso_j->fetchAll(PDO::FETCH_ASSOC);
            ?>
            <!-- Pour chaque ligne de réponse, afficher la valeur -->
            <?php foreach ($rep_consos_jours as $rep_conso_j): ?>
            <td><?=$rep_conso_j['Consommation_J']?></td>
            <?php endforeach; ?>
            <!-- Répéter la même chose pour Mois et Année-->
            <td></td>
            <td></td>
            <td></td>
            <td><?=$device['dev_battery_level']?></td>
            <td><a href="./devcharts.php?id=<?=$device['dev_id']?>">Voir graphique</i>
                </a>
            </td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>

La question est : si il n'y a pas de réponse, comment faire pour quand même remplir la cellule ? (du genre un '-' ou 'aucune valeur') car sinon, tout le tableau sera décalé car il n'aura pas autant de réponse de consommation qu'il n'y a de ligne de compteur...
Je ne sais pas si je suis clair

EDIT :
La nuit porte conseil, je vais essayer de ne faire qu'un seul foreach, de placer les requêtes de consommations DANS le foreach, avant la création des lignes. Et pour éviter résultat sans réponses donc décalage du tableau, je vais tenter de forcer au préalable les variables à une valeur -1 par exemple. Si après requête j'ai -1, alors j'affecterai un "non applicable" à la variable. Et normalement ce sera bon ^^

**Paraffine** · 09/02/2021, 13h20

Si l'on veut un tableau "à trous" avec tous les compteurs, il faut partir de la table des compteurs.

Quelque chose dans le goût de :

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 
       devicestatus.dev_id
     , devicestatus.dev_name
     , devicestatus.dev_battery_level
     , devicestatus.dev_last_update
     , J0.MS_DevLogID   
     , J0.MS_Date    
     , J0.MS_Value as 'MS_Index_JJ' 
     , J1.Index_JP as 'MS_Index_JP'
     , CASE WHEN J0.MS_Value - J1.Index_JP  > 0
            THEN J0.MS_Value - J1.Index_JP
            ELSE 0
       END as 'MS_Consommation_J'
     , M0.MS_Date as 'MS_Date_M'
     , M0.MS_Value as 'MS_Index_MP'
     , J0.MS_Value - M0.MS_Value as 'Consommation_M'
     , A0.MS_Date as 'MS_Date_A'
     , A0.MS_Value as 'MS_Index_AP'
     , J0.MS_Value - A0.MS_Value as 'Consommation_A'
FROM `devicestatus`
LEFT JOIN MS_CONSO_J J0 
     ON J0.MS_DevLogID = devicestatus.dev_id
LEFT JOIN 
     (SELECT MS_DevLogID
            , MS_Date
            , MS_Value as Index_JP
       FROM MS_CONSO_J
     ) J1
   ON J1.MS_DevLogID = J0.MS_DevLogID
  AND J1.MS_Date =
     ( SELECT max(J2.MS_Date)
       FROM   MS_CONSO_J J2
       WHERE J2.MS_DevLogID=J0.MS_DevLogID
         AND J2.MS_Date <J0.MS_Date
     )
LEFT JOIN MS_CONSO_M M0
    ON M0.MS_DevLogID=J0.MS_DevLogID
    AND M0.MS_Date=DATE_FORMAT(DATE_ADD(J0.MS_Date, INTERVAL -1 MONTH), '%Y-%m')
LEFT JOIN MS_CONSO_A A0
    ON A0.MS_DevLogID=J0.MS_DevLogID
    AND A0.MS_Date=DATE_FORMAT(DATE_ADD(J0.MS_Date, INTERVAL -1 YEAR), '%Y')
WHERE date(J0.MS_Date) = DATE( NOW()) OR date(J0.MS_Date) is NULL
;

**makimax** · 09/02/2021, 15h27

Merci, merci à vous deux, c'est vraiment très gentil de prendre le temps de m'aider. C'est nettement mieux dans cette version "une seule requête" ! J'y avait pensé mais pas réussi à la faire. Mais...
Mais il y a toujours le problème de la réponse sans résultat lorsqu'il n'y a pas de relevé de compteurs le jour demandé.

Par exemple, aujourd'hui je n'ai pas fait de nouveaux relevés de compteur dans la table 'meter', donc pas de date correspondante, alors la réponse est vide.
Par contre cela fonctionne si il y a au moins un relevé, tout comme les "réponses à trous"

Peut être qu'il faut mieux passer par deux requêtes, la 1ère pour construire les lignes du tableau avec les infos des compteurs, et profiter du foreach pour faire la seconde (soit individuelles soit unique, je ne connais pas l'impact su le temps de traitement) et agrémenter ce tableau. La fin de la seconde contiendrait un WHERE pour faire le lien entre les deux (WHERE J0.MS_DevLogID = '{$device['dev_id']}' )

**escartefigue** · 09/02/2021, 16h41

Le principe des jointures externes est bien de ne rechercher que facultativement les données si présentes.

Donc, si on part des compteurs (table interne) qui eux sont immuablement présents pour aller vers les mesures (table externe), on aura toujours des résultats, pas à toutes les dates, mais des résultats.

Après, la question à se poser c'est quels sont les éléments dont on a besoin.
Si la requête est exécutée tous les jours, il semble normal de ne pas vouloir extraire à nouveau aujourd'hui les infos de la veille si pour certains compteurs rien n'a bougé ?

A préciser, car selon la réponse, la requête change.

**makimax** · 09/02/2021, 19h38

Le but du projet est de suivre la consommation de différents compteurs d'eau d'un même site comme un bâtiment d'élevage par exemple.
La page de bilan (accueil) sera donc consultée régulièrement, au moins une fois par jour, minimum. Et en cliquant sur le bouton dans le tableau de bilan on accédera aux graphiques propre à ce compteur.
Je ne sais pas si la page d'accueil peut être rafraîchit en automatique, mais pour le moment c'est très bien en statique, il faut juste actualiser pour que les valeurs s'actualisent. Voir les consommations des animaux, établir des diagnostiques, détecter des fuites, ...

La liste des compteurs dépend de la configuration du site, c'est pour cela que j'ai une page de configuration où j'ajoute X compteurs, leur défini un nom, un poids d'impulsion, etc afin de récupérer leur valeurs sur un récepteur radio (partie que je ferai si le "site" fonctionne, car c'est surtout pour moi un projet concret qui me permet de découvrir le code sous diverses forme car comprenez bien que pour moi je pars de 0, c'est à titre personnel, une initiation qui me change de ma CAO et de mes plans... et surtout ca me plaît !).

Je ne sais pas si cela réponds à la question.

Evidemment si on consulte ce "site" pour faire le suivi, que j'appellerai un monitoring, il faut bien que les compteurs configurés et actifs soient présents dans ce tableau bilan, y compris si il n'y a pas de relevés de fait, cela permettra d'ailleurs de voir qu'il y a éventuellement un problème de communication ou autre si je défini un timeout par rapport au last update.

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

Requêtes MySQL

Discussions similaires

Partager

Partager