NTILE (Transact-SQL)
Gilt für:
SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
Verteilt die Zeilen in einer sortierten Partition in eine angegebene Anzahl von Gruppen. Die Gruppen sind nummeriert. Die Nummerierung beginnt bei 1. Für jede Zeile gibt NTILE die Nummer der Gruppe zurück, der die Zeile angehört.
Transact-SQL-Syntaxkonventionen
Syntax
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Hinweis
Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) und früher finden Sie unter Dokumentation zu früheren Versionen.
Argumente
integer_expression
Dies ist ein Ausdruck aus einer positiven ganzen Zahl, der die Anzahl von Gruppen angibt, in die jede Partition unterteilt werden muss. integer_expression kann vom Typ int oder bigint sein.
<partition_by_clause>
Teilt das von der FROM-Klausel erzeugte Resultset in Partitionen, auf die die Funktion angewendet wird. Weitere Informationen zur Syntax von PARTITION BY finden Sie unter OVER-Klausel (Transact-SQL).
<order_by_clause>
Bestimmt die Reihenfolge, in der die NTILE-Werte den Zeilen in einer Partition zugeordnet werden. Eine ganze Zahl kann keine Spalte darstellen, wenn <order_by_clause> in einer Rangfolgefunktion verwendet wird.
Rückgabetypen
bigint
Bemerkungen
Falls die Anzahl der Zeilen in einer Partition nicht durch den integer_expression-Wert geteilt werden kann, führt dies zu Gruppen mit zwei unterschiedlichen Größen, die sich um ein Element unterscheiden. Größere Gruppen stehen vor kleineren Gruppen in der von der OVER-Klausel angegebenen Reihenfolge. Wenn die Gesamtanzahl der Zeilen z. B. 53 beträgt und fünf Gruppen verwendet werden, enthalten die ersten drei Gruppen 11 Zeilen und die beiden anderen Gruppen jeweils 10 Zeilen. Falls jedoch die Gesamtanzahl der Zeilen durch die Anzahl von Gruppen teilbar ist, werden die Zeilen gleichmäßig auf die Gruppen verteilt. Wenn z. B. insgesamt 50 Zeilen und fünf Gruppen vorhanden sind, enthält jeder Bucket 10 Zeilen.
NTILE ist nicht deterministisch. Weitere Informationen finden Sie unter Deterministic and Nondeterministic Functions.
Beispiele
A. Unterteilen von Zeilen in Gruppen
Im folgenden Beispiel werden Zeilen in vier Gruppen von Mitarbeitern auf Grundlage ihrer Verkaufszahlen des laufenden Jahres unterteilt. Da die Gesamtanzahl der Zeilen nicht durch die Anzahl von Gruppen teilbar ist, enthalten die ersten beiden Gruppe vier Zeilen und die übrigen Gruppen jeweils drei Zeilen.
USE AdventureWorks2022;
GO
SELECT p.FirstName, p.LastName
,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD
, a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
Hier ist das Resultset.
FirstName LastName Quartile SalesYTD PostalCode
------------- --------------------- --------- -------------- ----------
Linda Mitchell 1 4,251,368.55 98027
Jae Pak 1 4,116,871.23 98055
Michael Blythe 1 3,763,178.18 98027
Jillian Carson 1 3,189,418.37 98027
Ranjit Varkey Chudukatil 2 3,121,616.32 98055
José Saraiva 2 2,604,540.72 98055
Shu Ito 2 2,458,535.62 98055
Tsvi Reiter 2 2,315,185.61 98027
Rachel Valdez 3 1,827,066.71 98055
Tete Mensa-Annan 3 1,576,562.20 98055
David Campbell 3 1,573,012.94 98055
Garrett Vargas 4 1,453,719.47 98027
Lynn Tsoflias 4 1,421,810.92 98055
Pamela Ansman-Wolfe 4 1,352,577.13 98027
(14 row(s) affected)
B. Aufteilen des Resultsets mithilfe von PARTITION BY
Im folgenden Beispiel wird das PARTITION BY-Argument dem Code in Beispiel A hinzugefügt. Die Zeilen werden zunächst durch PostalCode partitioniert und anschließend in jedem PostalCode in vier Gruppen aufgeteilt. Im Beispiel wird auch eine @NTILE_Var-Variable deklariert, die zum Angeben des Werts für den integer_expression-Parameter verwendet wird.
USE AdventureWorks2022;
GO
DECLARE @NTILE_Var INT = 4;
SELECT p.FirstName, p.LastName
,NTILE(@NTILE_Var) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS Quartile
,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD
,a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
Hier ist das Resultset.
FirstName LastName Quartile SalesYTD PostalCode
------------ -------------------- -------- ------------ ----------
Linda Mitchell 1 4,251,368.55 98027
Michael Blythe 1 3,763,178.18 98027
Jillian Carson 2 3,189,418.37 98027
Tsvi Reiter 2 2,315,185.61 98027
Garrett Vargas 3 1,453,719.47 98027
Pamela Ansman-Wolfe 4 1,352,577.13 98027
Jae Pak 1 4,116,871.23 98055
Ranjit Varkey Chudukatil 1 3,121,616.32 98055
José Saraiva 2 2,604,540.72 98055
Shu Ito 2 2,458,535.62 98055
Rachel Valdez 3 1,827,066.71 98055
Tete Mensa-Annan 3 1,576,562.20 98055
David Campbell 4 1,573,012.94 98055
Lynn Tsoflias 4 1,421,810.92 98055
(14 row(s) affected)
Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)
C. Unterteilen von Zeilen in Gruppen
Im folgenden Beispiel wird die NTILE-Funktion verwendet, um eine Gruppe von Vertriebsmitarbeitern basierend auf den ihnen zugewiesenen Sollvorgaben für den Verkauf im Jahr 2003 in vier Gruppen zu unterteilen. Da die Gesamtanzahl der Zeilen nicht durch die Anzahl von Gruppen teilbar ist, enthält die erste Gruppe fünf Zeilen, und die übrigen Gruppen enthalten jeweils vier Zeilen.
-- Uses AdventureWorks
SELECT e.LastName, NTILE(4) OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile,
CONVERT (VARCHAR(13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE sq.CalendarYear = 2003
AND SalesTerritoryKey IS NOT NULL AND SalesAmountQuota <> 0
GROUP BY e.LastName
ORDER BY Quartile, e.LastName;
Hier ist das Resultset.
LastName Quartile SalesYTD
----------------- -------- ------------`
Blythe 1 4,716,000.00
Carson 1 4,350,000.00
Mitchell 1 4,682,000.00
Pak 1 5,142,000.00
Varkey Chudukatil 1 2,940,000.00
Ito 2 2,644,000.00
Saraiva 2 2,293,000.00
Vargas 2 1,617,000.00
Ansman-Wolfe 3 1,183,000.00
Campbell 3 1,438,000.00
Mensa-Annan 3 1,481,000.00
Valdez 3 1,294,000.00
Abbas 4 172,000.00
Albert 4 651,000.00
Jiang 4 544,000.00
Tsoflias 4 867,000.00
D: Aufteilen des Resultsets mithilfe von PARTITION BY
Im folgenden Beispiel wird das PARTITION BY-Argument dem Code in Beispiel A hinzugefügt. Die Zeilen werden zunächst durch SalesTerritoryCountry partitioniert und anschließend in jedem SalesTerritoryCountry in vier Gruppen aufgeteilt. Beachten Sie, dass über die Anweisung ORDER BY in der OVER-Klausel NTILE und über die Anweisung SELECT ein Resultset angefordert wird.
-- Uses AdventureWorks
SELECT e.LastName, NTILE(2) OVER(PARTITION BY e.SalesTerritoryKey ORDER BY SUM(SalesAmountQuota) DESC) AS Quartile,
CONVERT (VARCHAR(13), SUM(SalesAmountQuota), 1) AS SalesQuota
,st.SalesTerritoryCountry
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
INNER JOIN dbo.DimSalesTerritory AS st
ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE sq.CalendarYear = 2003
GROUP BY e.LastName,e.SalesTerritoryKey,st.SalesTerritoryCountry
ORDER BY st.SalesTerritoryCountry, Quartile;
Hier ist das Resultset.
LastName Quartile SalesYTD SalesTerritoryCountry
----------------- -------- -------------- ------------------
Tsoflias 1 867,000.00 Australia
Saraiva 1 2,293,000.00 Canada
Varkey Chudukatil 1 2,940,000.00 France
Valdez 1 1,294,000.00 Germany
Alberts 1 651,000.00 NA
Jiang 1 544,000.00 NA
Pak 1 5,142,000.00 United Kingdom
Mensa-Annan 1 1,481,000.00 United States
Campbell 1 1,438,000.00 United States
Reiter 1 2,768,000.00 United States
Blythe 1 4,716,000.00 United States
Carson 1 4,350,000.00 United States
Mitchell 1 4,682,000.00 United States
Vargas 2 1,617,000.00 Canada
Abbas 2 172,000.00 NA
Ito 2 2,644,000.00 United States
Ansman-Wolfe 2 1,183,000.00 United States
Weitere Informationen
RANK (Transact-SQL)
DENSE_RANK (Transact-SQL)
ROW_NUMBER (Transact-SQL)
Rangfolgefunktionen (Transact-SQL)
Integrierte Funktionen (Transact-SQL)
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für