Verdichtungstabellen für Messwertarchiv erstellen

[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

[Medium]

Genauer will ich ja gar nicht. Im Gegenteil - Auf Sekunden begrenzt zu sein kommt mir hier eher entgegen! Die Partitionierung probiere ich einfach mal aus. MySQL wird vermutlich schon was sagen, wenn das nicht geht. Danke dir für deine geniale Hilfe. Morgen wird mächtig getestet!

[Dejan Vu]

Wurde schon erwähnt, das Du deinen Interval-Wert durch eine einfache Rechnung hinbekommst? Dazu nimmst du die Zeit als Double/Float und rundest sie gemäß der Formel (trunc(T/I)+0.5)*I (I=Interval, T=Zeit).

Wenn Du dann dazu noch deine Intervalle in einer kleinen separaten Tabelle hast, kannst du (vermutlich) alle Intervalle in einem Rutsch ablegen. Leider kenne ich mich nicht mit mySQL aus, aber zumindest *anzeigen* würde so gehen. (Sei 'Interval' eine Tabelle mit einer Spalte 'Interval' in dem die Float-Werte für deine Intervalle stehen, also 1/86400 für '1 sec', '1/1440' für '1 min', '15/1440' für '15 min' etc. Du kannst auch noch eine 'Description'-Spalte mit aufnehmen.

SQL-Code:

select :messID as messID, 
       cast(cast(:messDatum as Float)/i.interval)+0.5)*i.interval) as DateTime) as messDatum,
       IF (h.messID is null, :messwert, (messWertAVG *messWertANZ + :messwert) / (messWertANZ + 1) as messWertAVG,
       IF (h.messID is null, :messwert, IF(messWertMAX > :messwert, messWertMAX, :messwert) as messWertMAX
       IF (h.messID is null, 1, messWertANZ + 1) as messWertANZ
  from interval i 
       full join history h
  where h.messDatum = cast(cast(:messDatum as Float)/i.interval)+0.5)*i.interval) as DateTime)
    and h.messID = :messID

So zumindest die Idee. Im o.g. Query sind bestimmt mindestens Tippfehler drin.

Für jeden Eintrag in der Interval-Tabelle erzeugst Du eine Zeile in dieser Anzeige (Tabelle).
1. Spalte: messID (Parameter aus deiner Software)
2. Spalte: Berechnetes Interval-Datum
3. Spalte: Wenn es den Eintrag in History schon gibt, dann den überarbeiteten AVG-Wert, sonst den Messwert (der ist da der erste im neuen Interval)
4. Spalte: Ditto, nur für MAX-Wert
5. Spalte: Ditto, nur für ANZ-Wert

Wenn Du es schaffst, diese Tabelle (also dieses SELECT) in einem MERGE bzw. INSERT OR UPDATE - Befehl in deine History-Tabelle zu speichern, dann reicht dieser eine Befehl aus, um beliebig viele Intervalle abzulegen. Vorteil: Du kannst die Interval-Tabelle beliebig erweitern.

Da nur mit den PK-Indexen gearbeitet wird, sollte das auch sehr schnell gehen.

Ich persönlich würde der Interval-Tabelle noch eine Spalte 'ID' als PK spendieren und diese ID in der History-Tabelle mit ablegen. So bekommst Du einfach z.B. alle '15 min' Intervalle in einem einfachen Statement (woher willst Du sonst wissen, zu welchem Intervall welcher Eintrag gehört?).

SQL-Code:

select h.* 
  from History h 
       join Intervalle I on h.IntervalID = I.ID 
 where I.Description = '15 min'

Ach, ich sehe gerade: Du willst pro Interval eine eigene Tabelle haben. Na gut. Wie Du willst. Dann brauchst Du natürlich pro Tabelle ein eigenes INSERT und kannst Dir das hier alles sparen...

Ich erwähne es trotzdem, denn ich finde das flexibler.

