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.
Transact-SQL-Syntaxkonventionen
Syntax
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
Argumente
integer_expression
Dies ist ein konstanter Ausdruck aus einer positiven ganzen Zahl, der die Anzahl von Gruppen angibt, in die jede Partition unterteilt werden muss. integer_expression kann vom Datentyp int oder bigint sein.<partition_by_clause>
Teilt das von der FROM-Klausel erzeugte Resultset in Partitionen, auf die die 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. 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 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 AdventureWorks2012;
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
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 dem Code aus Beispiel A das PARTITION BY-Argument hinzugefügt. Die Zeilen werden zunächst nach PostalCode partitioniert und dann in vier Gruppen innerhalb jedes PostalCode unterteilt. Im Beispiel wird auch eine @NTILE\_Var-Variable deklariert, die zum Angeben des Werts für den integer_expression-Parameter verwendet wird.
USE AdventureWorks2012;
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
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)