Freigeben über


NTILE (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics 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)