Messdaten Ermitteln verarbeiten und ausgeben

[Mathrim]

Hallo,

ich bin noch sehr unerfahren mit SQL Datenbanken und habe folgendes vor.

Ich möchte von verschiedenen Baugleichen Sensoren daten erfassen und in eine SQL Tabelle "Measurement" schreiben. ->Das Funktioniert schonmal

Nun möchte ich alle 15 min Die Messdaten abfragen.
Die Sensoren haben zwei ID`s IDB (ID des Verteilerblocks) IDC (ID des Kanals) Beide gehen von 0-7.

Nun Möchte ich, dass in einer anderen Tabelle "ChannelState" eine Zeile für jeden Sensor Angelegt wird, der in den Letzten 15 min Messdaten in der Tabelle Measurement abgelegt hat.BSP

IDB	IDC	VAL1	VAL2
0	0	12	250
1	1	13	320
1	2	15	230
2	1	22	456
2	1	12	845

Der Sensor aus Zeile 2ist der einzige im Block und der einzige im Kanal.
Nun ist es so das Manche Sensoren Auf unterschiedlichen Kanälen des Gleichen Verteilerblocks Laufen. Wie Sensor aus Zeile 3 und 4.

manchmal laufen aber auch zwei Sensoren auf dem Block UND dem selben Kanal wie Sensor 3 und 4.
Im falle 5 und 6 Muss hier ein Mittelwert errechnet werden.

Es Müssten in der channel State tabelle also folgende zeilen angelegt werden

IDB	IDC	VAL1	VAL2
0	0	12	250
1	1	13	320
1	2	15	230
2	1	22	456

Insgesamt ist es eine Zeile Weniger, da 5 und 6 aus der ersten Adresse den Selben Block und den Selben Kanal Repräsentieren.

Nun ist die Frage, wie ich das der Datenbank begreiflich machen kann.

ich hatte überlegt über eine FOR schleife in einer For schleife vorzugehen

FOR (int tIDB=0;tIDB<=7;tIDB++ {
FOR(int tIDC=0;tIDC<=7;tIDC++{
Insert Into ChannelState select *From Measurement where timestamp >now(()-900 IDB=tIDB AND IDC=tIDC;
}//FOR tIDC
}//FOR tIDB

Allerdings werden dann alle zeilen der Letzten 15 minuten einbezogen.

Wie kann ich die anzahl der Zeilen Eingrenzen?

 

[dabadepdu]

Warum erfasst Du in ChannelState auch Werte?
Warum überhaupt die Tabelle ChannelState? Die Werte ließen sich immer bei Bedarf aus der Tabelle Measurement ermitteln, sofern dort auch Zeitangaben existieren (geh ich mal von aus)
Nach welchen Regeln die ChannelStates ermittelt werden (aus Measurement) ist relativ egal, würde aber sicher nicht über eine Schleife, sondern eine Aggregatabfrage erfolgen.

Ist es so, dass diese Werte in ChannelStates zyklisch (Interval) aktualisiert werden sollen, sodass immer NUR die letzten 15 Minuten abgebildet sind (alte Werte raus, neue rein)?

 

[Mathrim]

Naja mir geht es darum das ich in Measurements alle werte von allen Sensoren habe. Damit kan ich wenn es zu unerwarteten ereignissen kommt nachvolziehen welcher Sensor was gemacht hat.

Die Tabelle ChannelState soll mir immer wenn die Abfrage erfolgt also alle 15 min Für jeden angesprochenen Kanal Die werte ausgeben so das ich diese Tabelle Nutzen kann um einen Verlauf des ChannelStates für jeden kanal zu erstellen.

 

[dabadepdu]

Mmh, also nach Deiner Darstellung verstehe ich es so:
measurement: alle Datensätze
channelstates: Aggregat der letzten 15 Minuten
tja und dann scheinbar noch eine Tabelle, die den Verlauf von channelstates abbildet.

Alles könnte man über eine Abfrage von Measurement erreichen.
Hast Du so viele Daten oder so wenig Ressourcen, dass es so gemacht werden soll?

 

[Mathrim]

wie gesagt, ich bin noch sehr neu in der Datenbankgeschichte. Das war halt die Methode die ich mir überlegt habe, wenn du eine bessere idee hast lasse ich mich gerne belehren.


Was daten und Resourcen angeht

Es die Anzahl der Kanäle ist aktuel auf 64 begrenzt ich geh von nicht mehr als 5 Sensoren pro Kanal aus sind also
320 Reihen alle 15min.

Die Datenbank Läuft auf einem Raspi 3 und das Wlan über das die Daten reinkommen, wird von einem ESP8266 Bereitgestellt.

Da die Sensoren sich gegenseitig Aufwecken um zu senden sind auch nie mehr als 3 Sensoren im Wlan also das sollte passen.

Hoffe das war deine Frage.

 

[dabadepdu]

Ich würde sagen, Du kannst alles in die Measurements reinlaufen lassen und alle 15 Minuten das Aggregat abfragen, darstellen oder in eine separate Aggregattabelle eintragen.

Versuch einfach mal ne Abfrage auf Measurements, die die letzen 15 Minuten zurück gibt (alles).
Das kann man dann so erweitern, dass es die Konstellation ergibt, die Du haben willst.
Man muss vielleicht irgendwie sicherstellen, dass man bei "alle 15 Minuten" nichts verpasst und nicht doppelt abfragt. Kommt etwas drauf an, wie dicht die Werte reinkommen.

 

[ukulele]

Ich möchte von verschiedenen Baugleichen Sensoren daten erfassen und in eine SQL Tabelle "Measurement" schreiben. ->Das Funktioniert schonmal

In deinen Beispieldaten sieht man keinen Zeitstempel, den gibt es aber oder? Heißt die Spalte "timestamp"?

Nun möchte ich alle 15 min Die Messdaten abfragen.

Alle 15 Minuten für die letzten 15 Minuten? Okay ob das wirklich sinnvoll erscheint kann ich jetzt noch nicht sagen Ich würde auch sagen wir fangen erstmal mit einem Aggregat an und bauen damit die letzten 15 Minuten nach. Ein Raspi ist sicherlich keine Rakete und bei vielen Messdaten könnte man auch alle 15 Minuten ein Aggregat zwischenspeichern.

Die Sensoren haben zwei ID`s IDB (ID des Verteilerblocks) IDC (ID des Kanals) Beide gehen von 0-7.

