Peaks und Takte in Datenreihe per SQL ermitteln

[Heiko Baumann]

Hallo zusammen,

bislang hatte ich immer mit eher einfachen Queries zu tun.
Jetzt hab ich mir fï¿œr meine neue Heizung zuhause einen Datenlogger
installiert (volkszaehler.org), der in eine mysql-DB alle 60 Sekunden
diverse Temperaturdaten schreibt, unter anderem die Sole-Temperatur der
Wï¿œrmepumpe.

Schaltet sich die WP ein, sinkt die Soletemperatur drastisch ab und
stagniert dann irgendwann. Erst wenn der Arbeitstakt der WP zu Ende ist,
steigt sie wieder an.

Zur Verdeutlichung mal ein typisches Diagramm:
http://hbtest.kilu.de/rpi/zyklen-WP.png

Grafisch kann ich aus dem Temperaturverlauf problemlos die Zyklen
erkennen (siehe Abbildung) - jetzt frage ich mich (euch!), ob man das
auch per sql-Query direkt aus der Datenbank rausziehen kann.

Zyklusbeginn = (stark) sinkende Temp.werte fï¿œr >100 Sekunden
Zyklusende = ansteigende Temp.werte fï¿œr >100 Sekunden

Ich brauch also zwei Queries:
- eine Query, die mir idealerweise gruppiert nach Tagen (Stunden,
wochen, ...) die Anzahl der Zyklen ausgibt
- eine Query, die mir fï¿œr einen bestimmten Zeitraum die Detaildaten
jedes Zyklus (Anfang, Ende, Dauer) ausgibt.

Die wï¿œrde ich per Skript ausfï¿œhren und hï¿œtte eine schï¿œne ï¿œbersicht ï¿œber
die Laufzeit meiner WP (Hintergrund: Optimierung der Einstellung der
Heizkurve, Einschaltverzï¿œgerungen etc).

Hat jemand eine Idee oder gar Lï¿œsung dazu?

Tabellenstruktur ist billig:
mysql> describe data;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| channel_id | int(11) | YES | MUL | NULL | |
| timestamp | bigint(20) | NO | | NULL | |
| value | double | NO | | NULL | |
+------------+------------+------+-----+---------+----------------+

Auswertung soll fï¿œr channel_id=3 erfolgen.

Bin gespannt ob das so einfach geht...

Schon mal vielen Dank fï¿œr die Hilfe!
LG Heiko

[Dieter Nöth]

Heiko Baumann wrote:

> Grafisch kann ich aus dem Temperaturverlauf problemlos die Zyklen
> erkennen (siehe Abbildung) - jetzt frage ich mich (euch!), ob man das
> auch per sql-Query direkt aus der Datenbank rausziehen kann.
>

> Zyklusbeginn = (stark) sinkende Temp.werte für >100 Sekunden
> Zyklusende = ansteigende Temp.werte für >100 Sekunden

das ist eine der ganz wenigen Sachen bei denen ein Cursor mal nicht böse
und tatsächlich die beste Methode ist (wenn man nicht ein DBMS mit
OLAP-Funktionen oder eingebauter Zeitreihenanalyse hat).

Naürlich brauchst du erstmal eine MySQL-Version, die SPs mit Cursorn
unterstützt.
Ein DECLARE CURSOR in der richtigen zeitlichen Reihenfolge sortiert und
dann mit FETCH NEXT Zeile für Zeile über das Ergebnis gehen und jeweils
den aktuellen Wert mit dem vorherigen vergleichen, ob man einen
Zykluswechsel hat. Dauer berechnen und diesen Zeitpunkt in einer
Zwischentabelle abspeichern.

> Ich brauch also zwei Queries:
> - eine Query, die mir idealerweise gruppiert nach Tagen (Stunden,
> wochen, ...) die Anzahl der Zyklen ausgibt

> - eine Query, die mir für einen bestimmten Zeitraum die Detaildaten

> jedes Zyklus (Anfang, Ende, Dauer) ausgibt.

Damit hast du die Daten für Query #2 und die Grundlage für #1 :-)

Dieter

