Messwerte, welche stagnieren interpolieren?

jmar83

SQL-Guru
Beiträge
146
Hallo zusammen

Gegeben ist eine Tabelle mit versch. Messwerten (auch aus versch. Sensoren, über den FK "device_id" bestimmt)

Nun ist es so, dass der Hintergrunddienst die Messwerte nur schreibt, wenn sie sich verändert haben. Bis zum nächsten Datensatz (unter device_id x) können unter Umständen Sekunden, Minuten, Stunden oder Tage liegen.

Jeder Datensatz hat natürlich auch einen PK.

Frage: Wie kann ich mit einer SELECT-Abfrage stagnierende Messwerte interpolieren? Die Felder, welche in der Abfrage zusätzlich hinzugefügt werden sollen, sind das datetime-Feld sowie der Messwert.

Also:

Daten in der Tabelle:

id, device_id, messwert, timestamp
1,8,2,2019-05-06 10:15:03
2,8,6,2019-05-06 10:15:05
3,8,8,2019-05-06 10:15:08
4,8,4,2019-05-06 10:15:10
5,8,3,2019-05-06 10:15:14
6,8,6,2019-05-06 10:15:17
7,8,1,2019-05-06 10:115:19


Daten bei der Abfrage, ohne Interpolation, (ohne PK, "WHERE device_id = 8", "ORDER BY `timestamp` ASC"):

id, device_id, messwert, timestamp
8,2,2019-05-06 10:15:03
8,6,2019-05-06 10:15:05
8,8,2019-05-06 10:15:08
8,4,2019-05-06 10:15:10
8,3,2019-05-06 10:15:14
8,6,2019-05-06 10:15:17
8,1,2019-05-06 10:115:19

Daten bei der Abfrage mit Interpolation, (ohne PK, "WHERE device_id = 8", "ORDER BY `timestamp` ASC"):

id,device_id,messwert,timestamp
8,2,2019-05-06 10:15:03
8,2,2019-05-06 10:15:04 x
8,6,2019-05-06 10:15:05
8,6,2019-05-06 10:15:06 x
8,6,2019-05-06 10:15:07 x

8,8,2019-05-06 10:15:08
8,8,2019-05-06 10:15:09 x
8,4,2019-05-06 10:15:10
8,4,2019-05-06 10:15:11 x
8,4,2019-05-06 10:15:12 x
8,4,2019-05-06 10:15:13 x

8,3,2019-05-06 10:15:14
8,3,2019-05-06 10:15:15 x
8,3,2019-05-06 10:15:16 x

8,6,2019-05-06 10:15:17
8,6,2019-05-06 10:15:18 x
8,6,2019-05-06 10:15:19 x

8,1,2019-05-06 10:15:20

x = interpolierter Datums-/Zeitstempel, rot = interpolierter Messwert.

Besten Dank für eure Tipps. :)
 
Zuletzt bearbeitet:
Werbung:
Code:
id, device_id, messwert, timestamp
8,2,2019-05-06 10:15:03

4 Spalten definiert, nur 3 sind da. Fail.

Davon abgesehen: was Du suchst sind offenbar Window-Funktionen und so Dinge wie generate_series(), die alte und auch neue Versionen von MySQL nicht oder nur teilweise können.


Andreas
 
Danke fürs Feedback.

Und sorry für den copy/paste-Fehler. Diesen kann ich leider, so wie es aussieht, nicht mehr korrigieren. Da evtl. bereits auf den Beitrag geantwortet wurde...?
 
Auch egal jetzt. Ich bin zu lange aus dem Studium raus, um mit Statistik und Interpolation und so'n Kram fit zu sein. Im Open-Source - Statistikpaket "R" gibt es da aber definitiv passende Funktionen - und wenn Du PG hättest, könntest direkt aus PG heraus mit pl/R solche Funktionen mit den Daten in der DB nutzen. Ich denke mal, wenn ich das sehe:

