Bonjour, j'ai une table, qui est remplie par ma domotique. Je princinle qu'il y a toutes les valeurs dans une seule table. Certaines entrées sont effectuées par un module statistique. Il calcule la consommation d'eau mensuelle. Maintenant, je veux créer une requête, qui donne une vue mensuelle de la consommation pour chaque année.
Comme:
SELECT
monthname(TIMESTAMP) Monat,
VALUE '2018'
FROM
fhem.history acht
WHERE
DEVICE LIKE 'Wasserverbrauch'
AND READING LIKE 'statStateMonthLast'
AND year(TIMESTAMP) = 2018
La requête pour un an est: p >
|Month|2018|2019| |Jan..|7.3 |6.8| |Feb |5.9 |7.8|....
Mais comment rejoindre les valeurs de 2019? Quelqu'un peut-il m'indiquer la bonne direction?
Merci,
Ruediger
3 Réponses :
Vous pouvez effectuer une agrégation conditionnelle:
SELECT
MONTHNAME(timestamp) Monat,
MAX(CASE WHEN timestamp >= '2018-01-01' AND timestamp < '2019-01-01' THEN value END) `2018`,
MAX(CASE WHEN timestamp >= '2019-01-01' AND timestamp < '2020-01-01' THEN value END) `2019`
FROM fhem.history acht
WHERE
device = 'Wasserverbrauch'
AND reading = 'statStateMonthLast'
AND timestamp >= '2018-01-01'
AND timestamp < '2020-01-01'
GROUP BY MONTHNAME(timestamp)
Remarques:
les conditions LIKE dans la clause WHERE sont en fait équivalentes à = s, car il n'y a pas de caractère générique dans l'opérande de droite ; Je les ai modifiés en conséquence
il est généralement préférable de faire une comparaison explicite de la plage datetime plutôt que d'utiliser des fonctions de date (dans votre cas, year () ), car cette dernière empêche l'utilisation d'un index
si vous devez gérer plusieurs enregistrements par mois (ce qui ne semble pas être le cas en fonction de votre requête existante), vous devez utiliser une autre fonction d'agrégation pour obtenir un résultat plus raisonnable ( sum ( ) ou avg())
Je soupçonne que ça devrait être SUM, pas MAX
@ysth: pourrait être ... J'ai supposé MAX () car il n'y a pas d'agrégation dans la requête initiale OP,.
@GMB il me semble que la requête ne fonctionne pas comme prévu. db-fiddle.com/f/4jQGvpJzUYtXeaSNcx811J/0
@artoodetoo: cela fonctionne comme prévu ... Votre violon suppose plusieurs enregistrements par mois, ce qui n'est pas clairement indiqué dans la question (d'où MAX () ci-dessus contre SUM () débat). De plus, votre échantillon de données ne contient aucune donnée pour 2018 (OP veut 2018-2019, pas 2019-2020). Je ne vois pas vraiment pourquoi cette réponse a suscité un vote défavorable.
D'accord, il n'est pas indiqué que nous ayons un ou plusieurs enregistrements par mois. Donc, utiliser MAX () ici ne semble pas justifié.
@artoodetoo: si vous regardez la requête existante, vous pouvez voir qu'elle suppose un enregistrement par mois. Mais depuis que vous et vous avez commenté, j'ai mis à jour ma réponse avec plus de contexte.
Vous pouvez utiliser la fonction EXTRACT () pour obtenir l'année / le mois à partir de l'horodatage et une expression conditionnelle pour tout mettre dans des colonnes.
Je pense qu'une table avec des numéros de mois et des noms serait utile pour la jointure externe.
SELECT
m.`month`,
SUM(IF(h.`year_no`=2019, h.`value`, 0)) AS `2019`,
SUM(IF(h.`year_no`=2020, h.`value`, 0)) AS `2020`
FROM `monthes` AS m
LEFT JOIN (
SELECT
EXTRACT(YEAR FROM `timestamp`) AS `year_no`,
EXTRACT(MONTH FROM `timestamp`) AS `month_no`,
`value`
FROM `history`
) AS h ON h.`month_no` = m.`id`
GROUP BY m.`id`
J'ai utilisé des identifiants de mois GROUP BY, donc le résultat sera implicitement ordonné par numéro de mois, pas par nom de mois.
https://www.db-fiddle.com/f / 78LQtak6GwkDS8pzredroQ / 0
Je n'ai PAS utilisé de filtres supplémentaires ici pour la bravoure, mais si vous en avez besoin, ajoutez WHERE ... dans la sous-requête imbriquée ci-dessous FROM historique .
Ceci est une variante de la réponse de GMB. Mais si vous voulez les résultats par ordre chronologique par mois, vous devez l'inclure dans la requête:
SELECT MONTHNAME(timestamp) as Monat,
SUM(CASE WHEN YEAR(timestamp) = 2018 THEN value END) value_2018,
SUM(CASE WHEN YEAR(timestamp) = 2019 THEN value END) as value_2019
FROM fhem.history acht
WHERE device = 'Wasserverbrauch' AND
reading = 'statStateMonthLast' AND
timestamp >= '2018-01-01' AND
timestamp < '2020-01-01'
GROUP BY MONTHNAME(timestamp), MONTH(timestamp)
ORDER BY MONTH(timestamp);
Envisagez sérieusement de gérer les problèmes d'affichage des données dans le code d'application