Redgate Hub (Deutsch)

Ich habe die letzten sechs Jahre damit verbracht, in den USA zu reisen und Datenbankexperten an SQL-Samstagen und anderen Veranstaltungen über T-SQL-Fensterfunktionen zu informieren. Ich bin erstaunt, wie wenige Leute von diesen Funktionen gehört haben und noch weniger, die sie verwenden. Am Ende jeder Präsentation kommen eine oder mehrere Personen zu dem Schluss, dass sie sich wünschten, sie hätten diese Funktionen Jahre zuvor kennengelernt, weil sie für so viele Abfragen von Vorteil gewesen sein könnten.,

Diese Funktionen wurden gefördert, um die Leistung gegenüber anderen, traditionelleren Methoden zu verbessern. Ich stimme teilweise zu. Sie erleichtern das Schreiben vieler Abfragen und verbessern manchmal die Leistung.

Nichts mit dem Windows-Betriebssystem zu tun

Diese Funktionen sind Teil der ANSI SQL 2003-Standards und im Fall von SQL Server T-SQL-Funktionen zum Schreiben von Abfragen. Sie haben nichts mit dem Windows-Betriebssystem oder API-Aufrufen zu tun. Andere Datenbanksysteme wie Oracle haben diese ebenfalls als Teil ihrer eigenen SQL-Sprache aufgenommen.,

Window (auch windowing oder windowed) Funktionen führen eine Berechnung über eine Reihe von Zeilen durch. Ich denke gerne daran, die zurückgegebenen Zeilen“ durch das Fenster zu schauen “ und eine letzte Chance zu haben, eine Berechnung durchzuführen. Das Fenster wird durch die OVER-Klausel definiert, die bestimmt, ob die Zeilen in kleinere Mengen partitioniert werden und ob sie geordnet sind. Wenn Sie eine Fensterfunktion verwenden, verwenden Sie immer eine OVER-Klausel. Die OVER-Klausel ist auch Teil des NÄCHSTEN WERTS FÜR die Syntax, die für das Sequence-Objekt erforderlich ist, andernfalls wird sie jedoch mit window-Funktionen verwendet.,

Die OVER-Klausel kann eine PARTITION BY-Option enthalten. Dadurch werden die Zeilen in kleinere Mengen aufgeteilt. Sie denken vielleicht, dass dies dasselbe ist wie GROUP BY, aber nicht. Beim Gruppieren wird eine Zeile pro eindeutiger Gruppe zurückgegeben. Bei Verwendung von PARTITION BY werden alle Detailzeilen zusammen mit den Berechnungen zurückgegeben. Wenn Sie ein Fenster in Ihrem Haus haben, das in Scheiben unterteilt ist, ist jeder Bereich ein Fenster. Wenn Sie über Fensterfunktionen nachdenken, ist der gesamte Satz von Ergebnissen eine Partition, aber wenn Sie PARTITION BY verwenden, kann jede Partition auch als Fenster betrachtet werden., PARTITION BY wird für alle Fensterfunktionen unterstützt – und optional.

Die OVER-Klausel kann auch eine ORDER BY-Option enthalten. Dies ist unabhängig von der ORDER BY-Klausel der Abfrage. Einige der Funktionen erfordern ORDER BY und werden von den anderen nicht unterstützt. Wenn die Reihenfolge der Zeilen beim Anwenden der Berechnung wichtig ist, ist die REIHENFOLGE NACH erforderlich.

Fensterfunktionen dürfen nur in den SELECT-und ORDER BY-Klauseln einer Abfrage verwendet werden. Sie werden nach jedem Verbinden, Filtern oder Gruppieren angewendet.,

Ranking-Funktionen

Die am häufigsten verwendeten Fensterfunktionen, Ranking-Funktionen, sind seit 2005 verfügbar. Zu diesem Zeitpunkt führte Microsoft ROW_NUMBER, RANK, DENSE_RANK und NTILE ein. ROW_NUMBER wird sehr häufig verwendet, um einer Partition oder der gesamten Ergebnismenge eindeutige Zeilennummern hinzuzufügen. Das Hinzufügen einer Zeilennummer oder einer der anderen Ranking-Funktionen ist normalerweise nicht das Ziel, aber es ist ein Schritt auf dem Weg zur Lösung.

