15.12.2014

Dublettenentfernung

Technical Value

Öfter als einem lieb ist steht man vor dem Dublettenproblem, bei dem (meist fälschlicherweise) der gleiche Datensatz mehrfach in einer Tabelle auftaucht. Wenn man nicht die komplette Tabelle löschen und neu laden will oder kann, müssen die Dubletten auf andere Art und Weise eliminiert werden. Im Falle einer Tabelle mit einem Primärschlüssel (PK) unterscheiden sich die doppelt oder mehrfach vorhandenen Datensätze durch einen unterschiedlichen PK, ansonsten nicht. Das ist ein guter Ansatz um die Dubletten los zu werden nach der Mimik Self-Join auf alle Spalten außer dem PK und unterschiedlicher PK, aber es gibt auch noch andere Möglichkeiten wie wir sehen werden.

Zur Verdeutlichung erstellen wir ein kleines Beispiel:

  1. create table SIMULACRUM.dbo.dubtab1
  1. (
  1.       tab_id int not null,
  1.       tab_bk nvarchar(20) not null,
  1.       tab_bez nvarchar(50),
  1.       tab_n1 nvarchar(50),
  1.       tab_n2 nvarchar(50)
  1. );
  1.  
  1. ALTER TABLE SIMULACRUM.dbo.dubtab1 ADD CONSTRAINT
  1.       PK_dubtab1 PRIMARY KEY CLUSTERED
  1.       (
  1.       tab_id
  1.       );

In die erstellte Beispieltabelle stellen wir jetzt ein paar Datensätze mit Dubletten ein:

  1. insert into SIMULACRUM.dbo.dubtab1 values (1,'001','Bezeichnung 1','blah1','blubb1');
  1. insert into SIMULACRUM.dbo.dubtab1 values (2,'002','Bezeichnung 2','blah2','blubb2');
  1. insert into SIMULACRUM.dbo.dubtab1 values (3,'003','Bezeichnung 3','blah3','blubb3');
  1. insert into SIMULACRUM.dbo.dubtab1 values (4,'004','Bezeichnung 4','blah4','blubb4');
  1. insert into SIMULACRUM.dbo.dubtab1 values (5,'005','Bezeichnung 5','blah5','blubb5');
  1. insert into SIMULACRUM.dbo.dubtab1 values (6,'001','Bezeichnung 1','blah1','blubb1');
  1. insert into SIMULACRUM.dbo.dubtab1 values (7,'002','Bezeichnung 2','blah2','blubb2');
  1. insert into SIMULACRUM.dbo.dubtab1 values (8,'003','Bezeichnung 3','blah3','blubb3');
  1. insert into SIMULACRUM.dbo.dubtab1 values (9,'004','Bezeichnung 4','blah4','blubb4');
  1. insert into SIMULACRUM.dbo.dubtab1 values (10,'005','Bezeichnung 5','blah5','blubb5');
  1. insert into SIMULACRUM.dbo.dubtab1 values (11,'005','Bezeichnung 5','blah5','blubb5');

Ein SELECT * FROM [SIMULACRUM].[dbo].[dubtab1] order by tab_bk ergibt folgendes erwartungskonformes Ergebnis:

dubletten_pk_1

Wie man sieht ist jeder Datensatz doppelt vorhanden bis auf tab_bk = ‚005‘, der sogar dreifach vorhanden ist, und unterscheidet sich nur durch den unterschiedlichen PK (tab_id).

Eine Möglichkeit die Dubletten zu löschen besteht in der Verwendung des Merge-Befehls:

  1. merge into SIMULACRUM.dbo.dubtab1 as y
  1. using (select     a.tab_id,
  1.             a.tab_bk,
  1.             a.tab_bez,
  1.             a.tab_n1,
  1.             a.tab_n2
  1. from  SIMULACRUM.dbo.dubtab1 as a
  1. inner join SIMULACRUM.dbo.dubtab1 as b on (a.tab_bk = b.tab_bk
  2.  and a.tab_bez = b.tab_bez and a.tab_n1 = b.tab_n1
  3.  and a.tab_n2 = b.tab_n2 and a.tab_id > b.tab_id)
  1. ) as x
  1. on Y.tab_bk = x.tab_bk and y.tab_bez = y.tab_bez
  2.  and y.tab_n1 = x.tab_n1 and y.tab_n2 = x.tab_n2
  3.  and y.tab_id = x.tab_id
  1. when matched then delete;