[Heiko Baumann]

Hallo Dieter,

Danke fï¿œr deine Antwort.

> > Zyklusbeginn = (stark) sinkende Temp.werte fï¿œr >100 Sekunden
> > Zyklusende = ansteigende Temp.werte fï¿œr >100 Sekunden
>
> das ist eine der ganz wenigen Sachen bei denen ein Cursor mal nicht bï¿œse
> und tatsï¿œchlich die beste Methode ist (wenn man nicht ein DBMS mit

> OLAP-Funktionen oder eingebauter Zeitreihenanalyse hat).
>

> Naï¿œrlich brauchst du erstmal eine MySQL-Version, die SPs mit Cursorn
> unterstï¿œtzt.


Ist ziemlich aktuell (5.5.28-1 (Debian)

> Ein DECLARE CURSOR in der richtigen zeitlichen Reihenfolge sortiert und

> dann mit FETCH NEXT Zeile fï¿œr Zeile ï¿œber das Ergebnis gehen und jeweils

> den aktuellen Wert mit dem vorherigen vergleichen, ob man einen
> Zykluswechsel hat. Dauer berechnen und diesen Zeitpunkt in einer
> Zwischentabelle abspeichern.

Ok, Idee ist klar. Das sieht dann nach einer stored procedure aus, oder
wie macht man das? Hatte bislang noch nicht den Bedarf fï¿œr sowas :)
Hast du einen Tipp wie ich da am besten einsteige?
Die Datenstruktur ist ja billigst (id, channel_id=3, timestamp, value).

>
> > Ich brauch also zwei Queries:
> > - eine Query, die mir idealerweise gruppiert nach Tagen (Stunden,
> > wochen, ...) die Anzahl der Zyklen ausgibt

> > - eine Query, die mir fï¿œr einen bestimmten Zeitraum die Detaildaten

> > jedes Zyklus (Anfang, Ende, Dauer) ausgibt.
>

> Damit hast du die Daten fï¿œr Query #2 und die Grundlage fï¿œr #1 :-)
>
Genau soweit war ich auch schon - wenn ich #2 hab, mï¿œsste ich zu #1
relativ leicht kommen.

Vielen Dank!

LG Heiko

[Bernd Nawothnig]

On 2013-03-23, Heiko Baumann wrote:
>> Ein DECLARE CURSOR in der richtigen zeitlichen Reihenfolge sortiert und

>> dann mit FETCH NEXT Zeile für Zeile über das Ergebnis gehen und jeweils

>> den aktuellen Wert mit dem vorherigen vergleichen, ob man einen
>> Zykluswechsel hat. Dauer berechnen und diesen Zeitpunkt in einer
>> Zwischentabelle abspeichern.
>
> Ok, Idee ist klar. Das sieht dann nach einer stored procedure aus, oder

> wie macht man das? Hatte bislang noch nicht den Bedarf für sowas :)

Dann bräuchtest Du aber eine prozedurale Programmiersprache auf dem
SQL-Server und sowas bietet MySQL nicht. Wenn Du auch da nur SQL zur
Verfügug hast, sehe ich da keinerlei Vorteil.

Mit PostgreSQL und einer etwa in PLPGSQL geschriebenen Funktion wäre
es serverseitig machbar, was die Datenmenge, die dann über die
Verbindung geschoben würde, deutlich verrimgern würde (Du würdest so
serverseitig filtern). So bleibt Dir wohl nur die Möglichkeit, den
Cursor client-seitig zu verwenden und damit die Datenbank auf ein
simples Datengrab zu reduzieren. Die gesamte Tabelle würde so durch
den Client geschaufelt werden.

Aber vielleicht habe ich ja auch irgendwas übersehen ...




Bernd

--
Die Antisemiten vergeben es den Juden nicht, dass die Juden Geist
haben - und Geld: der Antisemitismus, ein Name der
Schlechtweggekommenen. [Friedrich Nietzsche]

[Dieter Nöth]

Bernd Nawothnig wrote:

> Dann bräuchtest Du aber eine prozedurale Programmiersprache auf dem
> SQL-Server und sowas bietet MySQL nicht. Wenn Du auch da nur SQL zur
> Verfügug hast, sehe ich da keinerlei Vorteil.

Was sind denn dann Stored Procedures, wenn keine prozedurale
Programmiersprache?
Die bietet selbst MySQL inzwischen und da läuft ein Cursor (hoffentlich)
auf dem Server.

Sorry Bernd für die mail, ich hab falsch geklickt.

Dieter

[Dieter Nöth]

Heiko Baumann wrote:

> Ok, Idee ist klar. Das sieht dann nach einer stored procedure aus, oder

> wie macht man das? Hatte bislang noch nicht den Bedarf für sowas :)

Das meinte ich mit SP = Stored Procedure :-)

> Hast du einen Tipp wie ich da am besten einsteige?
> Die Datenstruktur ist ja billigst (id, channel_id=3, timestamp, value).

Hast du schon "normal" programmiert?
Überleg dir wie du da die Logik auf einer passend sortierten Menge über
eine WHILE-Schleife machen würdest.
Das kannst du dann übertragen, schau dir ein paar einfache Beispiele für
die Bearbeitung eines Cursors an.

Dieter

[Bernd Nawothnig]

On 2013-03-23, Dieter Nöth wrote:
>> Dann bräuchtest Du aber eine prozedurale Programmiersprache auf dem
>> SQL-Server und sowas bietet MySQL nicht. Wenn Du auch da nur SQL zur
>> Verfügug hast, sehe ich da keinerlei Vorteil.
>
> Was sind denn dann Stored Procedures, wenn keine prozedurale
> Programmiersprache?

Solange nur SQL erlaubt ist, ist die Sache nicht turing complete. Und
dem ist so:

http://stackoverflow.com/questions/6487236/mysql-procedural-languages

| The LANGUAGE characteristic indicates the language in which the
| routine is written. The server ignores this characteristic; only SQL
^^^^^^^^
| routines are supported.
^^^^^^^^^^^^^^^^^^^^^^

> Die bietet selbst MySQL inzwischen und da läuft ein Cursor (hoffentlich)
> auf dem Server.

Ja, aber weder ist Rekursion erlaubt

http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html
| Stored functions cannot be used recursively.

noch gibt es sowas wie Schleifen.

PLPGSQL oder Oracles PL/SQL sind hingegen turing complete, also echte
prozedurale Programmiersprachen mit Schleifen, die auch rekursiv
aufgerufen werden können. Darüber hinaus kannst Du unter PostgreSQL
auch Java, Perl, Python oder z.B. selbst R verwenden.

> Sorry Bernd für die mail, ich hab falsch geklickt.

Macht nichts.

[Dieter Nöth]

Bernd Nawothnig wrote:

>> Die bietet selbst MySQL inzwischen und da läuft ein Cursor (hoffentlich)
>> auf dem Server.
> Ja, aber weder ist Rekursion erlaubt

> noch gibt es sowas wie Schleifen.

Für das was der OP möchte braucht man beides nicht, sondern "nur" einen
Cursor. Und laut Manual kann MySQL REPEAT, WHILE, usw. :-)

Dieter

[Bernd Nawothnig]

Stimmt, dann zieh ich meinen Einwand zurück ;-)

[Heiko Baumann]

Ok, nachdem der Ausflug in die Welt der Theorie beendet ist, zurï¿œck zum
Thema :)

In einer "normalen" Programmiersprache wï¿œrde ich das irgendwie so probieren:

function getTakte(%startdatum, %enddatum)
{
hole alle Datensï¿œtze zwischen %startdatum und %enddatum sortiert nach
zeitstempel
temperatur_vorgaenger = temperaturwert des allerersten Datensatzes
durchlaufe alle Datensï¿œtze von 1 (nicht 0) bis ende
if(temperatur_vorgaenger > aktueller_temperaturwert*1,05)
{ // Temp. sinkt um mind. 5%, es beginnt ein neuer Arbeitstakt
beginn = aktueller_zeitstempel;
while (temperatur_vorgaenger <= aktueller_temperaturwert*1,05) {
next; // "Vorspulen"
}
ende = aktueller_zeitstempel; //Ende des Takts erreicht
return beginn, ende, (ende-beginn) as dauer
//Ergebnisse dieses Takts als Zeile ausgeben
} else {
next
}
}

Dann werd ich morgen mal mein Glï¿œck versuchen...

Danke!

Gute Nacht..
Heiko :)

Am 23.03.2013 19:08, schrieb Dieter Nï¿œth:
> Heiko Baumann wrote:
>
>> Ok, Idee ist klar. Das sieht dann nach einer stored procedure aus, oder

>> wie macht man das? Hatte bislang noch nicht den Bedarf fï¿œr sowas :)

>
> Das meinte ich mit SP = Stored Procedure :-)
>
>> Hast du einen Tipp wie ich da am besten einsteige?
>> Die Datenstruktur ist ja billigst (id, channel_id=3, timestamp, value).
>
> Hast du schon "normal" programmiert?

> ï¿œberleg dir wie du da die Logik auf einer passend sortierten Menge ï¿œber
> eine WHILE-Schleife machen wï¿œrdest.
> Das kannst du dann ï¿œbertragen, schau dir ein paar einfache Beispiele fï¿œr

[Thomas 'PointedEars' Lahn]

Dieter Nöth wrote:

> Bernd Nawothnig wrote:
>> Dann bräuchtest Du aber eine prozedurale Programmiersprache auf dem
>> SQL-Server und sowas bietet MySQL nicht. Wenn Du auch da nur SQL zur
>> Verfügug hast, sehe ich da keinerlei Vorteil.
>
> Was sind denn dann Stored Procedures, wenn keine prozedurale
> Programmiersprache?

> Die bietet selbst MySQL inzwischen […]

Aber erst seit 8 Jahren (MySQL 5.0). Das kann Bernd unmöglich wissen :->

<http://dev.mysql.com/doc/refman/5.0/en/stored-programs-views.html>
<http://dev.mysql.com/doc/refman/5.0/en/development-history.html>

--
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[Peter J. Holzer]

On 2013-03-23 18:35, Bernd Nawothnig <Bernd.N...@t-online.de> wrote:
> On 2013-03-23, Dieter N�th wrote:
>>> Dann br�uchtest Du aber eine prozedurale Programmiersprache auf dem

>>> SQL-Server und sowas bietet MySQL nicht. Wenn Du auch da nur SQL zur

>>> Verf�gug hast, sehe ich da keinerlei Vorteil.

>>
>> Was sind denn dann Stored Procedures, wenn keine prozedurale
>> Programmiersprache?
>
> Solange nur SQL erlaubt ist, ist die Sache nicht turing complete. Und
> dem ist so:
>
> http://stackoverflow.com/questions/6487236/mysql-procedural-languages

[...]

> Ja, aber weder ist Rekursion erlaubt
>
> http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html
>| Stored functions cannot be used recursively.
>
> noch gibt es sowas wie Schleifen.

Vielleicht solltest Du Dich weniger auf Stackoverflow als auf das Manual
verlassen. Bereits das allererste Beispiel im Manual von MySQL 5.0
enth�lt eine Schleife:

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

-- <http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html>

hp


--
_ | Peter J. Holzer | Fluch der elektronischen Textverarbeitung:
|_|_) | Sysadmin WSR | Man feilt solange an seinen Text um, bis
| | | h...@hjp.at | die Satzbestandteile des Satzes nicht mehr
__/ | http://www.hjp.at/ | zusammenpa�t. -- Ralph Babel

[Bernd Nawothnig]

On 2013-03-24, Peter J. Holzer wrote:
>> http://stackoverflow.com/questions/6487236/mysql-procedural-languages
> [...]
>> Ja, aber weder ist Rekursion erlaubt
>>
>> http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html
>>| Stored functions cannot be used recursively.
>>
>> noch gibt es sowas wie Schleifen.
>
> Vielleicht solltest Du Dich weniger auf Stackoverflow

