Daten aggregieren

[Gast2]

Hi,
ich schreibe Daten von verschiedenen Sensoren in bestimmten Abständen in eine Datenbanktabelle. Zusätzlich zu den Werten leg ich noch nen Timestamp dazu.

Die Tabelle sieht dann z.b. so aus:

Sensor | Wert | Timestamp
1      | 5    | 13:22:22,234
2      | 10   | 13:22:22,365
3      | 5    | 13:22:22,674
1      | 4    | 13:32:22,133
2      | 9    | 13:32:22,334
3      | 4    | 13:32:22,654

etc.

In diesem Beispiel speichere ich z.b. jede 10minuten die Daten ab. Nun möchte ich gerne alle Daten die zu einem Zeitpunkt gehören (Sekunden und Millisekunden kann ich vernachlässigen!) aufsummieren.

Als Ergebnis hätte ich also gerne folgendes:

Wert | Timestamp
20   | 13:22:22,xxx (<- ist egal welcher da steht)
17   | 13:32:22,xxx

Das ganze würd ich am liebsten direkt mit ner passenden SQL Abfrage machen, hat da jemand eine Idee, oder sollte ich lieber beim Abspeichern darauf achten dass zugehörige Werte den selben Timestamp bekommen?

 

[SlaterB]

wenn nicht passend gespeichert, dann musst du über X gruppieren,
wobei X eine Funktion ist die den Spaltenwert so umwandelt wie du es möchtest

direkt in SQL gibt es wohl nichts, was einen Timestamp umwandelt,
in MySQL gibts verschiedene Funktionen
MySQL :: MySQL 5.1 Reference Manual :: 11.7 Date and Time Functions

TIME_TO_SEC() klingt ganz passend,
allgemein kann DATE_FORMAT(date,format) wahrscheinlich einen String erstellen, der nur sekundengenau ist

 

[XHelp]

Warum benutzt du nicht für die Zeit ein Typ, welcher auch für die Zeit gedacht ist?

Und ein anderer Hinweis: ich weiß nicht was du mit den Daten anschließend machst, aber wenn du dein Programm 24h laufen lässt, ist es wieder 13:22:22 und somit ist die Statistik unbrauchbar. (sofern alte Datensätze erhalten bleiben)

 

[Gast2]

Die Spalte Timestamp ist vom Typ Timestamp, das Datum habe ich nicht extra dazugeschrieben, wollte mich aufs wesentliche beschränken 😉

@SlaterB
Danke da werd ich mich mal durchwühlen, ist sicherlich was für meine Zwecke dabei.
Das mit dem DATE_FORMAT klingt an sich schonmal gut, aber ich denke ich bekomme dann Probleme wenn ich z.b. folgende 2 Timestamps habe:
12:59:59,998 und 13:00:00,111
Die liegen nur 113ms auseinander, würden aber durch das date format wohl nicht getroffen werden.

 

[XHelp]

Also wenn du damit wie mit normalem Datum arbeiten kannst, dann kannst du dir doch mit >= und <= das nötige Intervall raussuchen.

 

[Gast2]

Mich interessiert aber nicht nur ein Zeitpunkt (sonst wäre es trivial 🙂 ), ich möchte die Werte aller "gleichen" Timestamps addieren.

 

[SlaterB]

aber ich denke ich bekomme dann Probleme wenn ich z.b. folgende 2 Timestamps habe:
12:59:59,998 und 13:00:00,111
Die liegen nur 113ms auseinander, würden aber durch das date format wohl nicht getroffen werden.

tja, welches Kriterium hast du denn?
'ganz doll dicht zusammenliegend'?

was ist mit
58,1
58,2
58,3
..
59,9
00,0
00,1
..
über 10 sec?
irgendwo musst du einen Schnitt machen

falls du nicht von 0,00 bis 0,99 sondern 0,5 bis 0,49 der nächsten Sekunde runden willst,
dann bietet sich an, auf den Zeitwert vor dem Format 0.5 sec draufzuaddieren
(frag mich jetzt aber nicht wie man das in SQL schreibt 😉 )

 

[Gast2]

ich speichere je nach einstellung alle 5, 10 oder 15minuten die daten ab.
Also wäre in dem fall "nahe beieinanderliegend" alles was so maximal 5-10sek auseinander liegt (in der regel sinds aber nur ein paar hundert millisekunden).

Ich kann halt nicht genau sagen wann die Daten in die Datenbank kommen, ob zur anfang einer Sekunde, mitten in einer Sekunde, oder wenns blöd läuft eben genau wenn ne Stunde umspringt 😉

 

[SlaterB]

ja, ein ärgerliches Kriterium, selbst wenn du auf ganze Minuten gruppierst kannst du genau in der Mitte eines Intervalls landen,
ich glaube nicht wirklich, dass SQL dafür Mittel hat, werde aber noch bisschen dazu nachdenken 😉

 

[XHelp]

Suche die größte Zeit raus, ziehe davon "so maxi al 5-10sek" ab und dann hast du dein intervall.

 

[Gast2]

Suche die größte Zeit raus, ziehe davon "so maxi al 5-10sek" ab und dann hast du dein intervall.

Aber wieder nur EIN Intervall 🙂 Oder versteh ich dich einfach falsch?

 

[Gast2]

Hm, die gute Nachricht, ich habe einen Weg gefunden das Problem zu lösen:

SELECT SUM(datensum.wert) AS sumwert, datensum.ts
FROM ( 
	SELECT wert, (SELECT MIN(d2.ts) FROM daten AS d2 WHERE d.ts BETWEEN DATE_SUB(d2.ts, INTERVAL 10 SECOND) AND DATE_ADD(d2.ts, INTERVAL 10 SECOND)) AS ts
        FROM daten AS d
     ) AS datensum
GROUP BY datensum.ts

Die schlechte: Das schaut furchtbar unperformant aus, was meint ihr? Zweites manko... ich verwende die H2 db, die kennt leider DATE_ADD und DATE_SUB nicht, wisst ihr ob ich die Rechnerei da anders lösen kann?

Die Idee dahinter:
Zu jedem Timestamp (ts) in der Datenbank suche ich mir den kleinsten ts im bereich +-10 sek. Danach kann ich alle gleichen Timestamps aufsummieren.
Der Query spuckt mir dann:

sumwert	ts
20	2010-07-09 19:39:18
17	2010-07-09 19:49:18

aus.

EDIT:
Habe einen Ersatz für H2 gefunden:

SELECT SUM(datensum.wert) AS sumwert, datensum.ts
FROM ( 
	SELECT wert, (SELECT MIN(d2.ts) FROM daten AS d2 WHERE d.ts BETWEEN DATEADD('second', -10, d2.ts) AND DATEADD('second', 10, d2.ts)) AS ts
        FROM daten AS d
     ) AS datensum
GROUP BY datensum.ts;

SUMWERT  	TS  
20	2010-07-09 19:39:18.499
17	2010-07-09 19:49:18.499

Aber falls noch jemand Verbesserungsvorschläge hat, nur raus damit 🙂