Ich bitte wieder einmal um Hilfe bei einer Abfrage

[photonensammler]

Guten Morgen und einen schönen Feiertag!

die freie Zeit heute möchte ich für Datenauswertung nutzen, komme aber mit meiner Anfänger-Mysql-Abfrage nicht weiter.
Ihr könnt mir doch bestimmt wieder helfen ?

In einer Tabelle eines (solar)Datenloggers habe ich nun schon seit fast 11 Jahren Einträge in dieser Form (auf die relevanten Spalten reduziert):

datum_zeit	wh
2007-08-01 10:40:00	5379
2007-08-01 10:45:00	5504
2007-08-01 10:50:00	5829
2007-08-01 10:55:00	6160
....	...
2008-08-01 10:40:00	6186
2008-08-01 10:45:00	6468
2008-08-01 10:50:00	6751
2007-08-01 10:55:00	7033
....	...
2009-08-01 10:40:00	6845
2009-08-01 10:45:00	7162
2009-08-01 10:50:00	7483
2009-08-01 10:55:00	7807
....	...

Die Spalte wh enthält die aufsummierten Wattstunden der Anlage, datum_zeit wird vom Datenlogger erzeugt, einen neuen Eintrag erhalte ich alle 5 Minuten zwischen Sonnenaufgang und Sonnenuntergang.

Ich suche den maximalen Ertrag über 5 Minuten zum gleichen Datum/Uhrzeit, egal in welchem Jahr er auftrat.

Meine Abfrage sieht so aus und funktioniert natürlich nicht, sollte aber zeigen, was ich eigentlich ermitteln möchte:

SELECT max(t1.wh-t2.wh) FROM `test`t1,`test`t2 WHERE t1.datum_zeit like '%08-01 10:50%' and t2.datum_zeit like '%08-01 10:45';

...oder vielleicht noch besser so (als Entwurf, den natürlich so nicht funktioniert)....

SELECT max(t1.wh-t2.wh) FROM `test`t1,`test`t2 WHERE t1.datum_zeit like '%08-01 10:50%' and t2.datum_zeit like (t1.datum_zeit - 5 Minuten);

Die Abfrage soll später in PHP umgesetzt werden.
Der Sinn der ganzen Sache ist, aus allen Daten der letzten 11 Jahre diese 5-Minuten Maximalerträge über eine Stunde zu summieren.
Mit dieser Summe kann dann mit großer Sicherheit davon ausgegangen werden, dass dieser "Maximalertrag" durch 100% Sonnenscheindauer erzeugt wurde.
Über diese Summe könnte sich somit eventuell die Stromproduktion der Anlage für den aktuellen Tag vorhersagen lassen wenn dies mit der Vorhersage (stündliche Sonnenescheindauer) von http://wetterstationen.meteomedia.de...fik/103850.png abgeglichen wird.

....mal probieren, ob es klappt ....

Schon jetzt vielen Dank für die Hilfe!

Gruß
Jörg

[Perry Staltic]

Das Problem ist die bereits aggregierte Speicherweise, die für (Standard) SQL relativ schwierig zu handhaben ist. Jegliche Berechnung, die Einzelwerte benötigt, muss also diese erstmal "rausfinden".
Das Rausfinden scheitert prinzipbedingt in SQL daran, dass Funktionen zum Zugriff auf einen "vorigen" Datensatz (hier zur Differenzbildung) nicht vorgesehen sind.
Es gibt sogenannte Window Funktionen die das können in vielen aktuellen Datenbanksystemen, allerdings (noch) nicht in mysql (kann sein, dass V 8 das nun kann).
Dein 2. Ansatz wäre schon ein brauchbarer "Trick" den Vorgänger reinzu joinen. Aber bitte nicht mit Like arbeiten. Respektiere die Typen der Spalten (hoffentlich hier tatsächlich ein Datum bzw timestamp)
und die zugehörigen / gültigen Operationen. In Deinem Fall ein exakter Datumsvergleich von t1.datum = t2.datum - 5 minuten.Dafür gibt es "interval" in mysql oder addtime.
Das funktioniert nur, wenn die Darstellung oben mit den 5 Minutenintervall tatsächlich so ist und keine von Dir vorgenommene "Vereinfachung aufs Wesentliche".
Ist die Zeitangabe aus der Messwertaufnahme ungenau(er), gibt es aber noch andere Möglichkeiten.
Also, versuch mal Dein 2.Statement mit Interval und ohne Like anzupassen.

