Discussion :

[makimax]

Pour ne pas avoir de trous dans les échéances, il faut créer une table calendrier avec pour chaque jour une information permettant de savoir si des mesures sont attendues ou pas. (sauf si tous les jours font l'objet de mesures).

Bonjour et merci.
Je ne comprends pas l'idée d'une table calendrier ? Une vue plutôt ?
En théorie tous les jours font l'objet de mesures mais en réalité ce n'est pas le cas, par exemple si perte de communication avec les émetteurs, le récepteur,... et donc il est possible que qu'il n'y ai pas de relevé.
Avec les cas suivants : un relevé à J-2, aucun à J-1 et un nouveau à J, alors la consommation sera bien calculée (MAX de J - MAX de J-2), certe la consommation de J-1 sera "affectée" à mais c'est un peu logique.
Avec une table calendrier, si la date manquante est créée dans la table, cela voudrais dire qu'on créer une règle en cas d'absence, et que mettre, répéter la valeur précédente ?

Actuellement le problème se pose uniquement au premier relevé puisqu'il n'y a pas de "Max précédent".
MAX du Jour - Max du J précédent
MAX du Mois - Max du M précédent
MAX de l'année - Max de l'année précédente
Donc tant qu'il n'y a pas au moins un jour de passé, la "conso jour" restera vide sur le tableau de bilan. Ca passe encore pour un jour, mais un mois ou une année, cela fait long

[makimax]

Bonjour, après tout ce temps je reprends le projet à mes heures perdues et je rencontre quelques problèmes :


Pour le tableau de bilan, j'ai finalement utilisé UNE SEULE requête avec pas mal de JOIN LEFT sur les différentes VUES JOUR, MOIS, ANNEE et voici le résultat :


Mon problème est si je n'ai pas de relevé dans la journée, le tableau est totalement vide, alors que seule la colonne "Aujourd'hui" devrait être vide mais pas les autres.
Est-ce un problème de jointure ?

@escartefigue, vous parliez d'utilisez une table calendrier, cela y répondrait t'il ? en tout cas je comprends mieux l'intérêt des jour sans relevés, au moins il y aurait une réponse avec un jour mais sans relevé, alors que dans mon cas, pas de relevé signifie pas de jour non plus.


Merci de votre aide.

[escartefigue]

Bonjour makimax

@escartefigue, vous parliez d'utilisez une table calendrier, cela y répondrait t'il ? en tout cas je comprends mieux l'intérêt des jour sans relevés, au moins il y aurait une réponse avec un jour mais sans relevé, alors que dans mon cas, pas de relevé signifie pas de jour non plus.

En effet, c'était bien l'idée : la table calendrier comme table inner, toutes les autres en tables en outer, de sorte a avoir un résultat qu'il y ait ou pas une mesure effectuée

[makimax]

Pour ce tableau de bilan cela serait en effet possible mais j'ai peur du résultat des calculs de consommation puisque si je fait consommation du jour - celle du précédent et qu'il n'y a rien dans celle du jour car pas de relevé, cela donnerai un résultat négatif je pense ?
Par exemple si dans les 3 derniers jours, il y a un jour sans relevé, prenons l'exemple simple :

2021-08-06 : relevé compteur = 10
2021-08-07 : relevé compteur = Aucun relevé
2021-08-08 : relevé compteur = 14

Je risque d'avoir les consommations suivantes :
2021-08-07 : Consommation = -10 (Aucun - 10)
2021-08-08 : Consommation = 14 (14-Aucun)

Alors que cela devrait être :
2021-08-07 : Consommation = 0 (n'ayant pas de relevé, on pourrait utiliser le dernier connu donc 10-10).
2021-08-08 : Consommation = 4 (n'ayant pas de relevé, on pourrait utiliser le dernier connu donc 14-10).

L'avantage avec cette solution c'est aussi que lors d'une mise en graphique, les jours sans relevé serait visuellement présent.
Mais clairement, je n'ai pas idée comment créer une table calendrier (ou ceci https://sqlpro.developpez.com/cours/gestiontemps/#L6)

[escartefigue]

La fonction COALESCE() permet de remplacer les "null" par une valeur de son choix, par exemple zéro

[makimax]

La fonction COALESCE() permet de remplacer les "null" par une valeur de son choix, par exemple zéro

Avant de m'engager et de tout recommencer avec une table Calendrier, est-ce que vous pensez que cette fonction COALESCE() permettrait d'éviter la réponse totalement "vide" lors de la requête du bilan car j'ai essayé de la placer derrière les SELECT mais sans succès. Je pense mal l'utiliser car je me dit que nulle part je lui dit quelle est la valeur de remplacement.

La consommation du jour utilise la date du jour, alors que celles du mois / année, utilise un DATE_FORMAT, c'est quelque part un autre critère.
Même sans COALESCE, j'ai du mal à comprendre pourquoi lors des JOIN, je n'ai pas les résultats Mois / Année. Le type de jointure serait-il aussi responsable ? j'ai essayé INNER ou RIGHT mais à ce moment là, le PC se met à chauffer...
Et surtout, j'ai fait ces essais bêtement" et j'aimerai bien comprendre.

Pour rappel, la requête de ce tableau Bilan :

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
SELECT 
       devicestatus.dev_id
     , D0.V_CONSO_meter_logdate as 'Date_AD'
     , D1.V_CONSO_meter_logdate as 'Date_PD'
     , D11.V_CONSO_meter_logdate as 'Date_PD2'
     , D0.V_CONSO_meter_value as 'Index_AD' 
     , D1.Index_PD as 'Index_PD'
     , D11.Index_PD as 'Index_PD2'
     , D0.V_CONSO_meter_value - D1.Index_PD as 'Conso_AD'
     , D1.Index_PD - D11.Index_PD as 'Conso_PD'
     , date_format(D0.V_CONSO_meter_logdate, '%Y-%m') as 'Date_AM'
     , M0.V_CONSO_meter_logdate as 'Date_PM'
     , M0.V_CONSO_meter_value as 'Index_PM'
     , D0.V_CONSO_meter_value - M0.V_CONSO_meter_value as 'Conso_AM'
     , year(D0.V_CONSO_meter_logdate) as 'Date_AY'
     , Y0.V_CONSO_meter_logdate as 'Date_PY'
     , Y0.V_CONSO_meter_value as 'Index_PY'
     , D0.V_CONSO_meter_value - Y0.V_CONSO_meter_value as 'Conso_AY'
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
     )
LEFT JOIN 
     (SELECT V_CONSO_meter_devlog_id
            , V_CONSO_meter_logdate
            , V_CONSO_meter_value as Index_PD
       FROM v_conso_day
     ) D11
   ON D11.V_CONSO_meter_devlog_id = D0.V_CONSO_meter_devlog_id
  AND D11.V_CONSO_meter_logdate =
     ( SELECT DATE_ADD(max(D12.V_CONSO_meter_logdate), INTERVAL -1 DAY)
       FROM   v_conso_day D12
       WHERE D12.V_CONSO_meter_devlog_id=D0.V_CONSO_meter_devlog_id
         AND D12.V_CONSO_meter_logdate < D0.V_CONSO_meter_logdate
     )
LEFT JOIN v_conso_month M0
    ON M0.V_CONSO_meter_devlog_id=D0.V_CONSO_meter_devlog_id
    AND M0.V_CONSO_meter_logdate=DATE_FORMAT(DATE_ADD(D0.V_CONSO_meter_logdate, INTERVAL -1 MONTH), '%Y-%m')
LEFT JOIN v_conso_year Y0
    ON Y0.V_CONSO_meter_devlog_id=D0.V_CONSO_meter_devlog_id
    AND Y0.V_CONSO_meter_logdate=DATE_FORMAT(DATE_ADD(D0.V_CONSO_meter_logdate, INTERVAL -1 YEAR), '%Y')
WHERE dev_used = 1 
    AND date(D0.V_CONSO_meter_logdate) = DATE( NOW()) OR date(D0.V_CONSO_meter_logdate) IS NULL
ORDER BY dev_id

Encore merci pour l'aide, cela fait vraiment plaisir.

[escartefigue]

La syntaxe est coalesce(Col1, Col2, ..., Coln, valeur).

La première valeur non nulle est retenue

Par exemple COALESCE(ma_colonne, 5) restitue la valeur de ma_colonne si présente et 5 si ma_colonne est marquée "null".

[makimax]

J'essaye dans tous les sens sans succès, j'ai donc essayé sur une requête plus simple, celle qui liste les relevés max de chaque jour (le dernier puisque c'est incrémentiel). Sachant que je n'ai eu aucun relevé aujourd'hui, j'aimerai trouver 0 au lieu d'une absence de réponse.

Je suis parti de la requête originale qui me donne 3 colonnes en réponse mais vide car aucun relevé ces dernières 25 heures :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT meter_devlog_id as V_CONSO_meter_devlog_id
             , meter_value as V_CONSO_meter_value
             , meter_logdate as V_CONSO_meter_logdate
        FROM `meter`
        WHERE meter_logdate > (NOW() - INTERVAL 25 HOUR) /* limiter a 25 heures pour éviter un traitement trop long*/
        AND meter_logdate <= NOW()
        GROUP BY V_CONSO_meter_devlog_id
               , V_CONSO_meter_logdate
        ORDER BY V_CONSO_meter_devlog_id
               , V_CONSO_meter_logdate
       )

J'ai essayé avec un COALESCE, j'ai une réponse mais toujours vide mais cette fois au lieu de lister 3 colonnes sans réponse, j'obtiens une seule vide qui est COALESCE(meter_devlog_id , meter_value , meter_logdate ,0)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT COALESCE(meter_devlog_id
             , meter_value
             , meter_logdate
                ,0)
        FROM `meter`
        WHERE meter_logdate > (NOW() - INTERVAL 25 HOUR) /* limiter a 25 heures pour éviter un traitement trop long*/
        AND meter_logdate <= NOW()
        GROUP BY meter_devlog_id
               , meter_logdate
        ORDER BY meter_devlog_id
               , meter_logdate
       )

