Grohrock.data

Excel, Datenbanken und mehr

Kategorie: Datenbank

rank() versus row_number()

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.

Reverse-Engineering mit dem SAP PowerDesigner – Spalten mit hoher Kardinalität

Irgendwo zwischen lustig und peinlich rangiert meines Erachtens folgende Problematik. Mit Hilfe des SAP PowerDesigners soll aus einer Datenbank ein Modell erstellt werden und eigentlich klappt auch alles ganz gut, bis auf einmal folgende Fehlermeldung erscheint:

value X out of range for destination

Wobei X einen ziemlich hohen Wert einnimmt. Die Tabelle wird dabei nicht mehr vollständig geladen, heißt man hat zu wenige Spalten im Modell. Was nun?

Zielführend war hier das manuelle Ausführen der Abfrage, die man im PowerDesigner unter „Database > Edit current DBMS“ findet, indem man zur Option „Script\Objects\Column\SqlListQuery“ navigiert. Beim Betrachten der Spalte „coln“ wurde auch schnell klar, woran der Fehler liegt. Die Kardinalität der Problemspalte lag bei etwa 2,8 Milliarden, also jenseits der Grenze eines Signed-Int-Feldes und von daher der Fehler.

Abhilfe schafft hier das Auskommentieren der entsprechenden Abfragesegmente (Vorsicht mit Kommata, aus diesem Grund habe ich es auf dem Bild in die nächste Zeile gerückt und dort auskommentiert):

value X out of range for destination

Vorsicht allerdings, denn ich kann nicht garantieren, dass dadurch alle Funktionen bei der Datenmodellierung erhalten bleiben. Sollte man z. B. über den PowerDesigner einen Index erstellen wollen, kann ich mir gut vorstellen, dass es hier zu Problemen kommt. Für das reine Reverse-Engineering allerdings funktioniert die Methode wunderbar.

Läuft mit WordPress & Theme erstellt von Anders Norén