Juli 18, 2020

SQLShack (Deutsch)

In diesem Artikel werden wir diskutieren die SQL-Funktion ROW_NUMBER. Dies ist eine Fortsetzung der SQL Essential-Reihe. In diesem Handbuch werde ich erklären, worum es bei einer Fensterfunktion geht, und Sie werden Beispielbeispiele sehen, um die Konzepte hinter der SQL-Funktion ROW_NUMBER zu verstehen.

Einführung

Am meisten Die in SQL Server häufig verwendete Funktion ist die SQL-Funktion ROW_NUMBER. Die SQL-Funktion ROW_NUMBER ist ab SQL Server 2005 und späteren Versionen verfügbar.

ROW_NUMBER fügt dem Ergebnisraster eine eindeutige inkrementierende Nummer hinzu. Die Reihenfolge, in der die Zeilennummern angewendet werden, wird durch den Ausdruck ORDER BY bestimmt. Meistens werden eine oder mehrere Spalten im ORDER BY-Ausdruck angegeben, es ist jedoch möglich, komplexere Ausdrücke oder sogar eine Unterabfrage zu verwenden. Es wird also ein immer größer werdender Integralwert erstellt, der immer bei 1 beginnt und nachfolgende Zeilen den nächsthöheren Wert erhalten.

Sie können es auch mit einer PARTITION BY-Klausel verwenden. Wenn es jedoch eine Partitionsgrenze oder -grenze überschreitet, setzt es den Zähler zurück und beginnt bei 1. Die Partition kann also die Werte 1, 2, 3 usw. haben, und die zweiten Partitionen starten den Zähler erneut bei 1, 2, 3… und so weiter und so fort.

Grundlagen:

  1. Die SQL-Funktion ROW_NUMBER ist eine nicht persistente Generierung einer Folge temporärer Werte und wird dynamisch berechnet, wenn dann die Abfrage ausgeführt wird.
  2. Es gibt keine Garantie dafür, dass die von einer SQL-Abfrage mit der SQL-Funktion ROW_NUMBER zurückgegebenen Zeilen bei jeder Ausführung genau gleich geordnet werden.
  3. Die Funktionen ROW_NUMBER und RANK sind ähnlich. Die Ausgabe von ROW_NUMBER ist eine Folge von Werten, die bei 1 mit einem Inkrement von 1 beginnt. Während die RANK-Funktion verwendet wird, werden die Werte ebenfalls um 1 erhöht, aber die Werte werden für die Bindungen wiederholt.
  4. Wenn Sie haben Eine Erfahrung mit Oracle, dann ist Ihnen das ROWNUM vertrauter. Es ist eine Pseudosäule. Es beginnt mit 1 und geht bis zum Ende der Tabelle um eins nach unten.
  5. Die SQL-Funktion ROW_NUMBER ist dynamischer Natur und wir können die Werte mithilfe der PARTITION BY-Klausel zurücksetzen.
  6. Die ORDER BY-Klausel der Abfrage und die ORDER BY-Klausel des OVER Klausel haben nichts miteinander zu tun.

Syntax

1
2

ROW_NUMBER ()
OVER (] order_by_clause col1, col2 ..)

ROW_NUMBER

ROW_NUMBER gefolgt von der Funktion OVER Verwenden Sie dann in den Klammern eine ORDER BY-Klausel. Es ist erforderlich, die ORDER BY-Klausel zu verwenden, um eine Art Reihenfolge für die Ergebnismenge festzulegen.

OVER

Die OVER-Klausel definiert das Fenster oder die Reihe von Zeilen, die das Fenster enthält Die Funktion funktioniert, daher ist es sehr wichtig, dass Sie sie verstehen. Die möglichen Komponenten der OVER-Klausel sind ORDER BY und PARTITION BY.

Der ORDER BY-Ausdruck der OVER-Klausel wird unterstützt, wenn die Zeilen auf eine bestimmte Weise ausgerichtet werden müssen, damit die Funktion funktioniert.

1

PARTITION BY value_expression1

PARTITION BY

Die Partition By-Klausel ist optional. Bei Angabe des Werts wird die von der FROM-Klausel erzeugte Ergebnismenge in Partitionen unterteilt, auf die die SQL-Funktion ROW_NUMBER angewendet wird. Die in der PARTITION-Klausel angegebenen Werte definieren die Grenzen der Ergebnismenge. Wenn die PARTITION BY-Klausel nicht angegeben ist, verarbeitet die OVER-Klausel alle Zeilen der Ergebnismenge als einen einzigen Datensatz. Diese Klausel kann aus einer oder mehreren Spalten, einem komplexeren Ausdruck oder sogar einer Unterabfrage bestehen.

order_by_clause

Die Order by-Klausel ist eine obligatorische Klausel. Es bestimmt die Reihenfolge und Zuordnung des temporären Werts zu den Zeilen einer angegebenen Partition. Die ORDER BY-Klausel ist ein Ausdruck der OVER-Klausel und bestimmt, wie die Zeilen für die Funktion auf eine bestimmte Weise ausgerichtet werden müssen.

Demo

In diesem Abschnitt werden wir Werfen Sie einen Blick auf die SQL-Funktion ROW_NUMBER. Für die gesamte Demo habe ich die AdventureWorks2016-Datenbank verwendet.

Verwendung von ROW_NUMBER in SQL-Abfragen

In den folgenden Beispielen wird die Verwendung der OVER-Klausel gezeigt.