Avec celle-ci j'ai 3 colonnes dont la 3eme est le COALESCE mais toujours vide :

Code :

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

SELECT meter_logdate, meter_devlog_id , COALESCE(meter_value,0) FROM `meter` WHERE meter_logdate > (NOW() - INTERVAL 25 HOUR) AND meter_logdate <= NOW() GROUP BY meter_devlog_id , meter_logdate ORDER BY meter_devlog_id , meter_logdate

[escartefigue]

Je me suis visiblement mal expliqué

Pour avoir toutes les occurrences de dates attendues, qu'il y ait ou pas une mesure, il faut préalablement créer une table calendrier contenant toutes les dates attendues.
Ensuite, il faut faire une requête dans laquelle la table calendrier est la INNER TABLE et celle des mesures est la OUTER TABLE
Du coup, la restriction (WHERE) doit porter sur la table INNER (calendrier) et non plus sur la table OUTER (METER)

Soit quelque chose comme ceci :

Code SQL :

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

1
2
3
4
5
6
7
8
9
select CAL.CDATE
     , CAL.JOURSEM
     , coalesce(MTR.meter_devlog_id, 0)
     , coalesce(MTR.meter_value, 0)
from calendrier as CAL
left outer join meter as MTR
   on MTR.meter_logdate = CAL.CDATE
