DB Abfrage / Mittelwert berechnen / in Tabelle eintragen

[BerndS]

Hallo
erstmal zu meinem Projekt.
Ich möchte mir meine Verbrauchswerte von meiner Photovoltaikanlage per Diagram anzeigen lassen.
Als Grundlage habe ich hier MySQL 5.1 Python 3.2 das ganze läuft auf einem QNAP NAS.
Derzeitig werden die Werte von der PV-Anlage minütlich per Python in die DB eingetragen
Tabelle: Produktion <---- Werte werden eingetragen von Python
Spalten
ID <---- ID mit Auto_Increment
datumzeit <---- Zeitstempel - TYP timestamp
sensorwert <---- aktuelle Stromerzeugung
kwTag <---- gesamte Stromerzeugung am Tag
Gleichzeitig werden auch Werte vom Stromzähler eingetragen

Tabelle: Verbrauch <---- Werte werden eingetragen von Arduino MEGA 2560
ID <---- ID mit Auto_Increment
datumzeit <---- Zeitstempel TYP datetime
sensorwert <---- aktueller Stromverbrauch

Da die Datenflut doch recht groß ist möchte ich eine extra Tabelle nutzten in der die Durchschnittswerte gespeichert werden, ein 5 Minuten durchschnitt.
Erst wollte ich dies per Pyhton machen und einem Skript welches im 5 Minuten Takt durchläuft.
Aber im Pyhton Forum hat man mir erzählt das könne MySQL auch gleich so, das ich mir den Schritt sparen könnte und nun bin ich hier.

Die extra Tabelle:
Tabelle: VerbrauchWoche

ID <---- ID mit Auto_Increment
datumzeit <---- Zeitstempel TYP datetime
Produktion <---- 5minuten Durchschnittswerte
Verbrauch <---- 5minuten Durchschnittswerte
Differenz <---- Differenz zwischen Verbrauch und Produktion


Jetzt ist die Idee
in Tabelle VerbrauchWoche den letzten Zeitstempel zu prüfen (Bsp: 2016-03-14 00:00:00)
diesen in der Tabelle Produktion zu suchen nur ohne Sekunden anschließend aus den Werten der nächsten 5Minuten einen Durchschnitt zu berechen , das gleiche nur mit der Tabelle Verbrauch. Die erechneten Werte eintragen, die Differenz berechnen und einen Zeitstempel zu erstellen Bsp: 2016-03-14 00:05:00.

So und dies soll nun in einer Schleife laufen bis maximal 6Minuten vor datetime(NOW).

Bin hier da mit einem reinem SQL Code auf dem Holzweg oder doch lieber über zwischen Schritte per Python oder PHP oder oder oder?

Vielleicht könnt ihr mir ja schon ein paar Tipps geben zum Code oder zu den Tabellen.

Grüße der Arne

 

[BerndS]

Hallo
die ersten Schritte sind geschafft, ich denke das ich die Berechnungen alle in SQL machen kann und ohne Umweg über PHP & co

Der Code um den Durschnitt und die Differenz zu berechnen

INSERT INTO `VerbrauchWoche` (Produktion,Verbrauch,DatumZeit) VALUES 
((SELECT avg(sensorwert)  FROM `Produktion` WHERE `datumzeit` > '2016-10-01 12:05:00' AND `datumzeit` < '2016-10-01 12:10:00'),
(SELECT avg(sensorwert)  FROM `Verbrauch` WHERE `datumzeit` > '2016-10-01 12:05:00'  AND `datumzeit` < '2016-10-01 12:10:00'),
'2016-10-01 12:10:00')
;
UPDATE `PV`.`VerbrauchWoche` SET `Differenz` = (`Produktion`-`Verbrauch`) WHERE `VerbrauchWoche`.`Differenz` = "";

Jetzt würde ich aus der Tabelle "VerbrauchWoche" den ältesten Eintrag suchen

SELECT `DatumZeit` FROM `VerbrauchWoche` ORDER BY `VerbrauchWoche`.`DatumZeit` DESC LIMIT 1

Der Gedanke war nun diese Abfrage in einer Variablen zu speichern und in die Berechnung einzufügen

SET @Zeit  := (SELECT `DatumZeit` FROM `VerbrauchWoche` ORDER BY `VerbrauchWoche`.`DatumZeit` DESC LIMIT 1);
SELECT @Zeit

Dabei bekomme ich aber nur als Antwort

@Zeit
[BLOB - 19 B]


Das kann doch nicht richtig sein. Erwartet hätte ich 2016-10-01 12:05:00 bei SELECT @Zeit+1 kommt 2017. Erwünscht wäre 2016-10-01 12:10:00.
Wo ist der Fehler in der Variable?

 

[akretschmer]

Jetzt ist die Idee

Ich würde erst einmal die Idee in Frage stellen. Dz reduzierst die Anzahl der Datensätze grad mal auf 1/5. Mit eine guten Indexstrategie holst Du die Daten aus den Rohtabellen schnell genug raus, mal eben ein paar hundert oder tausend Datensätze zu aggregieren ist kein so großes Ding - erwähnte ich schon, daß PG in 9.6 das sogar parallel machen kann?

Jedenfalls, ohne Not die Daten redundant zu speichern sehe ich nicht zielführend. Was machst Du, wenn Du statt 5-minütiger Mittelwerte die aller 10 Minuten oder stündlich brauchst, weil der zu betrachtende Zeitraum sich ändert?

 

[BerndS]

Hallo
die Reduzierung der Datensätze ist für mich "sinnvoll" da ich wie gesagt die Daten per Diagramm (AmChart) anzeigen lassen möchte.
Dies läuft auch schon aber
1. sehr lange Ladezeiten
2. werden, wohl durch AmChart die Datensätze reduziert, ich habe aber keine Ahnung ob hier ein Durchschnitt berehnet wird oder nur jeder "5" Datensatz genutzt wird.

Die Anzeige soll auch nur für mich privat reichen so genau muss sich nicht sein.

 

[BerndS]

Hallo mal wieder
den CODE habe ich nun so abgeändert:

SET @Zeit  := (SELECT `DatumZeit` FROM `VerbrauchWoche` ORDER BY `VerbrauchWoche`.`DatumZeit` DESC LIMIT 1);
SET @Zeit5  := (SELECT TIMESTAMPADD(MINUTE,5,(SELECT `DatumZeit` FROM `VerbrauchWoche` ORDER BY `VerbrauchWoche`.`DatumZeit` DESC LIMIT 1)));

INSERT INTO `VerbrauchWoche` (Produktion,Verbrauch,DatumZeit) VALUES 
((SELECT avg(sensorwert)  FROM `Produktion`
WHERE `datumzeit` > @Zeit
AND `datumzeit` < '@Zeit5'),(SELECT avg(sensorwert)  FROM `Verbrauch`
WHERE `datumzeit` > @Zeit
AND `datumzeit` < @Zeit5),@Zeit5);
UPDATE `PV`.`VerbrauchWoche` SET `Differenz` = (`Produktion`-`Verbrauch`) WHERE `VerbrauchWoche`.`Differenz` = "";

Leider werden für die Variabel keine Daten geschrieben "# MySQL lieferte ein leeres Resultat zurück (d.h. null Datensätze)" wenn ich den Befehl

SELECT `DatumZeit` FROM `VerbrauchWoche` ORDER BY `VerbrauchWoche`.`DatumZeit` DESC LIMIT 1

ausführe gibt es als Antwort :
DatumZeit Zeitstempel
2016-09-30 00:35:00

Das wäre auch richtig.
Wo habe ich den Fehler in der Variablen gemacht?

Grüße der Arne