Lassen Sie uns die Liste aller Kunden abrufen, indem wir die Spalten wie SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue und RowNum projizieren. Die Funktion Row_Number wird mit der Reihenfolge der Spalte CustomerID angewendet.Der temporäre Wert beginnt mit 1, der basierend auf der Reihenfolge der Kunden-ID zugewiesen wurde, und die Werte werden bis zu den letzten Zeilen der Tabelle fortgesetzt. Die Reihenfolge der Kunden-ID kann nicht garantiert werden, da die ORDER BY-Klausel in der Abfrage nicht angegeben wird.

1
2
3
4
5
6
7
8
9
10
11

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
Zwischensumme,
TotalDue
FROM Sales.SalesOrderHeader;

Verwendung der Order by-Klausel

Im folgenden Beispiel wird die ORDER BY-Klausel in der Abfrage verwendet. Die ORDER BY-Klausel in der Abfrage, die auf die Spalte SalesOrderID angewendet wird. Wir können sehen, dass die Zeilen in der Ausgabe immer noch sortiert und zurückgegeben werden. Die Row_Number wird weiterhin auf die CustomerID angewendet. Die Ausgabe gibt an, dass ORDER BY der Abfrage und ORDER BY der OVER-Klausel unabhängig von der Ausgabe sind.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
Zwischensumme,
TotalDue
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID;

Verwenden mehrerer Spalten mit der OVER-Klausel

Im folgenden Beispiel sehen Sie, dass wir customerID und OrderDate in der ORDER BY-Klausel aufgelistet haben. Dies gibt den Kundendaten die neuesten Bestelldetails zusammen mit der der gesamten Ergebnismenge zugewiesenen Zahlenfolge.

1
2
3
4
5
6
7
8
9
10

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (ORDER BY CustomerID, OrderDate DESC) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
Zwischensumme,
TotalDue
FROM Sales.SalesOrderHeader

Verwenden der SQL-Funktion ROW_NUMBER mit PARTITION

Im folgenden Beispiel wird die Klausel PARTITION BY für die Felder CustomerID und OrderDate verwendet. In der Ausgabe sehen Sie, dass der Kunde 11019 drei Bestellungen für den Monat 2014-Jun hat. In diesem Fall erfolgt die Partition in mehr als einer Spalte.

Die Partition ist eine Kombination aus OrderDate und CustomerID. Die Row_Number beginnt für jede eindeutige Kombination aus OrderDate und CustomerID von vorne. Auf diese Weise ist es einfach, den Kunden zu finden, der am selben Tag mehr als eine Bestellung aufgegeben hat.

1
2
3
4
5
6
7
8
9
10
11
12

USE AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (PARTITION NACH Kunden-ID,
DATEADD (MONAT, DATEDIFF (MONAT, 0, Bestelldatum), 0)
ORDER BY SubTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
Zwischensumme,
TotalDue
FROM Sales.SalesOrderHeader;

Zurückgeben einer Teilmenge von Zeilen mit CTE und ROW_NUMBER

Im folgenden Beispiel wird SalesOrderHeader analysiert, um die fünf größten Bestellungen anzuzeigen, die jeder Kunde je getätigt hat Monat. Mit der Monatsfunktion werden die orderDate-Spalten bearbeitet, um den Monatsteil abzurufen.Auf diese Weise wird der Umsatz, der einem bestimmten Monat (OrderDate) entspricht, zusammen mit dem Kunden (CustomerID) partitioniert.

Um die fünf größten Bestellungen in jedem Monat für jeden Kunden aufzulisten, wird ein CTE verwendet. Auf den Partitionsdaten wird ein Fenster erstellt, dem die Werte zugewiesen werden. Anschließend wird der CTE aufgerufen, um die größten Aufträge abzurufen.

1
2
3
4
5
6
7
8
9
10
11
12

WITH cte
AS (SELECT ROW_NUMBER OVER (TEILUNG NACH Kunden-ID, MONAT (Bestelldatum) ORDER BY SubTotal DESC, TotalDue DESC) AS ROW_NUM,
Kunden-ID,
MONAT (Bestelldatum) Monat,
Zwischensumme,
TotalDue,
Bestelldatum
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5

Zusammenfassung

Bisher haben wir die SQL-Funktion ROW_NUMBER ausführlich überprüft. Wir haben verschiedene Beispiele von einfach bis komplex diskutiert. Außerdem haben wir erläutert, wie die SQL-Funktion ROW_NUMBER mit CTEs (Common Table Expressions) verwendet wird. In den meisten Fällen wird bei jeder Fensterfunktion immer eine Over-Klausel angezeigt.

Die over-Klausel definiert das Fenster, das jede Zeile sieht. Innerhalb der over-Klausel gibt es eine Partition von, die wiederum von jeder Fensterfunktion unterstützt wird, gefolgt von der order by-Klausel. Das ist alles für jetzt … Ich hoffe, Sie haben den Artikel genossen.

  • Autor
  • Letzte Beiträge
Ich bin ein Datenbanktechnologe mit mehr als 11 Jahren Erfahrung in Datenbanktechnologien. Ich bin Microsoft Certified Professional und habe einen Abschluss als Master of Computer Application.
Meine Spezialität ist das Entwerfen von & Implementieren von Hochverfügbarkeitslösungen und plattformübergreifender DB-Migration. Die Technologien, an denen derzeit gearbeitet wird, sind SQL Server, PowerShell, Oracle und MongoDB.
Alle Beiträge von Prashanth Jayaram anzeigen

Neueste Beiträge von Prashanth Jayaram (alle anzeigen)
  • Ein kurzer Überblick über die Datenbankprüfung in SQL – 28. Januar 2021
  • Einrichten der Azure-Datensynchronisierung zwischen Azure SQL-Datenbanken und lokalem SQL Server – 20. Januar 2021
  • Ausführen von Import- / Exportvorgängen für Azure SQL-Datenbanken mit PowerShell – 14. Januar , 2021

admin
0

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Archive

Neueste Beiträge