where CAL.CDATE > (NOW() - INTERVAL 25 HOUR) /* limiter a 25 heures pour éviter un traitement trop long*/
  and MTR.meter_logdate <= NOW()

[makimax]

Vous vous étiez très bien exprimé, c'est juste que je comprends vite mais il faut m'expliquer longtemps
Je tentais de le faire sans calendrier dans un premier temps, mais là je comprends que c'était impossible car pas de référence toujours présente et pour réussir cela il faut une jointure et un INNER d'une table de référence. Si je veux le faire sans calendrier, je pourrais faire référence à la liste des dev_id de ma table devices qui est la table des compteurs.

Mais je suis convaincu que votre idée de table calendrier reste l'idéal et vais m'y pencher. On est d'accord que c'est une table qui est générée par une requête et qui une fois créée reste là uniquement comme référence pour mes autres requêtes ?
Cette source est-elle selon vous adaptée ? : https://gist.github.com/bryhal/4129042

[escartefigue]

Si je veux le faire sans calendrier, je pourrais faire référence à la liste des dev_id de ma table devices qui est la table des compteurs.

Faire de la table DEVICE la table INNER ne convient que si on veut un résultat par compteur, qu'il y ait ou pas une mesure
Là on veut un résultat par date qu'il y ait ou pas une mesure. Du coup le calendrier s'impose.

On est d'accord que c'est une table qui est générée par une requête et qui une fois créée reste là uniquement comme référence pour mes autres requêtes ?

Oui

Cette source est-elle selon vous adaptée ? : https://gist.github.com/bryhal/4129042

Ca semble adapté (à tester), peut être à compléter avec des dates particulières pour lesquelles on ne voudrait pas de résultat (jours fériés par exemple).



Et dans ma réponse n° 69, j'ai oublié de déplacer une partie de la restriction de la table outer vers la table inner :
and MTR.meter_logdate <= NOW() à remplacer par and CAL.meter_logdate <= NOW().

[makimax]

Pour le tableau bilan, il me faut un résultat par compteur, dans ce cas je mets la table device en INNER.
Pour les graphiques, il me faut un résultat par date car il faut une logique dans la représentation des tracés et afficher l'absences de relevés. dans ce cas il me faut une table calendrier en INNER.
Ca commence à rentrer