Code:
8,2,2019-05-06 10:15:03
8,2,2019-05-06 10:15:04 x
8,6,2019-05-06 10:15:05
8,6,2019-05-06 10:15:06 x
8,6,2019-05-06 10:15:07 x
8,8,2019-05-06 10:15:08
8,8,2019-05-06 10:15:09 x

dann würde eine gute Software das besser glätten.
 
Es muss nix berechnet werden: "Nun ist es so, dass der Hintergrunddienst die Messwerte nur schreibt, wenn sie sich verändert haben."

...es sollten nur die fehlenden Datensätze (für jede Sekunde) mit dem Messwert welcher der Sensor das letze Mal gemessen und geschrieben hat, ergänzt werden.

(Bei stagnierenden Werten schreiben wir nicht in die Datenbank um Platz zu sparen, bei der Darstellung im Diagramm oder zur Berechnung des Durchschnitts sind diese Daten alleine (direkt) nicht brauchbar)
 
okay, ich zeige Dir eine Lösung:

Code:
test=*# select * from jmar83 ;
         ts          | wert
---------------------+------
 2019-05-07 08:00:00 |   10
 2019-05-07 08:01:00 |   10
 2019-05-07 08:02:00 |   10
 2019-05-07 08:05:00 |   20
 2019-05-07 08:07:00 |   20
(5 rows)


Wir suchen nun die Werte zwischen 08:00 und 08:10 Uhr.

Code:
test=*# with alle_zeiten as (select '2019-05-07 08:00:00'::timestamp without time zone + s * '1minute'::interval as zeit from generate_series(1,10) s) select zeit, ts, coalesce(wert, (select wert from jmar83 where ts < zeit order by ts desc limit 1)) from alle_zeiten left join jmar83 on alle_zeiten.zeit=jmar83.ts;
        zeit         |         ts          | coalesce
---------------------+---------------------+----------
 2019-05-07 08:01:00 | 2019-05-07 08:01:00 |       10
 2019-05-07 08:02:00 | 2019-05-07 08:02:00 |       10
 2019-05-07 08:03:00 |                     |       10
 2019-05-07 08:04:00 |                     |       10
 2019-05-07 08:05:00 | 2019-05-07 08:05:00 |       20
 2019-05-07 08:06:00 |                     |       20
 2019-05-07 08:07:00 | 2019-05-07 08:07:00 |       20
 2019-05-07 08:08:00 |                     |       20
 2019-05-07 08:09:00 |                     |       20
 2019-05-07 08:10:00 |                     |       20
(10 rows)

test=*#

generate_series() kann MySQL nicht. Entweder nachbauen, oder per Hand eine passende Tabelle erstellen. So in der Form ...
 
Wenn ich das richtig verstehe muss nichts interpoliert werden sondern es gilt zu jedem Zeitpunkt der letzte gemessene Wert denn, sollte sich der Wert ändern, würde ja ein neuer Messwert in die DB geschrieben. Also würde ich erstmal davon ausgehen das der letzte gemessene Wert aktuell ist.

Wenn du dann den letzten Messwert nur für einen Zeitpunkt ermitteln willst geht das ganz ohne Window-Funktion:
Code:
SELECT t.*,tabelle.messwert
FROM (

SELECT id,device_id,max(timestamp) AS timestamp
FROM tabelle
WHERE timestamp <= X
GROUP BY id,device_id

) t
LEFT JOIN tabelle
ON t.id = tabelle.id
AND t.device_id = tabelle.device_id
Wenn du natürlich für mehrere Zeitpunkte die Werte auf einmal bestimmen willst, also kein X wie hier existiert, sind Window-Funktion sehr nützlich. Welche MySQL Version ist das?
 
Werbung:
Hallo zusammen

Vielen Dank für die Feedbacks.

Die Version (nicht MySQL, sondern MariaDB!) lautet: 10.1.37-MariaDB-0+deb9u1
 
Zurück
Oben