chillministrator
Goto Top

Doppelte Daten anzeigen die nicht exact doppelt sind!

Hallo zusammen,

ich habe eine Tabelle GRUNDWASSER die seit über 20 Jahren täglich mit Messdaten von ettlichen Loggern gefüttert wird.
Es ist aufgefallen das manche Logger die Messdaten doppelt geschickt haben ( warscheinlich auf schlechte Verbindung der SIM Karten im Logger zurückzuführen). Jetzt möchte ich mir sämtliche dieser doppelten Einträge anzeigen lassen.

Das Problem bei der ganzen Sache ist , dass das Datum mit Uhrzeit in einer Spalte steht und die doppelten daten somit nicht identisch !
Die Messdaten sind die gleichen allerdings mit anderem Zeitstempel.

Tabelle : Grundwasser

Spalte a: Datum (date) : 10.06.2016 00:00:40

Spalte b: Geräte ID (number) : 4558

Es sollen jetzt alle IDs gelistet werden die doppelt geliefert haben.
Ich steh gerade voll auf dem Schlauch.

Ich hoffe Ihr könnt mir helfen....

Grüße

Content-Key: 306769

Url: https://administrator.de/contentid/306769

Ausgedruckt am: 29.03.2024 um 14:03 Uhr

Mitglied: TlBERlUS
TlBERlUS 10.06.2016 um 10:44:41 Uhr
Goto Top
Hi,

müsstest du doch mit Excel anzeigen lassen können?

Oder sollen die Daten irgendwie verwertet werden?
Mitglied: atze187
atze187 10.06.2016 um 10:48:14 Uhr
Goto Top
Hi

Deine Beschreibung ist leider recht schwammig, kannst du ein paar Zeilen aus der Tabelle bereitstellen?

André
Mitglied: Chillministrator
Chillministrator 10.06.2016 um 10:52:42 Uhr
Goto Top
Diese doppelten Datensätze verfälschen die Berechnungen deshalb müssen diese aus der Tabelle raus.


Ich suche nur nach der richtigen Abfrage damit ich diese Datensätze anzeigen lassen kann.

Nach der Kontolle werde ich sie löschen.
Mitglied: Lochkartenstanzer
Lochkartenstanzer 10.06.2016 um 10:56:17 Uhr
Goto Top
Moin,

ich würde in einem Skript einfach die aufeinanderfolgenden Werte der Meßdaten evrgleich und wenn die übereinstimmen ,die Differenz von Datum und Uhrzeit bilden udn schauen, ob die dicht genug beieinanderliegen.

das köntne man mit zwwei verschachtelten IF-Abfragen abfrühstücken.

lks
Mitglied: Chillministrator
Chillministrator 10.06.2016 um 11:00:58 Uhr
Goto Top
grun1
grun2
grun3
Mitglied: Lochkartenstanzer
Lochkartenstanzer 10.06.2016 um 11:03:32 Uhr
Goto Top
Die Frage ist jetzt natürlich, ob das als CSV SQL-DB Dbase.-DB oder welchem anderen Formnat vorliegt und welche Tools Du dafür verwenden willst.

lks
Mitglied: Chillministrator
Chillministrator 10.06.2016 um 11:10:12 Uhr
Goto Top
könntest Du mir das ein bisschen genauer erklären? Bei meinem Skriptkönnen wird das wohl eher ein Mitternachtssnack.

Wenn es keine passende Abfrage dazu gibt muss ich wohl darauf ausweichen...
Mitglied: Lochkartenstanzer
Lochkartenstanzer 10.06.2016 um 11:12:15 Uhr
Goto Top
Ganz einfach: Programmierst Du in C, SQL, Visualbasic? Und ist das einen einfache Exceltabbele, eine Textdatei oder eine Datenbankdatei, die Du da hast.