[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.

[Medium]

Zitat von Dejan Vu:

Wurde schon erwähnt, das Du deinen Interval-Wert durch eine einfache Rechnung hinbekommst? Dazu nimmst du die Zeit als Double/Float und rundest sie gemäß der Formel (trunc(T/I)+0.5)*I (I=Interval, T=Zeit).

Mensch, da war ich gerade so stolz darauf, dass ich mir beim Zähneputzen vorhin eine Lösung mit Unix-Timestamps und Modulo dafür erdacht habe, und dann kommst du mit so einer schlanken Formel daher (Danke!)

Zitat:

Wenn Du dann dazu noch deine Intervalle in einer kleinen separaten Tabelle hast, kannst du (vermutlich) alle Intervalle in einem Rutsch ablegen.

Der Gedanke kam mir gestern Abend auch noch. Das hat den gewaltigen Vorteil, dass man zum Ändern der Granularitäten nichtmals mehr am Source basteln muss, und alles hübsch dynamisch über die DB passiert.
Die Idee alle Werte in eine Tabelle zu packen wird mir dank deiner eleganten Idee hier immer sympathischer. Ich werde gleich mal nachsehen, ob ich nach zwei Kriterien partitionieren kann. Wenn ja, dann ist die Sache geritzt.


@jobo:
Bisher werden Ausfälle schlicht zu Lücken in den Daten. Das werde ich wohl auch so beibehalten, da dann ganz klar ersichtlich ist, dass hier keine gültigen Werte vorliegen. Glücklicherweise steht keine Zertifizierung dahinter, so dass zumindest keine "harten" Richtlinien existieren.
Konkret geht es darum, dass ein großer Kunde von uns alle seine Betriebsbereiche (und teils auch einzelne Motoren) mit Strommessgeräten ausstatten will. Dadurch soll zum einen der Gesamtverbrauch im Werk so optimiert werden, dass teure Bezugsspitzen durch besseres "Scheduling" vermieden werden, und die Energiekosten pro Produkt pro Zeit sollen ermittelt werden um den Produkten diese entsprechend anteilig im Preis zuordnen zu können. Bisher wird das bloß geschätzt. Auch soll es dem Energielieferanten gegenüber ggf. als Indiz dienen, falls dieser stark abweichende Rechnungen stellt. Die Verbrauchssummen werden netterweise schon von den Messgeräten gebildet, so dass ich da nicht auf Vollständigkeit der Integrale unter meinen Daten achten muss.
Zugriff von aussen steht bisher nicht auf dem Plan, aber dafür dann entsprechende Views zu machen wäre sicherlich angebracht.


Danke euch beiden auch nochmal!

[jobo]

Zitat von Medium:

Bisher werden Ausfälle schlicht zu Lücken in den Daten. Das werde ich wohl auch so beibehalten, da dann ganz klar ersichtlich ist, dass hier keine gültigen Werte vorliegen.

Ok, es geht also eher um Prozessüberwachung im Ganzen bzw. interne Messgrößen, kein Fertigungsmesswerte. Ob die Lücken klar erkennbar sind, hängt dennoch aber m.E. stark von den Intervallgrößen und den Amplituden ab. Ob also letztlich die Straße beginnt Strom zu sparen oder mehr und mehr Messwerte ausfallen, ist glaub ich nicht immer so ganz klar ersichtlich (an diesen Daten). Aber wahrscheinlich gibt es da bezüglich der Sensoren auch separate Kanäle für Fehlerereignisse.

Ich würde aber mal prophezeien, dass wenn der Kunde Blut geleckt hat (und das kann ich mir hier sehr gut vorstellen), ganz viele Fragen zu Messintervallinhalten auftauchen, besonders wenn sie nicht nur zeitlich kumuliert sind, sondern auch unspezifisch bezogen auf den/die Verbraucher sind. Wie auch immer, das kommt dann im Upgrade. Finde ich spannend sowas (klar, Strom)

[jobo]

Zitat von Dejan Vu:

Wurde schon erwähnt,

Ich versteh nicht ganz, wo hier im Kern der Unterschied zu Sir Rufo sein soll. Oder ich verstehe den Vorschlag selbst nicht. Die Intervallberechnung hat er in seinem ersten Beitrag anhand der Ergebnisse dargestellt. Die, so hab ich das verstanden, erfolgt im Client, der dann gemäß Ergebnis zuschlägt, also on demand.

Den Intervalltyp hat er ebenfalls definiert. Kann also auch alles in eine Tabelle. Und wenn man I=14 sek. statt 15 benötigt, sagt man das halt dem Client.

Problem:
Wenn ich "fertige" Intervalle (nicht nur den Typ) in der DB ablege, muss ich das ja irgendwie kontinuierlich oder Bulkartig, jedenfalls rechtzeitig, genügend Werte für die Zukunft anlegen, oder? Der Client widerum muss dann immer (rechtzeitig) nach vordefinierten, ggF. sich ändernden Intervallen in der DB schauen.

[Uwe Raabe]

Zitat von jobo:

Wenn ich "fertige" Intervalle (nicht nur den Typ) in der DB ablege, muss ich das ja irgendwie kontinuierlich oder Bulkartig, jedenfalls rechtzeitig, genügend Werte für die Zukunft anlegen, oder?

Das ist mir noch nicht ganz klar. Du willst also schon Datensätze für alle Intervalle oder einen bestimmten Zeitraum im Voraus anlegen, in denen aber die Anzahl der verrechneten Messungen gleich 0 ist? Kannst du kurz erläutern, was du damit bezwecken willst?

[jobo]

Zitat von Uwe Raabe:

Kannst du kurz erläutern, was du damit bezwecken willst?

Uwe, meinst Du mich? Ich will das nicht, ich frage, ob ich das richtig verstanden habe.

[frankyboy1974]

Hallo,

das Problem ist

Zitat:

Konkret geht es darum, dass ein großer Kunde von uns alle seine Betriebsbereiche (und teils auch einzelne Motoren) mit Strommessgeräten ausstatten will. Dadurch soll zum einen der Gesamtverbrauch im Werk so optimiert werden, dass teure Bezugsspitzen durch besseres "Scheduling" vermieden werden, und die Energiekosten pro Produkt pro Zeit sollen ermittelt werden um den Produkten diese entsprechend anteilig im Preis zuordnen zu können.

Dafür nun eine Lösung pauschal anzubieten, übersteigt zumindestens meine Möglichkeiten.

Du optimierst das Scheduling nach t, damit opt(BK) =besser ist; ansonsten bestimmst du den Gradient und optimierst weiter.

mfg

frank