Immer wieder kommt es vor, dass innerhalb von SQL-Anweisungen Duplikate herausgefiltert werden müssen; beispielsweise wenn Daten mit einem gewissen Zeitfenster auch rückwirkend geladen werden (immer die letzten 2 Tage). Theoretisch sollten das mit einem simplen distinct zu lösen sein, allerdings nicht immer und auch nicht fehlerfrei. Was passiert zum Beispiel, wenn die Daten in einem Staging Layer mit Zeitstempel versehen werden und sich in diesem SL die Daten von zwei oder mehr Ladeprozessen kumulieren? Was passiert, wenn fehlerhafte Daten kommen? Prominente Möglichkeiten, solche Duplikate zu filtern sind rank() oder row_number(), aber welche Lösung ist besser und welche schlechter?

Gehen wir von folgendem Beispiel aus:

create table #test (col1 int, col2 varchar(10), datum date);

insert into #test values (342,'Queue_ABC','2016-11-20');
insert into #test values (342,'Queue_JKL','2016-11-20');
insert into #test values (23,'Queue_DEF','2016-11-21');
insert into #test values (23,'Queue_GHI','2016-11-20');

An zwei Tagen wurden Daten in ein Staging Layer geladen und nun soll daraus der Operational Data Store gefüllt werden. Möglichkeit eins, wir erstellen mit rank() eine Rangfolge der Daten und filtern uns dann den höchsten Rang heraus.

select
 col1
 ,col2
 ,datum
 ,rank() over (partition by col1 order by datum desc) as rang
from #test
;

Das Ergebnis:

col1 col2 datum rang
23 Queue_DEF 21.11.2016 1
23 Queue_GHI 20.11.2016 2
342 Queue_ABC 20.11.2016 1
342 Queue_JKL 20.11.2016 1

Wie man sieht, hat das Vorgehen beim Wert 23 funktioniert, aber beim Wert 342 werden wir auch jetzt Duplikate erzeugen, wenn wir mit where rang=1 selektieren. Es spielt hierbei keine Rolle, ob so etwas auch im Normalfall passieren kann oder ob es sich hier einfach um einen Datenfehler handelt. Fakt ist, dass dadurch unter Umständen zu Problemen mit Restriktionen kommen kann. Eine Möglichkeit, dieser Herausforderung zu begegnen ist die Verwendung von row_number() statt rank(), also folgendermaßen:

select
 col1
 ,col2
 ,datum
 ,row_number() over (partition by col1 order by datum desc) as rang
from #test
;

Das Ergebnis:

col1 col2 datum rang
23 Queue_DEF 21.11.2016 1
23 Queue_GHI 20.11.2016 2
342 Queue_ABC 20.11.2016 1
342 Queue_JKL 20.11.2016 2

Nun haben wir tatsächlich nur noch einen einzigen Datensatz mit rang=1 pro Wert in col1. Wichtig hierbei ist, dass weder rank() noch row_number() deterministisch sind. Heißt das für die Wertekombination col1=342 und rang=1 kann das Ergebnis in col2 bei jeder Abfrage variieren.

So ganz rosa ist die Welt natürlich nicht, denn während wir so unseren ODS vor Duplikaten schützen, müssen wir damit leben, dass in so einem Fall die Datenbank für uns bestimmt, welcher Wert im Endeffekt geladen wird.