Nun ja, da stand nur, dass lediglich SQL als Sprache zulässig ist, was
aber ja wohl stimmt, weil MySQL den Sprachumfang um diese Sachen
erweitert hat und das Resultat immer noch "SQL" heißt (was bei anderen
Datenbanken so nicht gehandhabt wird).

> als auf das Manual verlassen. Bereits das allererste Beispiel im

> Manual von MySQL 5.0 enthält eine Schleife:

>
> CREATE PROCEDURE dorepeat(p1 INT)
> BEGIN
> SET @x = 0;
> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
> END;

Ich weiß es ja nun inzwischen, dass sowas geht ;-)

[Dieter Nöth]

Bernd Nawothnig wrote:
> Nun ja, da stand nur, dass lediglich SQL als Sprache zulässig ist, was
> aber ja wohl stimmt, weil MySQL den Sprachumfang um diese Sachen
> erweitert hat und das Resultat immer noch "SQL" heißt (was bei anderen
> Datenbanken so nicht gehandhabt wird).

Das kommt aus dem Standard SQL, da gibt's ein LANGUAGE und das kann zum
Beispiel auch C oder Java sein (wenn das DBMS das unterstützt).
Bei LANGUAGE SQL ist es die im Standard definierte Syntax und der kennt
natürlich Schleifen, etc.

Dieter

[Bernd Nawothnig]

Reines SQL-92 kennt das z.B. nicht, erst mit der Erweiterung SQL/PSM.
Also soooo natürlich ist das keineswegs, denn MySQL implementiert ja
noch nicht mal SQL-92 komplett ;-)

[Axel Schwenke]

Heiko Baumann <hb...@gmx.de> wrote:

>> das ist eine der ganz wenigen Sachen bei denen ein Cursor mal nicht b�se
>> und tats�chlich die beste Methode ist (wenn man nicht ein DBMS mit

>> OLAP-Funktionen oder eingebauter Zeitreihenanalyse hat).
>

>> Ein DECLARE CURSOR in der richtigen zeitlichen Reihenfolge sortiert und

>> dann mit FETCH NEXT Zeile f�r Zeile �ber das Ergebnis gehen und jeweils

>> den aktuellen Wert mit dem vorherigen vergleichen, ob man einen
>> Zykluswechsel hat. Dauer berechnen und diesen Zeitpunkt in einer
>> Zwischentabelle abspeichern.

Daf�r braucht man mit MySQL weder Stored Procedures noch Cursor.
Man hat ja User-Variablen:

http://dev.mysql.com/doc/refman/5.5/en/user-variables.html


Konkret:

-- Tabelle erzeugen und mit Testdaten f�llen
create table t1 (c1 serial, c2 double);
insert into t1 (c2) values (1), (2), (3), (3), (2), (2), (3), (1);

-- zur Probe mal ansehen, c1=Zeit, c2=Me�wert
select * from t1 order by c1;

-- Variable f�r Vergleichswert intitialisieren
select @x:=c2 from t1 order by c1 limit 1;

-- Abfrage mit Zyklenanzeige
select if(@x > c2, "start", "") as zyklus, @x:=c2 as c2 from t1 order by c1;


XL

[Heiko Baumann]

Am 24.03.2013 14:51, schrieb Axel Schwenke:

> Daf�r braucht man mit MySQL weder Stored Procedures noch Cursor.
> Man hat ja User-Variablen:
>
> http://dev.mysql.com/doc/refman/5.5/en/user-variables.html
>
>
> Konkret:
>
> -- Tabelle erzeugen und mit Testdaten f�llen
> create table t1 (c1 serial, c2 double);
> insert into t1 (c2) values (1), (2), (3), (3), (2), (2), (3), (1);
>
> -- zur Probe mal ansehen, c1=Zeit, c2=Me�wert
> select * from t1 order by c1;
>
> -- Variable f�r Vergleichswert intitialisieren
> select @x:=c2 from t1 order by c1 limit 1;
>
> -- Abfrage mit Zyklenanzeige
> select if(@x > c2, "start", "") as zyklus, @x:=c2 as c2 from t1 order by c1;
>
>
> XL
>