Im inneren Select-Statement verwenden wir einen Self-Join über alle Spalten und prüfen auf Gleichheit. Von den so gefundenen Datensätzen wird nur der größere Wert für den PK (tab_id) verwendet. dubletten_pk_2 Als Ergebnis erhält man eine Tabelle ohne Dubletten: dubletten_pk_3 Alternativ kann man dieses Szenario auch mit einem Anti-Join angehen:

  1. delete from SIMULACRUM.dbo.dubtab1
  2. where exists (select 1 from SIMULACRUM.dbo.dubtab1 as a
  3.               where a.tab_bk = SIMULACRUM.dbo.dubtab1.tab_bk
  4.                 and a.tab_bez = SIMULACRUM.dbo.dubtab1.tab_bez
  5.                 and a.tab_n1 = SIMULACRUM.dbo.dubtab1.tab_n1
  6.                 and a.tab_n2 = SIMULACRUM.dbo.dubtab1.tab_n2
  7.                 and a.tab_id < SIMULACRUM.dbo.dubtab1.tab_id)

Einfacher wäre es, wenn T-SQL die Verwendung eines Tabellenalias im Delete-Zweig zulassen würde, aber auch so erhalten wir das gewünschte Ergebnis.   Ein wenig anders sieht die Sache aus, wenn die Tabelle, die die Dubletten enthält, keinen Primärschlüssel aufweist. Auch hierzu schnell ein kleines Beispiel:

  1. create table SIMULACRUM.dbo.dubtab2
  1. (
  1.       tab_bk nvarchar(20) not null,
  1.       tab_bez nvarchar(50),
  1.       tab_n1 nvarchar(50),
  1.       tab_n2 nvarchar(50)
  1. );

Und auch hier stellen wir die gleichen Datensätze ein (wenn man einmal vom fehlenden PK absieht):

  1. insert into SIMULACRUM.dbo.dubtab2 values ('001','Bezeichnung 1','blah1','blubb1');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('002','Bezeichnung 2','blah2','blubb2');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('003','Bezeichnung 3','blah3','blubb3');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('004','Bezeichnung 4','blah4','blubb4');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('005','Bezeichnung 5','blah5','blubb5');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('001','Bezeichnung 1','blah1','blubb1');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('002','Bezeichnung 2','blah2','blubb2');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('003','Bezeichnung 3','blah3','blubb3');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('004','Bezeichnung 4','blah4','blubb4');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('005','Bezeichnung 5','blah5','blubb5');
  1. insert into SIMULACRUM.dbo.dubtab2 values ('005','Bezeichnung 5','blah5','blubb5');

Ein select * from SIMULACRUM.dbo.dubtab2 order by tab_bk ergibt folgendes Ergebnis: dubletten_opk_1 Auch hier sind alle Datensätze mindestens doppelt vorhanden, die Datensätze mit tab_bK = ‚005‘ sogar dreifach. Für die Dubletteneliminierung verwenden wir jetzt eine Common Table Expression (CTE):

  1. with x as
  1. (select     row_number() over (partition by tab_bk order by tab_bk) as nr
  1. from  SIMULACRUM.dbo.dubtab2)
  1. delete from x where x.nr > 1

Wieder ergibt sich das gewünschte dublettenfreie Ergebnis: dubletten_opk_2 Diese Art der Dubletteneliminierung ist eleganter als das erste Verfahren unter Verwendung eines Merge-Befehls und lässt sich genauso gut auch für das erste Beispiel verwenden. Streng genommen müsste man aber die partition-by-clause noch um die übrigen Nicht-Schlüsselfelder erweitern um mit Sicherheit in allen Spalten doppelt vorhandene Sätze zu eliminieren. So wie die CTE jetzt formuliert ist, eliminiert sie Sätze schon dann, wenn nur die Spalte tab_bk mehrfach vorhanden ist. Mit dem gleichem Thema angewendet auf sehr große Tabellen befasst sich auch der Beitrag meines Kollegen Hilmar Buchta .

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden