Daten ordnen und deduplizieren mit ROW_NUMBER()

Bei größeren Datensätzen kann man schnell den Überblick verlieren. Mögliche Mehrfacheinträge (Duplikate) können oftmals im Zuge des “Überfliegens” nicht eindeutig gefunden werden. Diese können später Fehler in der Auswertung verursachen.Durch den SQL-Befehl ROW_NUMBER() ist es in BigQuery möglich, die Ausgabe eines Resultset fortlaufend zu nummerieren. Dies ist auch pro Partition möglich. Mithilfe der Partitionierung können wir mögliche Duplikate identifizieren.Weitere Informationen über die ROW_NUMBER() und z.B. den Unterschied zu RANK() findet man in der Dokumentation.

Syntax

1ROW_NUMBER ()   
2  OVER (
3 	PARTITION BY partition_expression 
4    ORDER BY order_expression [ASC | DESC] 
5  )

Erklärung

Die Expression PARTITION BY gibt die gewünschte Partitionierung an, diese kann auch aus mehreren Spalten bestehen. Möchte man keine Partitionen angeben, so kann man den Ausdruck PARTITION BY weglassen.

Die Expression ORDER BY ist hingegen erforderlich (required). Sie bestimmt die Reihenfolge und weist jeder Partition eindeutige Zeilennummern zu. Durch ASC (ascending order) sortieren wir aufsteigend und durch DESC (descending order) absteigend.

ROW_NUMBER() wird pro Abfrage neu berechnet und ist somit ein temporärer Wert.

Beispiel: Daten ordnen mit ROW_NUMBER()

Betrachten wir folgende Beispieltabelle:

Wir wollen nun pro Land die Personen nach Alter sortieren, hierfür können wir folgende Abfrage verwenden:

1SELECT 
2  *,
3  ROW_NUMBER() OVER(PARTITION BY country ORDER BY age ASC) as rank
4FROM 'beispieltabelle'
5ORDER BY 
6  country, 
7  rank ASC

Wollen wir zusätzlich die Partition pro Stadt hinzufügen, würde die Abfrage sowie das Resultat wie folgt aussehen:

1SELECT 
2  *,
3  ROW_NUMBER() OVER(PARTITION BY country, city ORDER BY age ASC) as rank
4FROM 'beispieltabelle'
5ORDER BY 
6  country, 
7  city, 
8  rank ASC

Beispiel: Daten deduplizieren mit ROW_NUMBER()

Je mehr Partitionen wir einfügen, desto eindeutiger werden unsere Daten. Würden wir die Partition first_name hinzufügen, würde sich nichts ändern, da alle Personen aus Hamburg in unserem Beispiel Hannah heißen. Um herauszufinden, ob es Duplikate in den Daten gibt, muss man daher am Besten unique keys, also eindeutige Felder (z.B. IDs oder hier die E-Mail Adresse) bzw. eine eindeutige Kombination von Feldern auswählen. Felder, die sich gegebenenfalls ändern (z.B. Alter), eignen sich dafür nicht.

Um eine neue Tabelle, ohne die Duplikate, zu erstellen, können wir folgenden Befehl verwenden:

1WITH cte_main AS (
2  SELECT 
3    *,
4    ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email ORDER BY age ASC) as rank
5  FROM 'beispieltabelle'
6)
7SELECT * EXCEPT (rank)  
8FROM cte_main
9WHERE rank = 1

Beispiel: Daten filtern mit QUALIFY

Eine alternative, noch recht neue Möglichkeit zur Datenfilterung ist die Verwendung von QUALIFY.

1SELECT *,
2FROM 'beispieltabelle'
3QUALIFY ROW_NUMBER() 
4  OVER( 
5    PARTITION BY first_name, last_name, email
6    ORDER BY age ASC
7  ) = 1

Wir hoffen, dass euch der Artikel zu dem Befehl ROW_NUMBER() gefallen hat. Bei Fragen hinterlasst gerne einen Kommentar!