Doppelte Einträge anhand des Timestamps erfassen

[trmax]

Hallo!

Ich habe eine Tabelle "lorawan", wo ich Temperaturwerte von einem Funkempfänger, welcher mir die Werte der Temperatursensoren empfängt, alle 10 Minuten speichere. (1 Wert ca. alle 10 Minuten +/- einige Sekunden)

Die Tabelle hat folgenden Aufbau:

ID | DATE| TEMP | DEVEUI

"Date" ist ein Timestamp im Format 2019-12-08 09:44:41
"DEVEUI" ist die Seriennummer des Sensors, nach der ich bei der Abfrage Suche. Es gibt 2 Sensoren, die in die Tabelle "lorawan" schreiben.

Jetzt ist es so, dass ich manchmal doppelte Einträge habe, die max. 5 Sekunden auseinander liegen. Diese möchte ich finden, und jeweils einen davon löschen. Die Einträge kommen daher, dass die Sensoren an mehrere Empfangsstationen senden, und diese dann die Einträge doppelt speichern, wenn sie mehr als 500ms auseinander liegen. Die Zeitverschiebung von bis zu 5 Sekunden ist mit der Laufzeit zu begründen.

Welches Statement benötige ich, um die doppelten Einträge zu entferne?

liebe Grüße,
Markus

 

[trmax]

Ein doppelter Eintrag schaut so aus, wobei der Zeitstempel lediglich wenige Sekunden vom anderen abweicht. Einer der beiden sollte also gelöscht werden

 

[akretschmer]

Okay, mal vereinfacht:

test=*# select * from trmax;
 id |         ts          | temp
----+---------------------+------
  1 | 2019-12-16 10:00:00 |   20
  2 | 2019-12-16 10:10:00 |   15
  3 | 2019-12-16 10:20:00 |   17
  4 | 2019-12-16 10:20:10 |   17
  5 | 2019-12-16 10:30:00 |   14
(5 rows)

Okay, Du wiullst nun offenbar den Datensatz mit ID=4 löschen, oder?

test=*# select *, case when ts - lag(ts) over (order by ts) < '3 minutes'::interval then true else false end as delete from trmax;
 id |         ts          | temp | delete
----+---------------------+------+--------
  1 | 2019-12-16 10:00:00 |   20 | f
  2 | 2019-12-16 10:10:00 |   15 | f
  3 | 2019-12-16 10:20:00 |   17 | f
  4 | 2019-12-16 10:20:10 |   17 | t
  5 | 2019-12-16 10:30:00 |   14 | f
(5 rows)

Das 't' in Spalte delete heißt ja TRUE ;-)

test=*# with delete as (select id, case when ts - lag(ts) over (order by ts) < '3 minutes'::interval then true else false end as delete from trmax) delete from trmax where id in (select id from delete where delete);
DELETE 1
test=*# select * from trmax;
 id |         ts          | temp
----+---------------------+------
  1 | 2019-12-16 10:00:00 |   20
  2 | 2019-12-16 10:10:00 |   15
  3 | 2019-12-16 10:20:00 |   17
  5 | 2019-12-16 10:30:00 |   14
(4 rows)

Einfach, oder?

 

[trmax]

Danke ich werde es testen und melde mich wieder!

lg

 

[trmax]

select *, case when DATE - lag(DATE) over (order by DATE) < '3 minutes'::interval then true else false end as delete from lorawan_tummelplatz;

habs angepasst. irgenwie wirft es mir einen Fehler

Statische Analyse:

3 Fehler wurden während der Analyse gefunden.

1. Variablenname wurde erwartet. (near ":" at position 72)
2. Unerkanntes Schlüsselwort. (near "as" at position 107)
3. Unerwartetes Zeichen. (near "delete1" at position 110)

SQL-Befehl:

select *, case when DATE - lag(DATE) over (order by DATE) < '3 minutes'::interval then true else false end as delete1 from lorawan_tummelplatz

MySQL meldet:

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei '::interval then true else false end as delete1 from lorawan_tummelplatz' in Zeile

 

[akretschmer]

nun ja, ich habe PostgreSQL ...