lks
Mitglied: Chillministrator
Chillministrator 10.06.2016 um 11:15:49 Uhr
Goto Top
Ist eine Oracle DB, sämtliche Developer-Tools darf ich nutzen.
Mitglied: Chillministrator
Chillministrator 10.06.2016 um 11:22:22 Uhr
Goto Top
Sorry, in SQL und es ist eine Datenbankdatei
Mitglied: Biber
Lösung Biber 10.06.2016 aktualisiert um 11:51:08 Uhr
Goto Top
Moin Chillministrator,

wenn es wirklich eine Oracle-DB ist, dann wird dir TRUNC() weiterhelfen.

Mit trunc(datumswert) kannst du den Zeit-Anteil (Stunden, Minuten, Sekunden...) aus einem Datumswert "streichen".

Beispiel:
Select sysdate, trunc(sysdate) from dual;
SYSDATE                 | TRUNC(SYSDATE)         
------------------------+------------------------
10.06.2016 11:39:33,000 | 10.06.2016 00:00:00,000

Bei deiner Tabelle musst du dich dann entscheiden, ob in dem Falle "doppelter" Logger-Datenlieferungen
a) die erste Lieferung eines Tages die einzig wahre & gültige ist und alle anderen to be ignored
b) die letzte Datenlieferung eines Tages die einzig wahre & gültige ist, weil... die frischeste Lieferung eines Tages gewinnen soll.

So oder so kannst du dann alle GRUN_MOBJ_IDs+GRUN_DATUM-Sätze aus der Tabelle GRUNDWASSER holen für die gilt
SELECT * from GRUNDWASSER
WHERE ( GRUN_MOBJ_ID, GRUN_DATUM) in (
    SELECT GRUN_MOBJ_ID, min(GRUN_DATUM) from GRUNDWASSER
        GROUP BY GRUN_MOBJ_ID, GRUN_DATUM
 )

Ob nun ...min(GRUN_DATUM) oder ...max(GRUN_DATUM) das Gewünschte ist, musst du festlegen (s.o.)

[Edit]
Alternativ zu der ..WHERE ...IN (Select ...from) -Bedingung ginge es auch über analytische Funktionen ( Aggregatfunktion und Partition by Trunc(GRUN_DATUM).
Falls das Dtaenvolumen deiner Messwerte wirklich für alle Messdaten tagesweise rückwirkend bis 1994 durchgepflügt werden soll, könnte die obige Skizze evtl. ein wenig inperformant sein.
[/Edit]


Grüße
Biber
Mitglied: ukulele-7
Lösung ukulele-7 10.06.2016 um 11:59:09 Uhr
Goto Top
Bei SQL könnte man es auch so angehen:
SELECT	*
FROM	grundwasser g1
INNER JOIN grundwasser g2
ON		g1.number = g2.number
AND		g1.messwert = g2.messwert
AND		g2.[date] BETWEEN g1.[date] AND dateadd(mi,5,g1.[date])
Du würdest zu jedem Datensatz alle Datensätze dazu joinen die
a) die selbe Geräte ID haben
b) den selben Messwert (ich verstehe das so das der zwingend gleich ist)
c) die jeweils 5 Minuten nach dem jetzigen Datensatz eingetroffen sind. Die Zeitspanne läßt sich natürlich ändern. dateadd() ist jetzt eine MSSQL Funktion, Oracle hat bestimmt auch eine um Minuten zu addieren.

Du würdest dann beide Datensätze nebeneinander sehen und könntest so Dubletten erstmal finden. Ich denke mal das eine Nachkontrolle oder etwas derartiges auch erfolgen soll?

Mit trunc() hast du den Nachteil das du immer einen Zeitsprung nicht erkennen wirst, z.B. von 23:59:59 auf 00:00:00.
Mitglied: Chillministrator
Chillministrator 10.06.2016 um 12:08:59 Uhr
Goto Top
Wow !

Danke Biber und ukulele-7
Das sind mal super Posts die mir extrem weiterhelfen.