Solange es die Werte immer so im Doppelpack gibt ist das okay von Design her.

Nun Möchte ich, dass in einer anderen Tabelle "ChannelState" eine Zeile für jeden Sensor Angelegt wird, der in den Letzten 15 min Messdaten in der Tabelle Measurement abgelegt hat.BSP

IDB	IDC	VAL1	VAL2
0	0	12	250
1	1	13	320
1	2	15	230
2	1	22	456
2	1	12	845

Der Sensor aus Zeile 2ist der einzige im Block und der einzige im Kanal.

Das verstehe ich nicht. Warum ist Zeile 3 denn kein Block und Kanal?

Nun ist es so das Manche Sensoren Auf unterschiedlichen Kanälen des Gleichen Verteilerblocks Laufen. Wie Sensor aus Zeile 3 und 4.

manchmal laufen aber auch zwei Sensoren auf dem Block UND dem selben Kanal wie Sensor 3 und 4.

???

Ist das überhaupt relevant? Ist eine Kombination Block/Kanal eindeutig oder nicht?

Im falle 5 und 6 Muss hier ein Mittelwert errechnet werden.

Das lässt sich machen. Interessanter Weise hast du es nicht gemacht in der folgenden Tabelle:

Es Müssten in der channel State tabelle also folgende zeilen angelegt werden

IDB	IDC	VAL1	VAL2
0	0	12	250
1	1	13	320
1	2	15	230
2	1	22	456

Insgesamt ist es eine Zeile Weniger, da 5 und 6 aus der ersten Adresse den Selben Block und den Selben Kanal Repräsentieren.

Also wurde in deinem Fall der Datensatz in 5 ausgewählt, du willst aber den Durchschnitt? Haben die beiden Datensätze den exakt selben Zeitstempel oder kann der abweichen?

Das Problem wird sein: Wenn die Sensoren alle 15 Minuten nur einen Wert pro Sensor liefern, kann der in deinem Betrachtungsintervall der letzten 15 Minuten liegen. Er kann sich aber auch eine Sekunde verspäten, und der Wert fällt aus der Betrachtung. Je mehr Messwerte du in den 15 Minuten pro Sensor hast, desto weniger wirkt sich das aus. Aber es lohnt sich eine genaue Betrachtung. Wenn die Abstände der Messungen etwas variieren, könnte man eventuell die Sekunden raus lassen und damit quasi den Durschnitt für knapp 16 Minuten alle 15 Minuten berechnen, oder so.

ich hatte überlegt über eine FOR schleife in einer For schleife vorzugehen

...aber bitte keine Schleife , egal in welcher Sprache.

 

[Mathrim]

In deinen Beispieldaten sieht man keinen Zeitstempel, den gibt es aber oder? Heißt die Spalte "timestamp"?

Ja der Timestamp ist in jeder Zeile vorhanden.

Das verstehe ich nicht. Warum ist Zeile 3 denn kein Block und Kanal?

Doch, die BlockID ist 0 und die Kanal ID ist auch 0. 0 ist ein Wert nicht NULL.

Ich habe mal eine Skizze vom Projektaufbau gemacht ich hoffe das macht die Organisation mit IDB und IDC deutlicher

Projektaufbau-drawio hosted at ImgBB

Image Projektaufbau-drawio hosted in ImgBB

ibb.co

Also wurde in deinem Fall der Datensatz in 5 ausgewählt, du willst aber den Durchschnitt? Haben die beiden Datensätze den exakt selben Zeitstempel oder kann der abweichen?

Der Zeitstempel weicht ab, da alle Sensoren ihre Daten nacheinander Übermitteln.

Das Problem wird sein: Wenn die Sensoren alle 15 Minuten nur einen Wert pro Sensor liefern, kann der in deinem Betrachtungsintervall der letzten 15 Minuten liegen. Er kann sich aber auch eine Sekunde verspäten, und der Wert fällt aus der Betrachtung. Je mehr Messwerte du in den 15 Minuten pro Sensor hast, desto weniger wirkt sich das aus. Aber es lohnt sich eine genaue Betrachtung. Wenn die Abstände der Messungen etwas variieren, könnte man eventuell die Sekunden raus lassen und damit quasi den Durschnitt für knapp 16 Minuten alle 15 Minuten berechnen, oder so.

Ist es Vllt. Möglich die letzten Zeitstempel der Letzten Betrachtung abzulegen und den dann wieder als Startpunkt für die nächste Betrachtung zu nutzen?

...aber bitte keine Schleife , egal in welcher Sprache.

Ok dann nicht XD


Ist jetzt die Frage wie ich anfange.

1.Ich müsste mir Eine Variable deklarieren in der ich den Letzten Abgefragten Zeitstempel Speicher.
2. Ich muss ich alle Messreihen betrachten von diesem Stempel bis Now().
3. Ich muss ich die Sensoren Sorieren nach IDB und IDC und schauen ob es Sensoren gibt bei denen IDB AND IDC gleich sind
4. IF nur ein sensor vorhanden-> werte in Channel State schreiben.
else (mehrere Sensoren) AVG()->werte ich Channel State schreiben.
__________________
Wenn das soweit funktioniert ist schon mal ein gutes stück geschafft.

 

[dabadepdu]

Ist jetzt die Frage wie ich anfange.

Erstmal braucht man keine Variablen, genausowenig wie Schleifen. Alle Daten stehen, gehören, landen in deiner DB.