[protestix]

Es gibt da eine etwas unkonventionelle Möglichkeit in Mysql.

SET @diff:=5379;
SELECT  datum, (kwh-@diff) AS maxkwh, @diff:=kwh AS current
FROM sunpower
ORDER BY maxkwh DESC LIMIT 1 

Mit Set legt man den Startwert fest in einer Variablen. Das kann man in einer extra Query erledigen bevor man die SELECT Query abschickt oder mit einem Multiquery.
diff ist hier die Variable die den vorhergehenden Wert enthält. Mit ORDER BY wird die Ausgabe absteigend nach der Differenz sortiert , so das der grösste Wert oben steht. Da ja nur das Maximum gebraucht wird, trennen wir den Rest ab in dem wir mit Limit 1 die Ausgabe auf den ersten Wert beschränken.
Ist bei vielen Datensätzen, vielleicht nicht die schnellste Variante, aber besser als mit PHP aus einem Array zu fischen ist es allemal.

Habe dazu mal ein sqlfiddle angelegt
http://sqlfiddle.com/#!9/9a1dad/1

Perry Staltic hat ja schon auf die Problem hingewiesen, aber wenn du immer 5 Minuten Abstand hast, sollte es so machbar sein.

[Perry Staltic]

Ich fürchte, diese iterierende Lösung aus PHP ist mit dem Order By auf 1 Mio Datensätze sehr ressourcenhungrig.
Ergänzend zu meinen Ansätzen würde ich eher folgendes vorschlagen.
- Funktionierenden Join auf Vorgänger anlegen
- per Where die Gesamtmenge Datum bezogen auf einen gewünschten Bereich einschränken (t1.Datum between <a> and <b>)
- auf der Ergebnismenge die Max Ermittlung ausführen

[Perry Staltic]

Hab mich mal im fiddle eingezeckt und das reine Join Statement eingetragen.

SELECT t1.id,
       t1.datum,
       t1.kwh as currentKWH,
       t2.kwh as priorKWH,
       t1.kwh - t2.kwh as diffKWH
  FROM sunpower t1 join sunpower t2
    on t1.datum = ADDTIME(t2.datum ,'0:05:00');

http://sqlfiddle.com/#!9/9a1dad/10

Das könnte wie erläutert nun eingeschränkt werden auf Zeitspanne und dann aggregiert nach Bedarf.
Ach und falls in den echten Tabellen das Datum sogar Primärschlüssel ist- das Feld also mit Index versehen ist-, könnte es sogar flott sein.

[photonensammler]

Vielen Dank für die Denkanstöße, ich werde mal die verschiedenen Möglichkeiten ausprobieren.

Übrigens... die MIN() Werte brauche ich später auch noch.
Bei bewölktem Himmel gibt es ja trotzdem PV-Erträge.
Ich stelle mir vor, dass die Differenz zwischen MAX() und MIN() mit den Meteomedia-Vorhersage-Sonnenstunden interpoliert wird und zu den MIN()-Werten addiert wird.
Damit könnte ich vielleicht dem realen Wert des aktuelen Tages sehr nahekommen.

Die Datensätze haben grundsätzlich 5 Minuten-Abstand.
Seit 1.8.2007 sind es bis heute ca. 544.000 Datensätze.

Auf eine besonders schnelle Ausführung der Abfragen kommt es nicht an. Die Berechnung würde zwischen Sonnenauf- und -untergang nur einmal stündlich erfolgen weil Meteomedia die Grafik erfahrungsgemäß einmal stündlich aktualisiert.

Gruß
Jörg

[protestix]

Habe es etwas spät abgeschickt.

