NTILE (Transact-SQL)

Articolo
06/18/2013

Distribuisce le righe di una partizione ordinata in un numero specificato di gruppi. I gruppi sono numerati a partire da 1. Per ogni riga, NTILE restituisce il numero del gruppo a cui appartiene la riga.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

Argomenti

integer_expression
Espressione di costante intera positiva che specifica il numero di gruppi in cui suddividere ogni partizione. integer_expression può essere di tipo int o bigint.
<partition_by_clause>
Suddivide il set di risultati generato dalla clausola FROM in partizioni alle quali viene applicata la funzione. Per la sintassi PARTITION BY, vedere Clausola OVER (Transact-SQL).
<order_by_clause>
Determina l'ordine di assegnazione dei valori NTILE alle righe in una partizione. Una colonna non può essere rappresentata da un valore intero quando si utilizza <order_by_clause> in una funzione di rango.

Tipi restituiti

bigint

Osservazioni

Se il numero di righe in una partizione non è divisibile per integer_expression, verranno creati gruppi di due dimensioni che differiscono per un membro. I gruppi più grandi precedono i gruppi più piccoli nell'ordine specificato dalla clausola OVER. Se ad esempio il numero totale di righe è 53 e il numero di gruppi è 5, i primi 3 gruppi includeranno 11 righe e i 2 gruppi rimanenti 10 righe ognuno. Se invece il numero totale delle righe è divisibile per il numero di gruppi, le righe verranno distribuite uniformemente tra di essi. Se ad esempio il numero totale di righe è 50 e sono disponibili 5 gruppi, ogni gruppo conterrà 10 righe.

Esempi

A.Divisione di righe in gruppi

Nell'esempio seguente vengono divise le righe in quattro gruppi di dipendenti in base alle relative vendite da inizio anno. Poiché il numero totale di righe non è divisibile per il numero di gruppi, i primi due gruppi conterrà 4 righe e gli altri 3 righe ognuno.

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

Set di risultati:

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.Divisione del set di risultati tramite PARTITION BY

Nell'esempio seguente viene aggiunto l'argomento PARTITION BY al codice nell'esempio A. Le righe vengono prima partizionate da PostalCode, quindi divise in quattro gruppi all'interno di ogni PostalCode. Nell'esempio viene inoltre dichiarata una variabile @NTILE\_Var, che verrà utilizzata per specificare il valore del parametro integer_expression.

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

Set di risultati:

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)