Verdichtungstabellen für Messwertarchiv erstellen

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

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

[Uwe Raabe]

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

Sorry, dann hatte ich das falsch zugeordnet.

[Sir Rufo]

Sehe ich eigentlich nicht so mit dem Wust

SQL-Code:

CREATE TABLE messInt ( IntID INT, IntSec INT );

INSERT INTO messInt ( IntID, IntSec )
VALUES
  (1,1),
  (2,60),
  (3,15*60),
  (4,60*60);
  
CREATE TABLE messDat ( messID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, messDate DATETIME );
INSERT INTO messDat ( messDate )
VALUES
  ( '2015-08-08 12:59:59' ),
  ( '2015-08-08 13:00:00' ),
  ( '2015-08-08 13:00:01' ),
  ( '2015-08-08 13:14:59' ),
  ( '2015-08-08 13:15:00' ),
  ( '2015-08-08 13:15:01' ),
  ( '2015-08-08 13:29:59' ),
  ( '2015-08-08 13:30:01' );

SELECT 
  messDat.*, messInt.*,
  FROM_UNIXTIME( ( UNIX_TIMESTAMP( messDat.messDate ) DIV messInt.IntSec ) * messInt.IntSec ) AS intDate
FROM
  messDat, messInt;

[Medium]

Den Wust würde es bei der Verwendung o.g. Formel geben, wenn ich das mit dem Statement von DejanVu einsetzen würde. Klammerwald.
Das mit dem Datum als Zahl hatte ich schon verstanden, deswegen ergab sich ja die Frage nach einer entsprechenden Umwandlung die arithmetisch arbeitet, und nicht stumpf die Ziffern ohne Delimiter in eine Zahl schmeißt. (Wer braucht so ein blödes Format überhaupt!? Da kann man doch nix mit machen! (Ausser vergleichen, okay.))

[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