Ich fürchte, diese iterierende Lösung aus PHP ist mit dem Order By auf 1 Mio Datensätze sehr ressourcenhungrig.

Habe das mal mit einer Testtabelle mit 550tsd Datensätzen getestet und mit einer Ausführungszeit von 0.6 Sekunden ging das eigentlich. Er braucht es ja wohl nur ein mal auszuführen, um den Peak zu ermitteln, danach kann er mit der Zahl ja weiter arbeiten, wenn ich das richtig verstanden habe.
Habe im Taskmanger(Windows), die Performance und den Speicherverbrauch beobachtet und da schien der Rechner nicht besonders beeindruckt.
Speicher keine Änderung, CPU ging mal kurz auf 37% hoch.

Ich gebe dir aber Recht, dass es nicht optimal ist, man damit jedoch leben kann, wenn man kein Postgres zur Verfügung hat.
Mehr als 600000 Datensätzen dürften in 11 Jahren wohl auch nicht zusammen gekommen sein.

[akretschmer]

Das Problem ist die bereits aggregierte Speicherweise, die für (Standard) SQL relativ schwierig zu handhaben ist.

Unter "Standard-SQL" verstehst Du offenbar SQL92, Window-Funktionen ist SQL 2003 - Standard. Systeme wie MySQL unter 8 sind also in der Datenbank-Welt etwa so alt wie Windows 3.11 in der Windows-Welt. Nur im das mal in das richtige Licht zu rücken.

[Perry Staltic]

Unter "Standard-SQL" verstehst Du offenbar SQL92, ..

Nein, da habe ich einn blöden Begriff gewählt.
Ich meinte eigentlich die ursprüngliche oder klassische Mengenlogik, die z.B. keinen Vorgänger oder Nachfolger (in der Menge) kennt.
Die ISO Standards sind für mich persönlich zweitrangig. Ich habe keinen Nerv, mich im Einzelnen damit auseinanderzusetzen, welcher Standard bzw. Version was definiert und welcher Hersteller dann wieviel davon implementiert plus was alles an "Eigenlösungen" dazu kommt, erst Recht nicht, mir das alles zu merken.

In diesem Fall hier ist es ja auch wurscht, da ja offenbar anhand der Datenlage ein direkter Join möglich ist. Zumindest sagt der TE, die Daten sind exakt und lückenlos (was nicht mal so wichtig wäre), was aber in dem Fall eineindeutige joins ermöglicht.

[photonensammler]

Da die MySql-Abfrage nicht so einfach wie gedacht ist, bin ich einen anderen Weg gegangen:
In der Tabelle mit den Daten habe ich eine weitere Spalte zugefügt, in die das Delta zum vorherigen Eintrag eingetragen wird.

Dadurch wird die Abfrage und das Handling der benötigten Daten bedeutend einfacher.

Vielen Dank für Eure Hilfe!

Gruß
Jörg

[Perry Staltic]

In der Tabelle mit den Daten habe ich eine weitere Spalte zugefügt, in die das Delta zum vorherigen Eintrag eingetragen wird.
Dadurch wird die Abfrage und das Handling der benötigten Daten bedeutend einfacher.

Mit der Abfrage in #5 bekommst Du genau das was Du möchtest- kumulierten Wert und Differenz- ohne redundante Datenhaltung mit den üblichen Stolperfallen (Inkonsistenz, Platzbedarf, ..)
Vielleicht ist es Dir nicht klar, aber Du kannst diese Abfrage genauso behandeln wie eine Tabelle.

[photonensammler]

Ja, ich werde mit Hilfe Eurer Vorschläge weitermachen, um die vorgeschlagen Abfragen umzusetzen.

Die Spalte habe ich erst einmal eingefügt, um eine schnelle Lösung zu bekommen und die Idee der Ertragsberechnung erst einmal umzusetzen.
Zusätzlich habe ich damit dann auch gleich noch einen Vergleich, ob ich die von Euch vorgeschlagenen Abfragen richtig verstanden und umgesetzt habe.

Wenn dann alles läuft, wird diese Spalte wieder entfernt.

Nochmals vielen Dank!

Gruß
Jörg