ORDER BY ist in der OVER-Klausel erforderlich, wenn ROW_NUMBER und die anderen Funktionen in dieser Gruppe verwendet werden., Dies teilt der Datenbank-Engine die Reihenfolge mit, in der die Zahlen angewendet werden sollen. Wenn die Werte der Spalten oder Ausdrücke, die in ORDER BY verwendet werden, nicht eindeutig sind, befassen sich RANK und DENSE_RANK mit den Bindungen, während ROW_NUMBER sich nicht um Bindungen kümmert. NTILE wird verwendet, um die Zeilen basierend auf der REIHENFOLGE NACH in Buckets zu unterteilen.

Ein Vorteil von ROW_NUMBER ist die Möglichkeit, nicht eindeutige Zeilen in eindeutige Zeilen umzuwandeln. Dies könnte beispielsweise verwendet werden, um doppelte Zeilen zu eliminieren.

Um zu zeigen, wie dies funktioniert, beginnen Sie mit einer temporären Tabelle, die doppelte Zeilen enthält., Der erste Schritt besteht darin, die Tabelle zu erstellen und zu füllen.

Durch Hinzufügen von ROW_NUMBER und Partitionierung durch jede Spalte werden die Zeilennummern für jeden eindeutigen Satz von Zeilen neu gestartet. Sie können die eindeutigen Zeilen identifizieren, indem Sie diejenigen mit einer Zeilennummer gleich eins finden.,

Jetzt müssen Sie nur noch Zeilen löschen, deren Zeilennummer größer als eins ist., Das Problem ist, dass Sie der WHERE Klausel keine Fensterfunktionen hinzufügen können.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Sie können dann die Zeilen direkt aus dem CTE löschen.

Erfolg! Die zusätzlichen Zeilen wurden gelöscht und es verbleibt ein eindeutiger Satz von Zeilen.

Um den Unterschied zwischen ROW_NUMBER, RANK und DENSE_RANK zu sehen, führen Sie diese Abfrage aus:

Die ORDER BY für jede OVER-Klausel ist OrderDate, die nicht eindeutig ist. Dieser Kunde hat am 24.10.2013 zwei Bestellungen aufgegeben. ROW_NUMBER hat gerade die Zuweisung von Nummern fortgesetzt und nichts anderes getan, obwohl es ein doppeltes Datum gibt., RANG zugewiesen 6 zu beiden Zeilen und dann gefangen bis zu ROW_NUMBER mit einem 8 in der nächsten Zeile. DENSE_RANK hat den beiden Zeilen auch 6 zugewiesen,der folgenden Zeile jedoch 7.

Zwei erklären den Unterschied, stellen Sie sich ROW_NUMBER als positional vor. RANG ist sowohl positionell als auch logisch. Diese beiden Zeilen werden logisch gleich eingestuft, aber die nächste Zeile wird nach der Position in der Menge sortiert. DENSE_RANK ordnet sie logisch ein. Bestellung 2013-11-04 ist das 7. einzigartige Datum.

Die letzte Funktion in dieser Gruppe heißt NTILE. Es weist den Zeilen Bucket-Nummern anstelle von Zeilennummern oder Rängen zu., Hier ist ein Beispiel:

NTILE hat einen Parameter, in diesem Fall 4, der die Anzahl der Buckets ist, die Sie in den Ergebnissen sehen möchten. Die ORDER BY wird auf die Summe der Verkäufe angewendet. Den Zeilen mit den niedrigsten 25% wird 1 zugewiesen, den Zeilen mit den höchsten 25% werden 4 zugewiesen. Schließlich werden die Ergebnisse von NTILE mit 1000 multipliziert, um den Bonusbetrag zu erhalten. Da 14 nicht gleichmäßig durch 4 geteilt werden kann, geht eine zusätzliche Reihe in jeden der ersten beiden Eimer.

Fensteraggregate

Fensteraggregate wurden ebenfalls mit SQL Server 2005 eingeführt., Diese machen das Schreiben einiger kniffliger Abfragen einfach, führen jedoch häufig eine schlechtere Leistung als ältere Techniken aus. Sie ermöglichen es Ihnen, Ihre bevorzugte Aggregatfunktion zu einer nicht aggregierten Abfrage hinzuzufügen. Angenommen, Sie möchten beispielsweise alle Kundenaufträge zusammen mit der Zwischensumme für jeden Kunden anzeigen., Durch das hinzufügen einer SUMME über die OVER-Klausel, Sie können dies sehr einfach:

Durch hinzufügen der PARTITION, die ein Teilergebnis berechnet für jeder Kunde. Jede Aggregatfunktion kann verwendet werden, und ORDER BY in der OVER-Klausel wird nicht unterstützt.

Fensteraggregatverbesserungen 2012