Danke auch allen anderen die sich damit auseinandergesetzt haben.
Mitglied: Lochkartenstanzer
Lochkartenstanzer 10.06.2016 um 12:10:48 Uhr
Goto Top
Zitat von @Chillministrator:

Danke auch allen anderen die sich damit auseinandergesetzt haben.

Es hätte Dir viel schneller geholfen werden können, wenn Du gleich gesagt hättest, in welchem Format das Zeug vorliegt und in welcher Sprache Du die Statements brauchst. Wenn man das einem erst langsam aus der Nase ziehen muß, dauert es halt länger. face-smile

Schönen Freitag noch.

lks
Mitglied: Biber
Biber 10.06.2016 aktualisiert um 12:26:33 Uhr
Goto Top
P.S. zur Variante von ukulele-7

Äquivalent zur MS-SQL-Syntax
....DateAdd(mi, 5, g1.[date])

...wäre in Oracle
...g1.Date + interval '5' minute

und ja: die Ziffer 5 muss als String angegeben werden.

Grüße
Biber
Mitglied: ukulele-7
ukulele-7 10.06.2016 um 12:44:05 Uhr
Goto Top
Ergänzend könnte man dann auch noch sagen das z.B. bei dreifach Übermittlung der Daten innerhalb des Intervals dann eventuell mehr Results entstehen.

Datensatz / Zeit
A 10:01
B 10:02
C 10:04

ergibt dann:
A B
A C
B C

Je nachdem wie man beim bereinigen vorgeht kann das natürlich zu Konflikten führen.
Mitglied: Biber
Biber 10.06.2016 aktualisiert um 13:36:15 Uhr
Goto Top
Na ja, ukulele-7,

dann können wir ja deine Strategie etwas variieren...
SELECT	*
FROM	grundwasser g1
Where not exists (
SELECT 1 FROM grundwasser g2
WHERE		g1.number = g2.number
AND		g1.messwert = g2.messwert
AND		g2.GRUN_DATUM BETWEEN g1.date  AND g1.GRUN_DATUM + interval '2' hour)  

Hier würden jetzt (in der Theorie, da ungetestet) nur die Sätze im Resultset auftauchen, für die kein Datensatz mit gleichen IDs+Messwerten innerhalb der nächsten 2 Stunden existiert. Wobei auch 2 Stunden eher ein bisschen knapp kalkuliert ist...
Zur Anzeige nur der "doppelten" einfach das "not exists" in ein "exists" ändern.


Allerdings bleibt dann bei dieser Strategie das Problem, dass bei zwei Datenlieferungen an einem Tag mit UNgleichen Messwerten (also zB. eine marode Messung von 8.00h und einer korrigierten+korrekten von 9.00h) beide Datensätze relevant sind.
Bei meiner Strategie gäbe es immer max. 1 pro Tag.

Grüße
Biber
Mitglied: ukulele-7
ukulele-7 10.06.2016 um 15:24:57 Uhr
Goto Top
Das hängt halt stark von den Dubletten ab, vor allem wie viel Zeit zwischen den doppelten Übertragungen so liegt. Außerdem ob die Daten automatisch bereinigt werden sollen oder ob die Anzahl überschaubar ist und eine manuelle Bereinigung erfolgt. Machen kann man mit SQL eine Menge mehr...
Mitglied: Chillministrator
Chillministrator 13.06.2016 um 11:56:37 Uhr
Goto Top
Ich habe das jetzt erstmal so interpretiert:
select * from GRUNDWASSER g1 inner join GRUNDWASSER g2
  on    g1.GRUN_MOBJ_ID = g2.GRUN_MOBJ_ID
  and   g1.GRUN_HOCHREL = g2.GRUN_HOCHREL
  and   g2.GRUN_DATUM > g1.GRUN_DATUM and g2.GRUN_DATUM < g1.GRUN_DATUM + 1/24/60 
  where g1.grun_mobj_id = 9998;

Mit beteewn werden immer MIN/MAX mit einbezogen. Deshalb bin ich auf >min und <max ausgewichen.