Hi Axel,
Danke! Schon mal ein Anfang ohne SP, Danke f�r den code, ist mir alles
neu - aber interessant was alles geht :)

Nur: in jedem Pausenzyklus gehen die Temperaturen eine ganze Zeit lang
rauf, im Arbeitszyklus runter. Ich brauch in der Query also nicht JEDE
Stelle, an der sie absinkt bzw. ansteigt, sondern nur die, an denen ein
"Vorzeichenwechsel" (von steigend auf fallend -> Beginn Arbeitstakt bzw.
von fallend auf steigend -> Ende Arbeitstakt) stattfindet.

Wie mach ich das? Bau ich noch eine zweite Variable f�r den "vorletzten"
Datensatz und sag "Start nur dann, wenn beide > aktuellem Wert sind? �h,
neee, schmarrn - hab ja nicht nur 2 Werte pro Zyklus, sondern unbekannt
viele. Ich muss also doch irgendwie den Wechsel erkennen.. hmmm.

Hast du da noch ne Idee?

Vielen Dank f�rs konstruktive Mitdenken!
LG Heiko

[Heiko Baumann]

Am 24.03.2013 16:29, schrieb Heiko Baumann:
>
> Nur: in jedem Pausenzyklus gehen die Temperaturen eine ganze Zeit lang
> rauf, im Arbeitszyklus runter. Ich brauch in der Query also nicht JEDE
> Stelle, an der sie absinkt bzw. ansteigt, sondern nur die, an denen ein
> "Vorzeichenwechsel" (von steigend auf fallend -> Beginn Arbeitstakt bzw.
> von fallend auf steigend -> Ende Arbeitstakt) stattfindet.
>
> Wie mach ich das? Bau ich noch eine zweite Variable f�r den "vorletzten"
> Datensatz und sag "Start nur dann, wenn beide > aktuellem Wert sind? �h,
> neee, schmarrn - hab ja nicht nur 2 Werte pro Zyklus, sondern unbekannt
> viele. Ich muss also doch irgendwie den Wechsel erkennen.. hmmm.
>

Ich hatte eine: ich erzeuge zwei Variablen f�r die Werte des Vorg�ngers
(@x1)und Vor-Vorg�ngers (@x2).
Aus diesen beiden errechne ich die "Tendenz" der Vorg�nger des aktuellen
Werts und lege das in @x3 ab (>0 bedeutet steigend, <0 fallend).
Damit hab ich eigentlich alles f�r die korrekte Auswertung.

Sieht dann so aus:
//letzter Wert
select @x1:=value from data where channel_id=3 order by id limit 1;
// vorletzter Wert
select @x2:=value from data where channel_id=3 order by id limit 1,1;
// Tendenz aus letztem und vorletztem Wert (sinkend = -1, steigend = 1)
select @x3:=if(@x2>@x1,-1, 1) ;
select @t:= 1; // Parameter f�r Messungenauigkeit

Abfrage:
select id, value, @x1,@x2,@x3,
if((value*@t < (@x1) AND @x3 = 1), "start", if((@x1*@t < value AND @x3
=-1), "ende", "")) as zyklus,
@x2:=@x1 as value2,
@x1:=value as value1,
@x3:=if(@x2>=@x1,-1, 1) as tendenz,
from_unixtime(timestamp/1000) as zeit from data where channel_id=3
order by id limit 1000 ;

�berraschenderweise klappt das sogar. Zumindest einigerma�en, da ist
wohl noch etwas Feintuning n�tig, dass die Sonderf�lle (Gleichheit von
Werten, sofortiges "Umschalten"...), aber f�r den Anfang ists ok.

Ich bekomm damit zuverl�ssig ein "start" und "Ende" in die Spalte zyklus.

Dachte das w�r dann damit gel�st, aber jetzt steh ich beim Ausz�hlen auf
dem Schlauch. Wie bekomm ich aus diesen Daten ne vern�nftige Auswertung
(#takt, #anfang, #ende, #dauer), gruppiert nach Stunde/Tag/Monat/Jahr,
und schlie�lich die Gesamtanzahl/-dauer aller Takte?