Verdichtungstabellen für Messwertarchiv erstellen

[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


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]

Zitat von 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.

AFAIK geht das auch gemischt, bzw. so kombiniert

[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

[jobo]

Schöne Lösung von Sir Rufo!

Noch ein paar Anmerkungen hierzu, eher aus Neugier:

Zitat von Medium:

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.

Falls die verschiedenen AggrTypen in einer Tabelle landen sollten und Dritte auf die Messdaten zugreifen dürfen, empfiehlt sich vielleicht zum Auslesen eine Kapselung in versch. Views je AggTyp. (Wenn überhaupt direkt auf dem Prodsystem gelesen/ausgewertet werden darf)
Ansonsten eher zum Ablauf:
Was ist mit Messlücken? "Band steht" oder sowas.
Macht es Sinn, dann das Intervall fortzuschreiben mit 0 Werten? ("Nachträgliche Pflege ist keine Option"). Das erspart evtl. aufwendige Statements, falls chronologische Daten benötigt werden. Ist aber sicher mit Vorsicht zu genießen und hängt aber stark vom Einsatz/Weiterverwendung der Messdaten ab.
Was ist mit Messfehlern (Ausreißer, Sensorausfälle, ..)?
Falls das System irgendwie im Bereich MES, Traceability .. (und danach sieht es ja etwas aus) eingesetzt wird, wäre zu überlegen, wie man da anknüpft. Sollwerterfassung, Toleranzvorgaben, NOK Flag usw.
Naja, Aggregat Werte widersprechen dem eigentlich oder das wird sowieso separat abgefackelt oder ist unwichtig. Also das Ganze nur als Anmerkung.