Die Abfrage dauerte stolze 78 sec. für dieses eine Gerät.
Würde ich es mit allen durlaufen lassen würde das ganze dezente 11 h laufen.

Hat von Euch jemand erfahrung mit den Analytic functions bzw wie ich die Abfrage damit ausstatten kann? Bzw wie ich von diesen 11 Stunden wegkomme?
Grüße
Mitglied: ukulele-7
ukulele-7 13.06.2016 um 12:18:41 Uhr
Goto Top
Du kannst einen Index anlegen über
GRUN_MOBJ_ID, GRUN_HOCHREL, GRUN_DATUM
das könnte die Sache beschleunigen.
Mitglied: Biber
Biber 13.06.2016 aktualisiert um 14:15:30 Uhr
Goto Top
Moin Chillministrator,

na ja, zwei wesentliche Fragen sollten wir vorher klären:

a) auf welchen Feldern liegen denn Indizes, die irgendwie nutzbar wären? Evtl. würde es die Recherche durchaus beschleunigen, wenn ein nicht-unique-Index auf trunc(GRUN_DATUM), GRUN_MOBJ_ID angelegt werden würde. Denn die Laufzeit ist momentan unterirdisch, weil zwei full table scans aud die gesamte Monstertabelle gemacht werden
b) was genau ist denn dein Plan? Willst du "nur" in der Auswertung die logisch doppelten Sätze ausfiltern oder willst du alle anzeigen, die vermutlich Duplikate sind und die dann sukzessive nach Sichtkontrolle aus der Tabelle löschen?

Wenn so ein Index angelegt wäre, dann könntest du die Duplikate+das dazugehörige Original herausbekommen mit

select * from GRUNDWASSER g1 
where (Trunc(GRUN_DATUM), GRUN_MOBJ_ID, GRUN_HOCHREL) IN
( Select Trunc(GRUN_DATUM), GRUN_MOBJ_ID, GRUN_HOCHREL
 FROM  GRUNDWASSER g2
 -- optional zum Test:  where grun_mobj_id <= 9998
GROUP BY Trunc(GRUN_DATUM), GRUN_MOBJ_ID, GRUN_HOCHREL
Having count(*) > 1
)

Wie gesagt, wenn wenigstens irgendein Index unterstützend zur Verfügung steht, können wir ernsthaft reden.
So kennen wir ja nicht mal das Mengengerüst, also wissen nicht, ob wir durch 3MByte oder 55TByte Daten durchpflügen.

Grüße
Biber
Mitglied: Chillministrator
Chillministrator 14.06.2016 um 09:24:13 Uhr
Goto Top
Guten Morgen Biber und Ukulele,

Habe gerade den Index über mobj_id, datum und hochrel angelegt. Leider war das Ergebnis was die Geschwindigkeit angeht weiterhin unterirdisch.

Mir wurde sehr ans Herz gelegt die oben gepostete Abfrage ( welche genau das ausgiebt was gesucht wird ) mit den analytic functions umzubauen um den Geschwindigkeitsunterschied zu sehen.
Leider habe ich mit diesen noch nicht gearbeitet und darf mich jetzt damit auseinandersetzten.
Sicher gibt es bei DB viele Wege die zum Ziel führen, da mir aber bereits dieser winkt nahegelegt wurde, würde ich gerne auch diesen einschlagen.

Bei dieser Abfrage werden eben genau die DS angezeigt bei der ein Logger innerhalb eines Zeitfensters (1 Min) doppelt gesendet hat aber durch die exakte Uhrzeit im DS nicht als dublette gesehen wird.

