Ein Leitfaden für Anfänger zum Verständnis von SQL-Fensterfunktionen – Teil 2

Im Vorheriger Artikel , haben wir über Fensterfunktionen gesprochen – sie sind ein großartiges Werkzeug zur Lösung verschiedener analytischer Probleme; Sie können Ihnen Zugriff auf Funktionen wie erweiterte Analysen und Datenbearbeitung ermöglichen, ohne dass Sie komplexe Abfragen schreiben müssen. Bevor Sie weiterlesen, würde ich empfehlen, mit dem ersten Teil zu beginnen, da dieser dabei hilft, die Grundidee der Funktionsweise von Fensterfunktionen in SQL zu verstehen.

Da Sie nun mit den Grundlagen vertraut sind, können wir uns mit fortgeschritteneren SQL-Konzepten befassen. Es mag auf den ersten Blick etwas kompliziert klingen, aber ich werde für alle möglichen Fälle einfache Beispiele bereitstellen, die für Anfänger geeignet sind, damit es leicht fällt, meinen Standpunkt zu verstehen.

Inhaltsübersicht

• Cumulative Sum
• Ranking Window Functions
• Use cases
• Offset window functions
• Key Takeaways

Kumulierte Summe

Wir haben bereits Beispiele betrachtet, bei denen der Ausdruck over() entweder keine Parameter oder eine Partitionierung nach Parameter hatte. Nun schauen wir uns den zweiten möglichen Parameter für den Ausdruck over() an – order by.

Lassen Sie uns die Mitarbeiter-ID, den Namen des Mitarbeiters, die Abteilung, das Gehalt und die Summe aller Gehälter anfordern:

 select employee_id, employee_name, department, salary, sum(salary) over() from salary 

Jetzt fügen wir den Parameter „order by “ zum Ausdruck over() hinzu:

 select employee_id, employee_name, department, salary, sum(salary) over(order by employee_id desc) from salary 

Ich denke, wir müssen uns genauer ansehen, was hier passiert ist:

1. Zunächst wird „employee_id“ nun in absteigender Reihenfolge sortiert.

   
   

2. In der Spalte, die sich aus der Anwendung der Fensterfunktion ergibt, befindet sich nun eine kumulierte Summe.

   
   

Ich glaube, Sie kennen die kumulative Summe. Das Wesentliche ist einfach: Die kumulierte Summe oder laufende Summe bedeutet „wie viel bisher“. Die Definition der kumulativen Summe ist die Summe einer gegebenen Folge, die mit mehr Additionen zunimmt oder größer wird.

Folgendes haben wir in unserem Beispiel: Für den Mitarbeiter mit dem höchsten Wert „employee_id“ beträgt das Gehalt 3700, und die Gesamtsumme beträgt ebenfalls 3700. Der zweite Mitarbeiter hat ein Gehalt von 1500, und die Gesamtsumme beträgt 5200. Der dritte Mitarbeiter , mit einem Gehalt von 2900, hat eine Gesamtsumme von 8100 und so weiter.

Der Parameter order by im over() -Ausdruck gibt die Reihenfolge an. Bei aggregierenden Fensterfunktionen bestimmt es die Reihenfolge der kumulierten Summe.

Im over()- Ausdruck können sowohl die Attribute „partition by“ als auch „order by“ angegeben werden.

 select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary 

In diesem Fall wird die kumulierte Summe abschnittsweise berechnet.

Nachdem wir die kumulative Summe besprochen haben, müssen wir sagen, dass es sich möglicherweise um die einzige Art der kumulativen Summe handelt, die häufig verwendet wird. Der kumulative Durchschnitt und die kumulative Anzahl werden hingegen selten verwendet.

Dennoch geben wir ein Beispiel für die kumulative Durchschnittsberechnung – sie gibt uns den Durchschnitt einer Reihe von Werten bis zu einem bestimmten Punkt an:

 select employee_id, employee_name, department, salary, avg(salary) over(order by employee_id desc) from salary 

Ranking-Fensterfunktionen

