Verdichtungstabellen für Messwertarchiv erstellen

[Medium]

Huhu DP!

Ich habe gerade ein recht spannendes Problem. Ich erstelle ein Messwertarchiv, in dem diverse Werte mit ihrem Messzeitpunkt gespeichert werden. Jeder Wert gehört zu einer eindeutigen Messstelle, und jede Messstelle kann ein anderes Messintervall aufweisen. (Heisst: In der Tabelle sind wild sekündliche, minütliche und andere Intervalle vorhanden. Aber alle mit vollem Datum+Uhrzeit.)
Alle Messwerte landen zunächst in dieser großen "history" Tabelle mit folgendem Aufbau:

Code:

messID, messDatum, messWertAVG, messWertMAX

(messWertMAX macht hier noch keinen Sinn, gleich aber!)
Ein Index ist auf "messDatum, messID". messID ist ein Fremdschlüssel in meine Messstellenliste, die in einer anderen Tabelle liegt. Aufbau:

Code:

ID, messName, gatewayID, adresse

(die letzten zwei Felder sind für meine Modbus-Gateways, die die Werte liefern. Für das Problem hier unerheblich.)

Jetzt möchte ich zusätzliche Tabellen mit genau demselben Aufbau wie die "history" (wichtig!) haben, in denen aber dann die Durchschnittswerte und Maxima von Zeiträumen stehen. Also eine Tabelle für alle Werte minütlich, eine stündlich, eine tageweise - hier möchte ich auch gerne die Flexibilität haben beliebig viele Verdichtungstabellen mit frei wählbaren Intervallen erstellen zu können.
Die Tabellen sollen live, während neue Messungen kommen mitgeführt werden. Ein nachträglicher Pflegelauf ist keine Option!!

Die Krux dabei ist: Ich will, dass in den Verdichtungstabellen die Messzeiten genau auf glatte Grenzen fallen. Wenn ich also eine stündliche Tabelle habe, sollen dort nur 07:00:00, 13:00:00, usw. stehen. Bei Minütlich entsprechend 07:14:00, 13:41:00, usw.
Im Grunde also so, dass das Intervall immer ab einer Tagesgrenze gezählt wird. (Tagesintervall ist gleichzeitig auch das längste, das ich haben muss. Sekundenintervall ist das kleinste, welches in der Basistabelle vorkommt.)

Bisher mache ich das so, dass ich nach jedem Mal einen Messwert eintragen für alle Verdichtungen dies Mache:
a) Einen Zeitraum der Länge des gewünschten Intervalls suchen, für den "jetzt" so gerade in der Zukunft liegt, und dessen Startzeitpunkt dem o.g. Raster gehorcht. (Ich warte also immer auf fertige Invervall-Päckchen relativ zum Intervall in der Zieltabelle - pro messID.)
b) Ist kein Eintrag für diesen Zeitraum und messID vorhanden?
c) Gibt es für den Zeitraum und diese messID Werte in der Basistabelle?
Wenn b und c zutreffen, wird per SQL Statement ein entsprechend aggregierter Datensatz in meine Zieltabelle geschrieben, wo der Durchschnitt und das Maximum aus dem jeweiligen Messzeitraum der Basistabelle steht.

Leider ist dies VIEL zu langsam wenn man bedenkt, dass ich am Ende gut 400 Messstellen, größtenteils sekündlich erfasse. Zu viele Abfragen über potenziell riesige Datenmengen.

Ich bin hier offen für beides: Grundlegende Vorgehensweisen, aber auch konkrete Vorschläge.

Wem das Hirn nach Lesen meiner Ausführungen genau so verknotet ist wie meines, der soll bitte unbedingt noch Mal nachfragen! Ich habe mir zwar Mühe gegeben das so kompakt aber dennoch allgemeinverständlich zu schreiben wie es mir gerade möglich war, aber das ist ja noch lange keine Garantie :)

Danke euch schon mal!

[Sir Rufo]

Dieses "beliebig viele" kann natürlich den Server zum Kochen bringen.

Grundsätzlich ist das aber vom Prinzip kein Problem. Aus dem Zeitpunkt erstellst du dir den Intervall-Zeitpunkt.

Code:

2015-08-08 13:16:54
s 2015-08-08 13:16:54
m 2015-08-08 13:16:00
15m 2015-08-08 13:15:00
h 2015-08-08 13:00:00
d 2015-08-08 00:00:00

und trägst den Wert (am Besten per SP) in die Tabelle ein. Die Tabelle benötigt noch einen zusätzlichen Wert, mit der Anzahl der kumulierten Datansätze.

Den neuen Durchschnittswert errechnest du mit

Code:

( TabAnzahl * TabDurchschnitt + NeuDurchschnitt ) / ( TabAnzahl + 1 )

[Medium]

Hmmmmmm. Ich muss gestehen: Einfach aber gar nicht so übel. Ich war bisher in dem Gedanken verhaftet, dass es viel besser sein muss das "in bulk" zu erledigen, so dass z.B. alle messIDs gleich in einem Statement verarbeitet werden. Aber wenn man das so aufzieht, kann das sogar ganz gut klappen! Ich werde das morgen mal probieren. Vor allem gefällt mir, dass dieser Weg die Komplexität aus den SQL Statements raus nimmt.

Muss mir nur noch überlegen, wie ich eine SP schreibe (selten gemacht bisher), die entweder einen Kummulier-Datensatz anlegt wenn es für den Zeitpunkt noch keinen gibt, oder eben kummuliert wenn er schon da ist. Die Abfrage nach der Existenz seitens meines Programms würde ich mir nämlich gerne sparen. Dann ist das pro Wert, pro Verdichtungstabelle, nur ein Statement. Das wäre fein.