select * from GRUNDWASSER g1 inner join GRUNDWASSER g2 
on    g1.GRUN_MOBJ_ID = g2.GRUN_MOBJ_ID 
and   g1.GRUN_HOCHREL = g2.GRUN_HOCHREL 
and   g2.GRUN_DATUM > g1.GRUN_DATUM and g2.GRUN_DATUM < g1.GRUN_DATUM + 1/24/60  
where g1.grun_mobj_id = 9998;
Mitglied: ukulele-7
ukulele-7 14.06.2016 um 09:28:10 Uhr
Goto Top
Leider nutze ich kein Oracle sondern MSSQL und habe mit analytischen Funktionen wenig Erfahrung. Dabei werde ich dir also nicht helfen können sry. Ich habe auch kürzlich Dubletten elemeniert, dabei habe ich aber die Laufzeit als Nebensache behandelt, meine Datenbestände sind nicht groß.
Mitglied: Lochkartenstanzer
Lochkartenstanzer 14.06.2016 um 09:29:10 Uhr
Goto Top
Moin,

Wenn das in der DB selbst solange dauert, was spricht dagegen, einfach alle Datensätze in eine csv zu exportieren, mit einem C-Program (oder shellscript) druchzunudeln und sich eine Liste mit den zu löschenden Datensätzen geben zu lassen und diese dann in der DB zu löschen?

Wäre zumindest mein pragmatischer Ansatz, wenn es innerhalb der DB zu langsam geht.

lks
Mitglied: Chillministrator
Chillministrator 14.06.2016 um 09:36:38 Uhr
Goto Top
Danke erstmal für Eure Hilfe bis hierhin.

Wie gesagt gibt es ja diverse Wege um ans Ziel zu kommen.
Meine Aufgabe besteht leider darin die analytic functions kennen zu lenrnen und im besten Fall in den Griff zu bekommen face-smile
Mitglied: Biber
Biber 14.06.2016 aktualisiert um 11:39:08 Uhr
Goto Top
Moin Chillministrator,

bin ja durchaus bereit dir zu helfen, auch mit analytic functions, ich möchte nur nicht total im Nebel stochern.
Ohne nutzbare Indizes kommen wir nicht weit, egal mit welcher Strategie.

Was ich bisher von deiner Tabelle weiss, ist;
ich habe eine Tabelle GRUNDWASSER die seit über 20 Jahren täglich mit Messdaten von ettlichen Loggern gefüttert wird.
Und die Struktur der Tabelle, die du in einem deiner ersten Kommentare gepostet hast.

Fragen dazu:
  • wenn die Logger Daten liefern, tun sie das "täglich", sagst du. Bedeutet: planmäßig 1x täglich oder täglich im Stundentakt oder welcher Frequenz? Wie viele Datenlieferungen sind denn pro Tag da?
  • Wenn ich diese Tabelle angelegt hätte, dann als partionierte Tabelle (also zB. je eine Partition für einen Monat oder ein Quartal, jedenfalls für einen Zeitraum). Denn in der Regel müssten die meisten Fragestellungen im täglichen Auswerten sich auf einen Teil oder alle der GRUN_MOBJ_IDs im gleichen Zeitraum (April 2013 oder Jahr 2016...) beziehen. Deshalb wäre es natürlich geschickter, wenn alle relevanten Daten sich in einer oder in wenigen Partitionen befinden. Hat das euer DBA auch so gesehen/ist dem so?

Das Performanzproblem bei einem INNER JOIN liegt daran, dass vermutlich beide Tabellen (G1 und G2) sequentiell gelesen werden, also jeder gottverdammte Satz angefasst wird (Full table scan). [Dazu müste aber auch der Ausführungsplan/das EXPLAIN etwas aussagen.] Davon müssen wir jedenfalls weg - wir sollten den potentiell relevanten SubSet der Tabelle, der überhaupt Duplikate enthalten kann, eingrenzen/wirksam filtern.
Dazu noch die Frage: diese wie auch immer verursachten Doppeldatenlieferungen traen schon immer auf seit 20 Jahren oder nur im Jahr 2009 oder nur vom 15.04.2015 bis 29.11.2015 oder wie ist der Sachverhalt?

Wenn das alles diesen Thread sprengen würde, dann lass uns diese Details über PN austauschen.

Grüße
Biber