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 entweder keine Parameter oder eine Partitionierung nach Parameter hatte. Nun schauen wir uns den zweiten möglichen Parameter für den Ausdruck an – over() over() 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 “ zum Ausdruck hinzu: „order by over() 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: Zunächst wird nun in absteigender Reihenfolge sortiert. „employee_id“ 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 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. „employee_id“ Der Parameter order by im -Ausdruck gibt die Reihenfolge an. Bei aggregierenden Fensterfunktionen bestimmt es die Reihenfolge der kumulierten Summe. over() Im Ausdruck können sowohl die Attribute „partition by“ als auch „order by“ angegeben werden. over()- 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. ACHTUNG! Wenn beide Attribute im Ausdruck angegeben sind, steht die Partition by immer an erster Stelle, gefolgt von der . Zum Beispiel: . over()- Reihenfolge by over(partition by Department order by Employee_id) 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 Ausdruck in der 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. ORDER BY- OVER- 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 eine weitere Spalte hinzu: row_number() over() select employee_id, employee_name, department, salary, row_number() over() from salary Die Fensterfunktion hat den Zeilen Nummern zugewiesen, ohne deren Reihenfolge zu ändern. Bisher bringt uns das nicht viel Wert, oder? row_number() over() 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: Die Fensterfunktion 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. row_number() over(Order by Salary Desc) Die Fensterfunktion ) 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. rank() over(order by Salary desc Die Fensterfunktion weist Ränge in absteigender Reihenfolge des Gehalts zu. Es weist identischen Werten den gleichen Rang zu. „dense_rank() over(order by Salary desc)“ Die Fensterfunktion ist der relative (prozentuale) Rang der aktuellen Zeile, berechnet nach der Formel: (Rang – 1) / (Gesamtzahl der Zeilen in der Partition – 1). „perzent_rank()“ über (Reihenfolge nach Gehalt desc) Die Fensterfunktion teilt die Anzahl der Zeilen in 5 gleiche Teile und weist jedem Teil eine Zahl zu. Die Anzahl der Teile wird innerhalb der Funktion angegeben. ntile(5) over(order by Salary desc) ntile(5) ACHTUNG! Im Gegensatz zu Aggregatfunktionen, z. B. sum(salary), nehmen Ranking-Funktionen, z. B. row_number(), keine Spalte darin auf. In der Funktion ntile(5) wird jedoch die Anzahl der Teile 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 und natürlich auch . row_number() over() 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 ACHTUNG! In allen drei Fensterfunktionen ist dies der Fall essentiell um die Partition per Parameter anzugeben. Der Parameter „Reihenfolge nach“ ist nicht obligatorisch, aber durch seine Angabe können Sie die Reihenfolge der Zeilen innerhalb der Partition ändern. In der folgenden Abfrage haben wir beispielsweise nach Gehalt innerhalb der Sektion (Abteilung) sortiert, und jetzt ist der erste_Wert das höchste Gehalt in der Sektion. 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 und zeigen den ersten und letzten Gehaltswert innerhalb der Sektion (Abteilung) an. first_value(salary) over(partition by Department) last_value(salary) over(partition by Department) Die Funktion wiederum zeigt den zweiten Gehaltswert innerhalb der Sektion (Abteilung) an. Bitte beachten Sie, dass in 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. nth_value(salary, 2) over(partition by Department) nth_value() Abgesehen von den oben genannten gibt es auch die Funktionen und . Die Funktion 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. lag() Lead() lag() 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 die Gehälter um eine Zeile nach unten, und die Funktion verschiebt die Gehälter um eine Zeile nach oben. Obwohl diese Funktionen ziemlich ähnlich sind, finde ich es bequemer, zu verwenden. lag (salary) over (order by Salary) Lead(salary) over(order by Salary) lag() ACHTUNG! Für diese Funktionen ist es zwingend erforderlich, den Parameter „Order by“ im Ausdruck over() anzugeben. Sie können die Partitionierung auch mithilfe von „partition by“ angeben, dies ist jedoch nicht obligatorisch. select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department Hier übernimmt die gleiche Funktion wie zuvor, jedoch nun speziell innerhalb von Abschnitten (Abteilungen). lag() 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 Ausdruck die Grundlage für die Rangfolge angibt. Reihenfolge-nach- Zu den Offset-Fensterfunktionen gehören f , und , die das Abrufen von Daten aus anderen Zeilen basierend auf ihrem Abstand von der aktuellen Zeile ermöglichen. Vergessen Sie nicht die Funktionen und . Die Funktion kann nützlich sein, um Werte aus der Zeile abzurufen, die der aktuellen Zeile vorangeht, während die Funktion verwendet wird, um Werte aus einer Zeile abzurufen, die auf die aktuelle Zeile folgt. irst_value() last_value() nth_value() lag() Lead() lag() Lead() 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.
