NTILE (Transact-SQL)
Verteilt die Zeilen in einer sortierten Partition in eine angegebene Anzahl von Gruppen. Die Gruppen sind nummeriert, wobei mit eins begonnen wird. Für jede Zeile gibt NTILE die Nummer der Gruppe zurück, zu der die Zeile gehört.
Syntax
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Argumente
integer_expression
Ein konstanter Ausdruck aus einer positiven ganzen Zahl, der die Anzahl von Gruppen angibt, in die jede Partition unterteilt werden kann. integer_expression kann vom Datentyp int oder bigint sein.<partition_by_clause>
Teilt das von der FROM-Klausel erstellte Resultset in Partitionen, auf die die RANK-Funktion angewendet wird. 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. Weitere Informationen finden Sie unter ORDER BY-Klausel (Transact-SQL). Eine ganze Zahl kann keine Spalte darstellen, wenn < order_by_clause > in einer Rangfolgefunktion verwendet wird.
Rückgabetypen
bigint
Hinweise
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 Mitglied 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 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.
Beispiele
A. Unterteilen von Zeilen in Gruppen
Im folgenden Beispiel werden die Zeilen in vier Gruppen aufgeteilt. Da die Gesamtanzahl von Zeilen nicht durch die Anzahl von Gruppen teilbar ist, enthält die erste Gruppe vier Zeilen und die übrigen Gruppen jeweils drei Zeilen.
USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
Dies 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 AdventureWorks2008R2;
GO
DECLARE @NTileVar int = 4;
SELECT p.FirstName, p.LastName
,NTILE(@NTileVar) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY LastName;
GO
Dies 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)
(14 Zeile(n) betroffen)