Au bout de 4 heures de persévérance et surtout les yeux qui piquent, ca y est j'arrive enfin au résultat pour le tableau bilan, table DEVICE en INNER :

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
SELECT 
       devicestatus.dev_id
     , devicestatus.dev_name
     , devicestatus.dev_group_id    
     , groups.grp_group_name
     , DATE(NOW()) as 'Date_AD'
     , D10.V_CONSO_meter_logdate AS 'Date_PD'
     , D11.V_CONSO_meter_logdate AS 'Date_PD2'
     , D0.V_CONSO_meter_value AS 'Index_AD'
     , D10.V_CONSO_meter_value AS 'Index_PD' 
     , D11.Index_PD AS 'Index_PD2'
     , COALESCE (D0.V_CONSO_meter_value - D1.Index_PD,0) AS 'Conso_AD'
     , COALESCE (D10.V_CONSO_meter_value - D11.Index_PD,0) AS 'Conso_PD'
     , date_format(DATE(NOW()), '%Y-%m') as 'Date_AM'
     , M0.V_CONSO_meter_logdate AS 'Date_PM'
     , M0.V_CONSO_meter_value AS 'Index_AM'
     , M1.Index_PM AS 'Index_PM'
     , COALESCE (M0.V_CONSO_meter_value - M1.Index_PM,0) AS 'Conso_AM'
     , year(DATE(NOW())) as 'Date_AY'
     , Y0.V_CONSO_meter_logdate AS 'Date_PY'
     , Y0.V_CONSO_meter_value AS 'Index_AY'
     , Y1.Index_PY AS 'Index_PY'
     , COALESCE (Y0.V_CONSO_meter_value - Y1.Index_PY,0) AS 'Conso_AY'
     , devicestatus.dev_nvalue    
     , devicestatus.dev_last_update
     , devicestatus.dev_signal_level
     , devicestatus.dev_battery_level
FROM `devicestatus`
LEFT JOIN groups
    ON groups.grp_id = devicestatus.dev_group_id
LEFT JOIN v_conso_day D0 
    ON D0.V_CONSO_meter_devlog_id = devicestatus.dev_id
    AND date(D0.V_CONSO_meter_logdate) = DATE( NOW()) OR date(D0.V_CONSO_meter_logdate) IS NULL
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
        )
LEFT JOIN v_conso_day D10 
    ON D10.V_CONSO_meter_devlog_id = devicestatus.dev_id
    AND date(D10.V_CONSO_meter_logdate) = DATE(DATE_ADD(NOW(), INTERVAL -1 DAY)) OR date(D10.V_CONSO_meter_logdate) IS NULL
LEFT JOIN 
    (SELECT V_CONSO_meter_devlog_id
            , V_CONSO_meter_logdate
            , V_CONSO_meter_value as Index_PD
       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_month M0
    ON M0.V_CONSO_meter_devlog_id = devicestatus.dev_id
    AND M0.V_CONSO_meter_logdate = date_format(NOW(), '%Y-%m') OR M0.V_CONSO_meter_logdate IS NULL
LEFT JOIN
    (SELECT 
        V_CONSO_meter_devlog_id,
        V_CONSO_meter_logdate,
        V_CONSO_meter_value as Index_PM
    FROM v_conso_month
    ) M1
    ON M1.V_CONSO_meter_devlog_id = M0.V_CONSO_meter_devlog_id
    AND M1.V_CONSO_meter_logdate = 
        (SELECT max(M2.V_CONSO_meter_logdate)
        FROM v_conso_month M2
        WHERE M2.V_CONSO_meter_devlog_id = M0.V_CONSO_meter_devlog_id
        AND M2.V_CONSO_meter_logdate < M0.V_CONSO_meter_logdate
        )
LEFT JOIN
    v_conso_year Y0
    ON Y0.V_CONSO_meter_devlog_id = devicestatus.dev_id
    AND Y0.V_CONSO_meter_logdate = year(NOW()) OR Y0.V_CONSO_meter_logdate IS NULL
LEFT JOIN
    (SELECT 
        V_CONSO_meter_devlog_id,
        V_CONSO_meter_logdate,
        V_CONSO_meter_value as Index_PY
    FROM v_conso_year
    ) Y1
    ON Y1.V_CONSO_meter_devlog_id = Y0.V_CONSO_meter_devlog_id
    AND Y1.V_CONSO_meter_logdate =
        (SELECT max(Y2.V_CONSO_meter_logdate)
        FROM v_conso_year Y2
        WHERE Y2.V_CONSO_meter_devlog_id = Y0.V_CONSO_meter_devlog_id
        AND Y2.V_CONSO_meter_logdate < Y0.V_CONSO_meter_logdate
        )
WHERE
devicestatus.dev_used = 1 
ORDER BY devicestatus.dev_name, groups.grp_group_name

En tout cas merbi beaucoup, et encore merci pour les vues car elles sont indispensable.
Je vais même en créer des plus légères qui filtrent les 2 derniers jour, le dernier mois, la dernière année afin que cette requête soit la plus réactive possible.

Etape suivante, Calendrier, mais pas pour ce soir.