Also Du könntest mal die Measure Tabelle posten, als Create Script, also Text, ganz einfach.
Dann dazu paar Daten, auch als Script (Insert).
Laut Deiner Grafik laufen die Messwerte einfach in die DB (Tabelle Measurment), ok.
zu irgendeinem Zeitpunkt fragst Du nun die Messwerte ab, die letzten 15 Minuten.
Diese Abfrage kannst Du formulieren und auch hier posten, einfaches Selectstatement.

Daraus machen wir dann ein Aggregat.
Das landet in einer neuen Tabelle, z.B. mit dem zuletzt abgefragten, größten Zeitstempel.
Der wird der Aufhänger für die nächste Abfrage. Das ist eine Fachfrage, was willst Du wirklich? Die letzten 15 Minuten oder lückenlose Daten ...
Die Abfrageergebnisse landen alle 15 Minuten in der neuen Tabelle.

 

[Mathrim]

SO! Das ist die Tabelle Measuerements





CREATE TABLE `Measurement` (
`Timestamp` timestamp NULL DEFAULT current_timestamp(),
`IDB` tinyint(8) unsigned DEFAULT NULL,
`IDC` tinyint(8) unsigned DEFAULT NULL,
`MoistMin` tinyint(8) unsigned DEFAULT NULL,
`Moist` tinyint(8) unsigned DEFAULT NULL,
`MoistMax` tinyint(8) unsigned DEFAULT NULL,
`HumMin` tinyint(8) unsigned DEFAULT NULL,
`Humidity` tinyint(8) unsigned DEFAULT NULL,
`HumMax` tinyint(8) unsigned DEFAULT NULL,
`Airpressure` int(11) unsigned DEFAULT NULL,
`Temperature` float DEFAULT NULL,
`Light` tinyint(8) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci






Hier noch die Tabelle ChannelState welche vom Aufbau her identisch ist

CREATE TABLE `ChannelState` (
`Timestamp` timestamp NULL DEFAULT current_timestamp(),
`IDB` tinyint(8) unsigned DEFAULT NULL,
`IDC` tinyint(8) unsigned DEFAULT NULL,
`MoistMin` tinyint(8) unsigned DEFAULT NULL,
`Moist` tinyint(8) unsigned DEFAULT NULL,
`MoistMax` tinyint(8) unsigned DEFAULT NULL,
`HumMin` tinyint(8) unsigned DEFAULT NULL,
`Humidity` tinyint(8) unsigned DEFAULT NULL,
`HumMax` tinyint(8) unsigned DEFAULT NULL,
`Airpressure` int(11) unsigned DEFAULT NULL,
`Temperature` float DEFAULT NULL,
`Light` tinyint(8) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci



Hier ein paar Inserts um sie mit Daten zu füllen

INSERT INTO GA_MK23.Measurement (IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (0,0,50,55,95,40,46,50,99,21.11,62);

INSERT INTO GA_MK23.Measurement (IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (0,1,60,55,95,40,39,50,99,21.11,62);

INSERT INTO GA_MK23.Measurement (IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (0,2,30,3495,40,39,50,99,21.11,62);

INSERT INTO GA_MK23.Measurement (IDB,IDC,MoistMin,Moist,MoistMax,HumMin,Humidity,HumMax,Airpressure,Temperature,Light) VALUES (0,2,30,36,95,40,45,50,99,21.11,62);


Und der Select Befehl für alle Daten die noch nicht in Channelstate übernommen wurden. (Dieser wird dann im 15 min tackt ausgeführt)

SELECT * FROM Measurement WHERE Timestamp > (SELECT MAX(timestamp) FROM ChannelState);

 

[dabadepdu]

Gut, ich hab das mal etwas umgebaut. (in den Inserts oben gibt es einen Fehler, Komma fehlt bei einem Datensatz)

MySQL 8.0

| version() | |:----------| | 8.0.36 |

dbfiddle.uk