MYSQL query

[BjorntobeWild]

Ik heb een MySQL database met daarin een tabel welke als volgt is opgebouwd:

Datum	Leveren_T1	LeverenT2	Verbruik_T1	Verbruik_T2
2001-01-01	15.00	3.00	2.00	5.00
2001-01-02	13.55	2.25	5.32	4.25

Nu wil ik een query maken om dit weer te geven in een tabel welke ik later ga gebruiken om een grafiek van te maken. Het resultaat van de tabel moet er als volgt uitzien:

Maand	Energiehoeveelheid	Tarief	Categorie
2001-jan	450	1	Verbruik
2001-jan	412	2	Verbruik
2001-jan	25	1	Levering
2001-jan	0	2	Levering
2001-feb	564	1	Verbruik
2001-feb	45	2	Verbruik
2001-feb	0	1	Levering
2001-feb	10	2	Levering

Met de volgende query krijg ik de data er wel uit, maar niet met het gewenste resultaat.

code:

SELECT DATE_FORMAT((stamp), '%M %Y') AS Maand, SUM(verbruik_1) AS 'Hoog tarief', SUM(verbruik_2) AS 'Laag tarief', SUM(terugleveren_1) AS 'Terugleveren hoog tarief', SUM(terugleveren_2) AS 'Terugleveren laag tarief' FROM energy_week WHERE idx=135 group by YEAR(stamp), MONTH(stamp) ORDER BY stamp ASC

Ik wil graag weten hoe ik de kolommen Tarief en Categorie moet maken in MySQL.

[g0tanks]

Ik denk dat het met UNION moet kunnen: https://stackoverflow.com...o-unpivot-columns-to-rows

[CyBeRSPiN]

Ja met een union.
Even uit de losse pols op mn iPad, maar iets als dit zou moeten werken:

code:

Select maand, sum(kWh) as energiehoeveelheid, tarief, categorie
From (
Select t1.maand, t1.verbruik_T1 as kWh, 1 as tarief, “Verbruik” as categorie
From meterstand_tabel t1

UNION ALL

Select t2.maand, t2.verbruik_T2 as kWh, 2 as tarief, “Verbruik” as categorie
From meterstand_tabel t2

UNION ALL

Select t3.maand, t3.leveren_T1 as kWh, 1 as tarief, “Leveren” as categorie
From meterstand_tabel t3

UNION ALL

Select t4.maand, t4.leveren_T2 as kWh, 2 as tarief, “Leveren” as categorie
From meterstand_tabel t4

) 
Group by maand, tarief, categorie

[ Voor 6% gewijzigd door CyBeRSPiN op 05-05-2021 23:01 ]

[Hydra]

Je issue is eigenlijk vooral dat je datamodel 'verkeerd' is waardoor je het jezelf nu moelijk maakt. Het komt er kortweg op neer dat als je meer devices moet toevoegen je meer kolommen toe moet voegen, je verkeerd bezig bent.

Uiteindelijk maak je het jezelf makkelijker als je overgaat naar een datum, device id, leveren, verbruik tabel gaat werken waar je voor "t1" en "t2" aparte regels insert.

[BjorntobeWild]

Hydra schreef op donderdag 6 mei 2021 @ 10:11:
Je issue is eigenlijk vooral dat je datamodel 'verkeerd' is waardoor je het jezelf nu moelijk maakt. Het komt er kortweg op neer dat als je meer devices moet toevoegen je meer kolommen toe moet voegen, je verkeerd bezig bent.

Uiteindelijk maak je het jezelf makkelijker als je overgaat naar een datum, device id, leveren, verbruik tabel gaat werken waar je voor "t1" en "t2" aparte regels insert.

Er zit ook een kolom 'id' in de tabel, deze heb ik gemakshalve weggelaten. Met de query van @CyBeRSPiN (iets aangepast) lukte het wel de juiste output te krijgen.

Thnx!

[RobIII]

BjorntobeWild schreef op donderdag 6 mei 2021 @ 10:25:
[...]


Er zit ook een kolom 'id' in de tabel, deze heb ik gemakshalve weggelaten

Dat is niet wat @Hydra zegt noch bedoelt
Lees je eens in in databasenormalisatie. Je model is "fout". Je wil een tabel met "devices"(?) en in je huidige tabel wil je daar naar verwijzen i.p.v. voor elk ding een nieuwe kolom te moeten maken.

[BjorntobeWild]

RobIII schreef op donderdag 6 mei 2021 @ 10:42:
[...]

Dat is niet wat @Hydra zegt noch bedoelt
Lees je eens in in databasenormalisatie. Je model is "fout". Je wil een tabel met "devices"(?) en in je huidige tabel wil je daar naar verwijzen i.p.v. voor elk ding een nieuwe kolom te moeten maken.

Ik begrijp wat je bedoelt met de normalisatie van databases. Ben er ook wel (een beetje) mee bekend. Dit is echter een tabel uit Domoticz welke ik rechtstreeks heb geïmporteerd naar mysql.
In deze tabel staan maar twee devices (de energiemeter en de zonnepanelen). Beide zijn weergegeven als een integer. Namelijk 135 voor de verbruikte energie van de energiemeter en 145 voor de opgewekte energie van de zonnepanelen.

Naar mijn inziens is er niks mis met de manier waarop de tabel momenteel is opgebouwd. Indien ik de verbruikte of opgewekte energiehoeveelheden per maand wil uitlezen dan kan ik dat met de eerder genoemde query weergeven.

Mijn doel met de gevraagde query was om de tabel op een andere manier weer te geven omdat ik de data wil visualiseren in een Highcharts grafiek (als gestapelde en gegroepeerde kolom) zie linkje. De grafiek wordt gegenereerd aan de hand van de data in de tabel, alleen werkt nog niet zoals ik had verwacht.

Ik zal dus nog even door moeten puzzelen.

[matthijsln]

BjorntobeWild schreef op donderdag 6 mei 2021 @ 16:35:
[...]
Naar mijn inziens is er niks mis met de manier waarop de tabel momenteel is opgebouwd. Indien ik de verbruikte of opgewekte energiehoeveelheden per maand wil uitlezen dan kan ik dat met de eerder genoemde query weergeven.

Mijn doel met de gevraagde query was om de tabel op een andere manier weer te geven omdat ik de data wil visualiseren in een Highcharts grafiek (als gestapelde en gegroepeerde kolom) zie linkje. De grafiek wordt gegenereerd aan de hand van de data in de tabel, alleen werkt nog niet zoals ik had verwacht.

Die grafiek zit toch al in Domoticz op de http://domoticz:8080/#/Devices/<deviceId>/Report pagina? De Domoticz pagina gebruikt het API endpoint
http://domoticz:8080/json.htm?actyear=2021&idx=1&range=year&sensor=counter&type=graph en doet het groeperen per maand en het maken van een Highcharts grafiek in het JavaScript bestand EnergyMultiCounterReport.js.

De Domoticz tabel is inderdaad niet genormaliseerd, maar een kolom voor dal- en normaaltarief is misschien nog niet eens zo raar. Nog erger, de tabel MultiMeter_Calendar heeft kolommen genaamd Value1 t/m Value6 en Counter1 t/m Counter4

Als je de broncode van het endpoint om de gegevens als JSON op te halen bekijkt breekt helemaal je klomp