Ab 2012 können Sie der OVER-Klausel eine ORDER BY zu Fensteraggregaten hinzufügen, um beispielsweise laufende Summen und gleitende Durchschnitte zu erzeugen. Gleichzeitig führte Microsoft das Konzept des Framing ein. Hinzufügen einer PARTITION DURCH ist wie das Teilen eines Fensters in Scheiben., Das Hinzufügen von Rahmen ist wie das Erstellen eines Buntglasfensters. Jede Zeile hat ein einzelnes Fenster, in dem der Ausdruck angewendet wird.

Mit dieser Erweiterung können Sie laufende Summen erstellen, auch ohne die Framing-Syntax hinzuzufügen., Here is an example that returns a running total by customer:

Der Standardrahmen, der verwendet wird, wenn kein Rahmen angegeben ist, liegt IM BEREICH ZWISCHEN UNBEGRENZTER VORHERIGER UND AKTUELLER ZEILE. Leider funktioniert dies nicht so gut, wie wenn Sie stattdessen diesen Frame angeben: ZEILEN ZWISCHEN UNBEGRENZTER VORHERIGER UND AKTUELLER ZEILE. Der Unterschied sind die Wortzeilen. RANGE ist zu diesem Zeitpunkt nur teilweise implementiert und für die Arbeit mit Zeiträumen gedacht, während ROWS positional ist., Der Rahmen, ZEILEN ZWISCHEN DER VORHERIGEN UND der AKTUELLEN ZEILE, bedeutet, dass das Fenster aus der ersten Zeile der Partition und allen Zeilen bis zur aktuellen Zeile besteht. Jede Berechnung erfolgt über einen anderen Satz von Zeilen. Bei der Berechnung für Zeile 4 werden beispielsweise die Zeilen 1 bis 4 verwendet.

Bei der Berechnung für Zeile 5 sind die Zeilen 1 bis 5. Das Fenster wird größer, wenn Sie von einer Zeile zur nächsten wechseln.,

Sie können auch die Syntaxzeilen ZWISCHEN N VORHERGEHENDEN UND AKTUELLEN ZEILEN oder ZEILEN ZWISCHEN AKTUELLEN ZEILEN UND N FOLGENDEN verwenden. Dies könnte beispielsweise für die Berechnung eines gleitenden Dreimonatsdurchschnitts nützlich sein. Die folgende Abbildung stellt ZEILEN ZWISCHEN DER VORHERGEHENDEN UND DER AKTUELLEN ZEILE dar.

Wenn 5 die aktuelle Zeile ist, bewegt sich das Fenster; es ändert seine Größe nicht.,

Hier ist die Liste der Begriffe, die Sie beim Schreiben der Framing-Option wissen müssen:

Ich gebe zu, dass diese Syntax etwas verwirrend ist, aber die Verwendung der SQL-Eingabeaufforderung erleichtert das Schreiben der Framing-Option!

Offset-Funktionen

Ebenfalls in der Version von SQL Server 2012 sind vier Funktionen enthalten, mit denen Sie Werte aus anderen Zeilen einfügen können – ohne einen Self-Join durchzuführen. Microsoft nennt diese „Analysefunktionen“, aber ich beziehe mich bei der Präsentation zu diesem Thema immer auf sie als „Offset-Funktionen“., Mit zwei Funktionen können Sie Spalten oder Ausdrücke aus einer Zeile vor (LAG) oder nach (LEAD) der aktuellen Zeile ziehen. Mit den beiden anderen Funktionen können Sie Werte aus der ersten Zeile der Partition (FIRST_VALUE) oder der letzten Zeile der Partition (LAST_VALUE) zurückgeben. FIRST_VALUE und LAST_VALUE erfordern auch Framing, also achten Sie darauf, den Rahmen einzuschließen, wenn Sie diese Funktionen verwenden. Alle vier Funktionen erfordern die ORDER BY-Option der OVER-Klausel. Das ist sinnvoll, da die Datenbank-Engine die Reihenfolge der Zeilen kennen muss, um herauszufinden, welche Zeile den zurückzugebenden Wert enthält.,

Manche Leute haben eine Lieblingsband; manche Leute haben einen Lieblingsfilm. Ich habe eine Lieblingsfunktion-VERZÖGERUNG. Es ist einfach zu bedienen (kein Rahmen!) und führt groß., Here is an example:

LAG und zu FÜHREN, erfordert ein argument – die Spalte oder Ausdruck, den Sie zurückgeben möchten. Standardmäßig gibt LAG den Wert aus der vorherigen Zeile und LEAD den Wert aus der folgenden Zeile zurück. Sie können dies ändern, indem Sie einen Wert für den OFFSET-Parameter angeben, der standardmäßig 1 ist. Beachten Sie, dass die erste Zeile der Partition NULL zurückgibt. Wenn Sie die Nullen überschreiben möchten, können Sie einen Standardwert angeben., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE und LAST_VALUE kann verwendet werden, zu finden, einen Wert aus der ersten Zeile oder Letzte Zeile der partition an. Stellen Sie sicher, dass Sie den Frame nicht nur aus Leistungsgründen angeben, sondern weil der Standardrahmen nicht wie erwartet mit LAST_VALUE. Der Standardrahmen, BEREICH ZWISCHEN UNBEGRENZTER VORHERIGER UND AKTUELLER ZEILE, geht nur bis zur aktuellen Zeile. Die letzte Zeile der Partition ist nicht enthalten., Um die erwarteten Ergebnisse zu erhalten, müssen Sie bei Verwendung von LAST_VALUE ZEILEN ZWISCHEN DER AKTUELLEN ZEILE UND der UNBEGRENZTEN FOLGENDEN angeben., Hier ist ein Beispiel für die Verwendung FIRST_VALUE:

Statistische Funktionen

Microsoft gruppiert diese vier Funktionen – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – zusammen mit den Offset-Funktionen, die alle acht Analysefunktionen aufrufen. Da ich diese gerne von den Offset-Funktionen unterscheide, nenne ich diese statistisch.

PERCENT_RANK und CUME_DIST stellen eine Rangfolge für jede Zeile über einer Partition bereit. Sie unterscheiden sich leicht. PERCENT_RANK gibt den Prozentsatz der Zeilen zurück, die unter der aktuellen Zeile liegen., „Meine Punktzahl ist höher als 90% der Punkte.“CUME_DIST oder kumulative Verteilung gibt den genauen Rang zurück. „Meine Punktzahl liegt bei 90% der Punktzahlen.“Hier ist ein Beispiel mit der durchschnittlichen hohen Temperatur in St. Louis für jeden Monat. Beachten Sie, dass die Ränge durch die Fahrenheit Temperatur bestimmt wurden.

Die Ränge werden nicht durch die relativen Werte bestimmt, sondern durch die Positionen der Zeilen. Beachten Sie, dass März und November die gleiche durchschnittliche Hochtemperatur haben, also wurden sie gleich eingestuft.

Sie fragen sich vielleicht, wie Sie PERCENT_RANK und CUME_DIST berechnen., Hier sind die Formeln:

PERCENTILE_DISC und PERCENTILE_CONT arbeiten umgekehrt. Bei einem Prozentrang finden Sie den Wert in diesem Rang., Sie unterscheiden sich darin, dass PERCENTILE_DISC einen Wert zurückgibt, der in der Menge vorhanden ist, während PERCENTILE_CONT einen genauen Wert berechnet, wenn keiner der Werte in der Menge genau auf diesen Rang fällt. Sie können PERCENTILE_CONT verwenden, um einen Median zu berechnen, indem Sie 0.5 als Prozentrang angeben. Zum Beispiel, welche Temperatur liegt bei 50% in St. Louis?

Die Funktion PERCENTILE_CONT nimmt den Durchschnitt der beiden Werte an, die der Mitte am nächsten sind, 67 und 69, und gemittelt sie. PERCENTILE_DISC gibt einen genauen Wert zurück, 67., Beachten Sie auch, dass diese beiden Funktionen eine zusätzliche Klausel haben, die in den anderen Funktionen INNERHALB der GRUPPE nicht zu sehen ist und die ORDER BY anstelle der OVER-Klausel enthält.

Zusammenfassung

Dieser Artikel ist ein sehr schneller Überblick über T-SQL Fensterfunktionen. Zwei Arten von Funktionen wurden mit SQL Server 2005 veröffentlicht, die Ranking-Funktionen und Fensteraggregate. Mit 2012 haben Sie Window Aggregate um Framing und die Analysefunktionen erweitert. Ich möchte die Analysefunktionen in zwei Gruppen unterteilen, die Offset-und Statistikfunktionen., Fensterfunktionen erleichtern das Schreiben vieler Abfragen, und ich glaube, das ist der Hauptvorteil. In einigen Fällen werden die Abfragen auch besser funktionieren, aber das ist eine Diskussion für einen anderen Tag.

ich hoffe, dieser Artikel hat Sie dazu inspiriert, mehr über diese fantastischen Funktionen!