Wir verwenden die Ranking-Fensterfunktionen, um die Position eines Werts innerhalb einer Wertemenge zu bestimmen. Der ORDER BY- Ausdruck in der OVER- Klausel gibt die Grundlage für die Rangfolge vor, wobei jedem Wert eine Rangfolge innerhalb seiner festgelegten Partition zugewiesen wird. Wenn Zeilen identische Werte für die Rangfolgekriterien aufweisen, wird ihnen der gleiche Rang zugewiesen.

Um zu sehen, wie die Funktionen des Ranking-Fensters funktionieren, fordern wir die folgenden Spalten aus der Gehaltstabelle an: Mitarbeiter-ID, Mitarbeitername, Abteilung und Gehalt:

 select employee_id, employee_name, department, salary from salary 

Jetzt fügen wir mit der Fensterfunktion row_number() over() eine weitere Spalte hinzu:

 select employee_id, employee_name, department, salary, row_number() over() from salary 

Die Fensterfunktion row_number() over() hat den Zeilen Nummern zugewiesen, ohne deren Reihenfolge zu ändern. Bisher bringt uns das nicht viel Wert, oder?

Was aber, wenn wir die Zeilen in absteigender Reihenfolge des Gehalts nummerieren möchten? Um dies zu erreichen, müssen wir die Sortierreihenfolge angeben, d. h. die Reihenfolge nach Parameter an den Ausdruck over() übergeben.

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Zum Vergleich fügen wir der Abfrage die verbleibenden Ranking-Funktionen hinzu:

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc), rank() over(order by salary desc), dense_rank() over(order by salary desc), percent_rank() over(order by salary desc), ntile(5) over(order by salary desc) from salary

Gehen wir die einzelnen Ranking-Fensterfunktionen durch:

1. Die Fensterfunktion row_number() over(Order by Salary Desc) ordnet die Zeilen in absteigender Reihenfolge des Gehalts und weist Zeilennummern zu. Beachten Sie, dass Annie und Tony das gleiche Gehalt haben, ihnen jedoch unterschiedliche Nummern zugewiesen sind.

   
   

2. Die Fensterfunktion rank() over(order by Salary desc ) weist Ränge in absteigender Reihenfolge des Gehalts zu. Es weist identischen Werten den gleichen Rang zu, aber der nächste Wert erhält eine neue Zeilennummer.

   
   

3. Die Fensterfunktion „dense_rank() over(order by Salary desc)“ weist Ränge in absteigender Reihenfolge des Gehalts zu. Es weist identischen Werten den gleichen Rang zu.

   
   

4. Die Fensterfunktion „perzent_rank()“ über (Reihenfolge nach Gehalt desc) ist der relative (prozentuale) Rang der aktuellen Zeile, berechnet nach der Formel: (Rang – 1) / (Gesamtzahl der Zeilen in der Partition – 1).

   
   

5. Die Fensterfunktion ntile(5) over(order by Salary desc) teilt die Anzahl der Zeilen in 5 gleiche Teile und weist jedem Teil eine Zahl zu. Die Anzahl der Teile wird innerhalb der Funktion ntile(5) angegeben.

   
   

Anwendungsfälle

Es ist Zeit, praktische Aufgaben mithilfe der Ranking-Fensterfunktionen zu erkunden. Wir zeigen die Mitarbeiter-ID, den Namen des Mitarbeiters, die Abteilung und das Gehalt an und weisen Zeilennummern in absteigender Reihenfolge des Gehalts zu.

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Manchmal müssen Sie möglicherweise Zeilen in absteigender Reihenfolge des Gehalts innerhalb von Abteilungen (Abschnitten) nummerieren. Dies kann durch Hinzufügen des Attributs „Partition nach“ zum Ausdruck „over()“ erreicht werden:

 select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) from salary 

Machen wir die Aufgabe anspruchsvoller. Wir müssen pro Abteilung nur einen Mitarbeiter mit dem höchsten Gehalt behalten. Dies kann mit einer Unterabfrage erreicht werden:

 select * from ( select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) as rn from salary ) as t2 where rn = 1 

