SQLShack (Nederlands)
In dit artikel bespreken we de SQL ROW_NUMBER-functie. Dit is een voortzetting van de essentiële SQL-serie. In deze handleiding leg ik uit waar een vensterfunctie over gaat, en zie je voorbeeldvoorbeelden om de concepten achter de SQL ROW_NUMBER-functie te begrijpen.
Inleiding
De meeste veelgebruikte functie in SQL Server is de functie SQL ROW_NUMBER. De functie SQL ROW_NUMBER is beschikbaar vanaf SQL Server 2005 en latere versies.
ROW_NUMBER voegt een uniek oplopend getal toe aan het resultatenraster. De volgorde waarin de rijnummers worden toegepast, wordt bepaald door de ORDER BY-uitdrukking. Meestal worden een of meer kolommen gespecificeerd in de ORDER BY-expressie, maar het is mogelijk om complexere expressies of zelfs een subquery te gebruiken. Het creëert dus een steeds toenemende integrale waarde en begint altijd bij 1 en volgende rijen krijgen de volgende hogere waarde.
U kunt het ook gebruiken met een PARTITION BY-clausule. Maar wanneer het een partitielimiet of -grens overschrijdt, reset het de teller en begint bij 1. De partitie kan dus waarden 1, 2, 3 enzovoort hebben en de tweede partities starten de teller opnieuw vanaf 1, 2, 3 … enzovoort.
Basis:
- De functie SQL ROW_NUMBER is een niet-permanente generatie van een reeks tijdelijke waarden en deze wordt dynamisch berekend wanneer de query vervolgens wordt uitgevoerd.
- Er is geen garantie dat de rijen die worden geretourneerd door een SQL-query met behulp van de SQL ROW_NUMBER-functie, exact hetzelfde worden geordend bij elke uitvoering.
- ROW_NUMBER- en RANK-functies zijn vergelijkbaar. De uitvoer van ROW_NUMBER is een reeks waarden die begint bij 1 met een toename van 1, maar terwijl de RANK-functie, de waarden ook worden verhoogd met 1, maar de waarden worden herhaald voor de banden.
- Als je een ervaring met Oracle, dan is ROWNUM u meer vertrouwd. Het is een pseudo-kolom. Het begint met 1 en gaat steeds verder met één naar beneden, tot aan het einde van de tafel.
- De functie SQL ROW_NUMBER is dynamisch van aard en we mogen de waarden resetten met behulp van de PARTITION BY-clausule
- De ORDER BY-clausule van de query en de ORDER BY-clausule van de OVER clausule hebben niets met elkaar te maken.
Syntaxis
|
1
2
|
ROW_NUMBER ()
OVER (] order_by_clause col1, col2 ..)
|
ROW_NUMBER
ROW_NUMBER gevolgd door OVER-functie en gebruik vervolgens tussen haakjes een ORDER BY-clausule. Het is vereist om de ORDER BY-clausule te gebruiken om een soort volgorde voor de resultaatset op te leggen.
OVER
De OVER-clausule definieert het venster of de reeks rijen die het venster functie werkt, dus het is erg belangrijk dat u het begrijpt. De mogelijke onderdelen van de OVER-clausule zijn ORDER BY en PARTITION BY.
De ORDER BY-expressie van de OVER-clausule wordt ondersteund wanneer de rijen op een bepaalde manier moeten worden opgesteld om de functie te laten werken.
|
1
|
PARTITIE OP waarde_expressie1
|
PARTITION BY
De Partition By-clausule is optioneel. Bij het specificeren van de waarde, wordt de resultatenset die door de FROM-component wordt geproduceerd, verdeeld in partities waarop de functie SQL ROW_NUMBER wordt toegepast. De waarden die in de PARTITION-clausule zijn gespecificeerd, definiëren de grenzen van de resultatenset. Als de PARTITION BY-component niet is opgegeven, werkt de OVER-clausule op alle rijen van de resultatenset als een enkele gegevensset. Deze clausule kan bestaan uit een of meer kolommen, een meer complexe uitdrukking of zelfs een subquery.
order_by_clause
De Order by-clausule is een verplichte clausule. Het bepaalt de volgorde en associatie van de tijdelijke waarde met de rijen van een opgegeven partitie. De ORDER BY-clausule is een uitdrukking van de OVER-clausule en deze bepaalt hoe de rijen op een bepaalde manier moeten worden opgesteld voor de functie.
Demo
In deze sectie bespreken we Ik zal de functie SQL ROW_NUMBER eens bekijken. Voor de hele demo heb ik de AdventureWorks2016-database gebruikt.
Hoe ROW_NUMBER te gebruiken in SQL Query
In de volgende voorbeelden zullen we het gebruik van de OVER-clausule zien.
Laten we de lijst van alle klanten krijgen door de kolommen zoals SalesOrderID, OrderDate, SalesOrderNumber, SubTotal, TotalDue en RowNum te projecteren. De functie Row_Number wordt toegepast met de volgorde van de kolom CustomerID.De tijdelijke waarde begint bij 1 toegewezen op basis van de volgorde van de CustomerID, en de waarden worden voortgezet tot de laatste rijen van de tabel. De volgorde van CustomerID is niet gegarandeerd omdat we de ORDER BY-clausule niet in de query specificeren.
|
1
2
3
4
5
6
7
8
9
10
11
|
GEBRUIK AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader;
|
Hoe de Order by-clausule te gebruiken
Het volgende voorbeeld gebruikt de ORDER BY-clausule in de query. De ORDER BY-component in de query die is toegepast op de SalesOrderID-kolom. We kunnen zien dat de rijen in de uitvoer nog steeds worden geordend en geretourneerd. Het Row_Number wordt nog steeds toegepast op de CustomerID. De output geeft aan dat de ORDER BY van de query en de ORDER BY van de OVER-clausule onafhankelijk zijn van de output.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
GEBRUIK AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (
ORDER BY CustomerID) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
BESTEL OP SalesOrderID;
|
Hoe gebruik je meerdere kolommen met de OVER-clausule
In het volgende voorbeeld kun je zien dat we customerID en OrderDate hebben vermeld in de ORDER BY-clausule. Dit geeft de klantgegevens met de meest recente bestelgegevens samen met de reeks nummers die aan de gehele resultaatset is toegewezen.
|
1
2
3
4
5
6
7
8
9
10
|
GEBRUIK AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (ORDER BY CustomerID, OrderDate DESC) AS RowNum,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotal,
TotalDue
FROM Sales.SalesOrderHeader
|
Hoe de SQL ROW_NUMBER-functie te gebruiken met PARTITION
In het volgende voorbeeld wordt de PARTITION BY-clausule gebruikt voor de velden CustomerID en OrderDate. In de uitvoer kunt u zien dat de klant 11019 drie bestellingen heeft voor de maand 2014-juni. In dit geval wordt de partitie op meer dan één kolom uitgevoerd.
De partitie is een combinatie van OrderDate en CustomerID. Het rijnummer begint opnieuw voor elke unieke combinatie van OrderDate en CustomerID. Op deze manier is het gemakkelijk om de klant te vinden die op dezelfde dag meer dan één bestelling heeft geplaatst.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
GEBRUIK AdventureWorks2016;
GO
SELECT ROW_NUMBER () OVER (PARTITION BY CustomerID,
DATEADD (MONTH, DATEDIFF (MONTH, 0, OrderDate), 0)
ORDER BY SubTotal DESC) AS MonthlyOrders,
CustomerID,
SalesOrderID,
OrderDate,
SalesOrderNumber,
SubTotaal,
TotalDue
VAN Sales.SalesOrderHeader;
|
Een subset rijen retourneren met CTE en ROW_NUMBER
In het volgende voorbeeld gaan we SalesOrderHeader analyseren om de top vijf van grootste bestellingen weer te geven die elke klant elke keer heeft geplaatst maand. Met de functie Maand worden de kolommen orderDate gemanipuleerd om het maandgedeelte op te halen.Op deze manier wordt de verkoop die overeenkomt met de specifieke maand (OrderDate) samen met de klant (CustomerID) verdeeld.
Om voor elke klant de vijf grootste bestellingen in elke maand weer te geven, wordt een CTE gebruikt. Er wordt een venster gemaakt op de partitiegegevens en het wordt toegewezen met de waarden en vervolgens wordt de CTE aangeroepen om de grootste bestellingen op te halen.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
WITH cte
AS (SELECT ROW_NUMBER OVER (PARTITION BY customerID, MONTH (OrderDate) ORDER BY SubTotal DESC, TotalDue DESC) AS ROW_NUM,
CustomerID,
MONTH (OrderDate) Month,
SubTotal,
TotalDue,
OrderDate
FROM Sales.SalesOrderHeader
)
SELECT *
FROM cte
WHERE ROW_NUM < = 5
|
Samenvatting
Tot dusver hebben we de functie SQL ROW_NUMBER in detail bekeken. We hebben verschillende voorbeelden besproken, van eenvoudig tot complex. We hebben ook besproken hoe je de SQL ROW_NUMBER-functie kunt gebruiken met CTEs (Common Table Expressions). In de meeste gevallen ziet u altijd een over-clausule bij elke vensterfunctie.
De over-clausule definieert het venster dat elke rij ziet. Binnen de over-clausule is er een partitie door, opnieuw wordt deze ondersteund door elke vensterfunctie, gevolgd door de order-by-clausule. Dat is alles voor nu … Ik hoop dat je het artikel met plezier hebt gelezen.
- Auteur
- Recente berichten
Mijn specialiteit ligt in het ontwerpen van & het implementeren van oplossingen voor hoge beschikbaarheid en platformonafhankelijke DB-migratie. De technologieën waar momenteel aan wordt gewerkt zijn SQL Server, PowerShell, Oracle en MongoDB.
Bekijk alle berichten van Prashanth Jayaram
- Een kort overzicht van database-audit in SQL – 28 januari 2021
- Azure Data Sync instellen tussen Azure SQL-databases en on-premises SQL Server – 20 januari 2021
- Azure SQL-database import- / exportbewerkingen uitvoeren met PowerShell – 14 januari , 2021