Wie eine SQL-Fenster verwenden eine Ergebnismenge erstellen

1999: Windows und Fensterfunktionen wurden zum ersten Mal in SQL eingeführt. Mit einem Fenster kann ein Benutzer optional einen Datensatz zu partitionieren, optional auch die Zeilen in jeder Partition bestellen, und geben Sie eine Sammlung von Zeilen (der Fensterrahmen), die mit einer bestimmten Zeile zugeordnet ist.

Der Fensterrahmen einer Zeile R ist eine Teilmenge der Partition enthält, R. Zum Beispiel kann der Fensterrahmen aller Zeilen vom Anfang der Partition bis einschließlich R bestehen kann, auf der Grundlage der Art und Weise Reihen sind in der Trennwand bestellt .

Eine Fensterfunktion berechnet einen Wert für eine Zeile R, basierend auf den Zeilen in dem Fensterrahmen von R.

Beispiel: Angenommen, Sie eine SALES Tabelle, die Spalten von CustID, InvoiceNo und TotalSale hat. Ihr Vertriebsleiter wissen wollen, was die Gesamtverkäufe für jeden Kunden waren über einen bestimmten Bereich der Rechnungsnummern. Sie können erhalten, was sie mit der folgenden SQL-Code will:

SELECT CustID, InvoiceNo, SUM (TotalSale) OVER (PARTITION BY CustIDORDER VON InvoiceNoROWS BETWEENUNBOUNDED PRECEDINGAND CURRENT ROW) FROM SALES-

Das ZU ENDE Klausel legt fest, wie die Zeilen der Abfrage partitioniert werden, bevor sie durch die in diesem Fall in Bearbeitung SUMME Funktion. Eine Partition wird jedem Kunden zugeordnet. Innerhalb jeder Partition eine Liste der Rechnungsnummern sein, und mit jedem von ihnen verbunden sind, werden die Summe aller sein TotalSale des angegebenen Bereichs von Zeilen Werte über, für jeden Kunden.

SQL: 2011 mehrere wichtige Verbesserungen an der ursprünglichen Fenster-Funktionalität, die Aufnahme neuer Keywords hinzugefügt.

Wie ein Fenster in Eimer mit NTILE partitionieren

Das NTILE Fensterfunktion aufteilt in eine positive ganze Zahl ist eine geordnete Fenster Partition n von Eimern, die Nummerierung der Eimer von 1 bis n. Wenn die Anzahl der Zeilen in einer Partition m ist nicht teilbar durch n, dann, nachdem die NTILE Funktion füllt die Eimer gleichmäßig, den Rest m / n, namens r, wird der ersten aufgeteilt r Eimer, so dass sie größer als die anderen Eimer zu machen.

Angenommen, Sie möchten Ihre Mitarbeiter von Gehalt zu klassifizieren, um sie in fünf Eimer Partitionieren vom höchsten zum niedrigsten. Sie können es mit dem folgenden Code tun:

SELECT Vorname, Nachname, NTILE (5) OVER (ORDER BY Gehalt DESC) AS BUCKETFROM Employee-

Wenn es zum Beispiel 11 Mitarbeiter wird jeder Eimer mit zwei mit Ausnahme des ersten Eimer gefüllt, die mit drei gefüllt ist. Der erste Eimer enthält die drei höchsten bezahlten Angestellten und der fünfte Eimer werden die beiden am schlechtesten bezahlten Mitarbeiter enthalten.

Wie zu nisten Fensterfunktionen

Manchmal das Ergebnis zu erhalten Sie brauchen, ist der einfachste Weg, um Nest eine Funktion innerhalb einer anderen. SQL: 2011 hinzugefügt, um die Fähigkeit solche Verschachtelung mit Fensterfunktionen zu tun.

Als Beispiel betrachten wir einen Fall, wo ein Lager Investor zu bestimmen versucht, ob es eine gute Zeit ist, eine bestimmte Aktie zu kaufen. Um den Griff zu bekommen diese, beschließt sie, den aktuellen Aktienkurs, um den Preis zu vergleichen, es auf den unmittelbar vorangegangenen 100 Trades verkauft. Sie fragt sich, wie oft in den vorangegangenen 100 Trades es für weniger als den aktuellen Preis verkauft.

Um eine Antwort zu erreichen, macht sie die folgende Abfrage:

SELECT SaleTime, SUM (CASE WHEN Saleprice 
Das Fenster umfasst die 100 Zeilen die aktuelle Zeile vorangehenden, die den 100 Verkäufe entsprechen unmittelbar vor dem aktuellen Zeitpunkt. Jedes Mal, wenn eine Zeile ausgewertet wird, wenn der Wert von Saleprice ist kleiner als der jüngste Preis, wird 1 zu der Summe addiert.
Das Endergebnis ist eine Zahl, die die Anzahl der Verkäufe aus dem vorherigen hundert erzählt, die zu einem niedrigeren Preis als den aktuellen Preis gemacht wurden.
Wie Gruppen von Zeilen zu bewerten
Manchmal ist die Sortierschlüssel Sie gewählt haben, eine Partition, um Duplikate müssen zu platzieren. Sie können alle Zeilen auswerten wollen, die die gleiche Sortierschlüssel als Gruppe haben. In solchen Fällen können Sie mit dem GRUPPEN Option. Mit ihm können Sie Gruppen von Zeilen zählen, wo die Sortierschlüssel identisch sind.
Hier ein Beispiel:

SELECT CustomerID, Verkaufsdatum, SUM (InvoiceTotal) OVER (PARTITION BY CustomerIDORDER VON SaleDateGROUPS ZWISCHEN 2 vor und 2 nach) FROM Kunden-

Der Fensterrahmen in diesem Beispiel besteht aus bis zu fünf Gruppen von Zeilen: zwei Gruppen vor der Gruppe die aktuelle Zeile enthält, die Gruppe die aktuelle Zeile, und zwei Gruppen im Anschluss an die Gruppe, die die aktuelle Zeile enthält. Die Zeilen in jeder Gruppe haben die gleiche Verkaufsdatum, und das Verkaufsdatum mit jeder Gruppe verbunden ist von dem Verkaufsdatum Werte für die anderen Gruppen.