Danke dir! Manchmal muss man in die Eleganz der Simplizität zurück gerufen werden :D


Edit: "Beliebig viele" heisst in dem Fall übrigens das, was du im Grunde schon gezeigt hast. Mehr als 6-8 werden das nicht. Ich möchte ganz gerne mit verschiedenen Granularitäten herumspielen können, da das auswertende Gegenstück eine eigene Chart-Komponente ist, die dynamisch abhängig vom "Zoom" eine geeignete Verdichtungstabelle als Basis wählen soll. Ich will damit erreichen, dass man flotti Übersichten über Monate und Jahre haben kann, aber dennoch übergangsfrei von da aus bis auf sekundengenau hineinzoomen kann. Das ist die eigentliche Motivation die dahinter steckt.
Derzeit scheint sich das hier ganz gut anzustellen: 1s, 10s, 30s, 1min, 1h, 1t, 1mon. Feiner oder mehr muss nicht. Monatlich ist eigentlich schon fast über, keiner wird sich >5 Jahre am Stück angucken müssen, und selbst dann ist die Anzahl noch überschaubar bei täglich.

[Sir Rufo]

So sieht so ein Statement aus

SQL-Code:

CREATE TABLE mw
    (`messID` INT NOT NULL , 
     `messAggID` INT NOT NULL,
     `messDatum` DATETIME NOT NULL, 
     `messWertAVG` FLOAT , 
     `messWertMAX` FLOAT, 
     `messWertANZ` INT DEFAULT 1, 
     PRIMARY KEY (messID, messAggID, messDatum) );
    
INSERT INTO mw
    (`messID`, `messAggID`, `messDatum`, `messWertAVG`, `messWertMAX`)
VALUES
    (1,1,'2015-08-08 13:15:00',5,6)
ON DUPLICATE KEY UPDATE
  messWertAVG = ( messWertAVG * messWertANZ + 5 ) / ( messWertANZ + 1 ),
  messWertMAX = IF( messWertMAX > 6, messWertMAX, 6 ),
  messWertANZ = messWertANZ +1
;

INSERT INTO mw
    (`messID`, `messAggID`, `messDatum`, `messWertAVG`, `messWertMAX`)
VALUES
    (1,1,'2015-08-08 13:15:00',6,7)
ON DUPLICATE KEY UPDATE
  messWertAVG = ( messWertAVG * messWertANZ + 6 ) / ( messWertANZ + 1 ),
  messWertMAX = IF( messWertMAX > 7, messWertMAX, 7 ),
  messWertANZ = messWertANZ +1
;

Eigentlich brauchst du gar keine SP, die Statistik kannst du über ein Statement mit Parametern jagen. Alles in einer Transaktion erledigen und gut ist.

[Uwe Raabe]

Nur um sicher zu gehen, daß ich das richtig verstanden habe:

Die einzelnen Messwerte und die kumulierten Werte werden alle in dieselbe Tabelle eingetragen und die Abfrage für eine bestimmte Granularität erfolgt dann über die Selektion nach messAggID?

Wenn nicht, was ist dann messAggID?

[Medium]

Wow, danke! Wenn ich nicht so müde wäre, würde ich am liebsten direkt ins Büro rüber und das rund machen!

@Uwe: Ich vermute, dass Sir Rufo das so gemeint hat. Ginge rein theoretisch von meinen Randbedingungen her auch. Ich vermute aber, dass es am Ende etwas performanter kommt, wenn ich doch für alle Aggregat-Stufen separate Tabellen nehme, wie ich es jetzt schon mache. Vielleicht findet sich am Wochenende etwas Zeit das genauer zu messen.
Leider dauert das Erzeugen von Testdaten hier naturgemäß relativ lange. Sekündliche Werte für ein ganzes Jahr, für mind. 3 Messstellen eintragen - da jodelt die DB ganz schön. Da das aber nachher ein System für den Einsatz auf mind. 5-10 Jahre gedacht ist (in der Industrie gibt's sowas ja noch), lohnt sich jeder vorab gemachte Gedanke und Test. Zumal das ganze auch noch relevant für diverse Abrechnungen wird, und als Basis für Produktpreise dienen soll.

[Medium]

Eine andere Sache am Rande noch: Wenn ich einen Datumswert in mein Statement gebe, würde ich das natürlich am liebsten mit einem Parameter. Der wird als TDateTime (=Double) entgegengenommen. In der Datenbank kann ich zwar nur bis auf eine Sekunde genau, aber wie sicher ist es, dass mir ein Unique-Index mit dem Datumsfeld zuschlägt obgleich ich da mit einem wackeligen Float rein gehe? Sollte ich da doch lieber mit einem FormatDateTime() vorab in einen String umwandeln, oder ist das unnötig?

[Sir Rufo]

Du brauchst keine separaten Tabellen.

Ja diese messAggID ist für den AggregatTypen und für jeden dieser Typen erstellst du eine Partition. Von der Performance ist das genauso, als ob du unterschiedliche Tabellen pro AggregatTypen hättest, nur die Abfrage ist komfortabler, weil es eine logische Tabelle ist.

[Medium]

Partitionen wollte ich bereits für Zeitabschnitte einrichten. Vermutlich Jährlich. Ich vermute mal ganz stark, dass das nicht mischbar ist dann. Ansonsten natürlich völlig richtig.

[Sir Rufo]

Mit MySQL kannst du auch genauere Zeitwerte ablegen
http://dev.mysql.com/doc/refman/5.6/...l-seconds.html

Aber so eine Umwandlung ist nicht notwendig