Und noch ein Beispiel: Wenn wir in jeder Stadt drei Mitarbeiter mit dem höchsten Gehalt anzeigen müssen, gehen wir wie folgt vor:

 select * from ( select employee_id, employee_name, city, salary, row_number() over(partition by city order by salary desc) as rn from salary ) as t2 where rn <= 3 

Diese Art von Aufgaben kommen sehr häufig vor, insbesondere wenn Sie eine bestimmte Anzahl von Zeilen innerhalb von Abschnitten (Gruppen) in aufsteigender oder absteigender Reihenfolge eines Attributs anzeigen müssen. In der Praxis verwende ich konsequent die Fensterfunktion row_number() over() und natürlich auch dense_rank() over() .

Offset-Fensterfunktionen

Mit diesen Funktionen können Sie Daten aus anderen Zeilen basierend auf deren Entfernung von der aktuellen Zeile zurückgeben. Um es anschaulicher zu machen, gehen wir die Funktionen first_value(), last_value() und nth_value() durch.

 select t1.*, first_value(salary)over(partition by department), last_value(salary)over(partition by department), nth_value(salary,2)over(partition by department) from salary as t1 order by department 

 select t1.*, first_value(salary)over(partition by department order by salary decs), last_value(salary)over(partition by department order by salary decs), nth_value(salary,2)over(partition by department order by salary decs) from salary as t1 order by department

Die Funktionen first_value(salary) over(partition by Department) und last_value(salary) over(partition by Department) zeigen den ersten und letzten Gehaltswert innerhalb der Sektion (Abteilung) an.

Die Funktion nth_value(salary, 2) over(partition by Department) wiederum zeigt den zweiten Gehaltswert innerhalb der Sektion (Abteilung) an. Bitte beachten Sie, dass in nth_value() ein zusätzliches Argument angegeben wird – die Zeilennummer innerhalb des Abschnitts. In unserem Fall ist die Zeilennummer 2, daher zeigt die Funktion den zweiten Gehaltswert an.

Abgesehen von den oben genannten gibt es auch die Funktionen lag() und Lead() . Die Funktion lag() wird verwendet, um einen Wert aus der Zeile abzurufen, die der aktuellen Zeile vorausgeht. Die Funktion „lead()“ wird verwendet, um einen Wert aus einer Zeile abzurufen, die auf die aktuelle Zeile folgt.

 select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 

Wie Sie sehen können, verschiebt die Funktion lag (salary) over (order by Salary) die Gehälter um eine Zeile nach unten, und die Funktion Lead(salary) over(order by Salary) verschiebt die Gehälter um eine Zeile nach oben. Obwohl diese Funktionen ziemlich ähnlich sind, finde ich es bequemer, lag() zu verwenden.

 select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department 

Hier übernimmt lag() die gleiche Funktion wie zuvor, jedoch nun speziell innerhalb von Abschnitten (Abteilungen).

Die zentralen Thesen

Und zum Schluss noch ein kurzer Überblick über das, was wir heute behandelt haben:

• Die kumulative Summe stellt die laufende Summe einer Sequenz dar und summiert sich mit jeder weiteren Addition.

  
  

• Ranking-Fensterfunktionen werden verwendet, um die Position eines Werts innerhalb einer Reihe von Werten zu bestimmen, wobei der Reihenfolge-nach- Ausdruck die Grundlage für die Rangfolge angibt.

  
  

• Zu den Offset-Fensterfunktionen gehören f irst_value() , last_value() und nth_value() , die das Abrufen von Daten aus anderen Zeilen basierend auf ihrem Abstand von der aktuellen Zeile ermöglichen. Vergessen Sie nicht die Funktionen lag() und Lead() . Die Funktion lag() kann nützlich sein, um Werte aus der Zeile abzurufen, die der aktuellen Zeile vorangeht, während die Funktion Lead() verwendet wird, um Werte aus einer Zeile abzurufen, die auf die aktuelle Zeile folgt.

  
  

Vielen Dank, dass Sie sich mir angeschlossen haben. Ich hoffe, dass dieser Artikel Ihnen hilft, die Fähigkeiten von Fensterfunktionen in SQL besser zu verstehen und Sie bei Routineaufgaben sicherer und schneller zu machen.