NTILE (Transact-SQL)

Article
08/19/2013

Distribue les lignes d'une partition triée dans un nombre spécifié de groupes. Les groupes sont numérotés à partir de un. Pour chaque ligne, NTILE retourne le numéro du groupe auquel la ligne appartient.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

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

Arguments

integer_expression
Expression constante dont la valeur est un entier positif qui spécifie le nombre de groupes utilisés pour diviser chaque partition. integer_expression peut être de type int ou bigint.
<partition_by_clause>
Divise le jeu de résultats généré par la clause FROM en partitions auxquelles la fonction est appliquée. Pour connaître la syntaxe de PARTITION BY, consultez Clause OVER (Transact-SQL).
<order_by_clause>
Détermine l'ordre dans lequel les valeurs de NTILE sont assignées aux lignes d'une partition. Un entier ne peut pas représenter une colonne lorsque <order_by_clause> est utilisé dans une fonction de classement.

Types de retour

bigint

Notes

Si le nombre de lignes d'une partition n'est pas divisible par integer_expression, vous obtiendrez des groupes de deux tailles différentes qui diffèrent d'un membre. Les groupes plus grands viennent avant les groupes plus petits dans l'ordre spécifié par la clause OVER. Par exemple, si le nombre total de lignes est égal à 53 et qu'il y a 5 groupes, les trois premiers groupes contiendront 11 lignes et les deux derniers, 10 lignes. En revanche, si le nombre total de lignes est divisible par le nombre de groupes, les lignes seront réparties équitablement entre les différents groupes. Par exemple, si le nombre total de lignes est égal à 50 et qu'il y a 5 groupes, chacun d'eux contiendra 10 lignes.

Exemples

A.Division des lignes en groupes

L'exemple suivant divise les lignes en quatre groupes d'employés en fonction de leurs ventes de l'année. Dans la mesure où le nombre total de lignes n'est pas divisible par le nombre de groupes, les deux premiers groupes contiennent quatre lignes tandis que les autres en possèdent trois chacun.

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

Voici l'ensemble des résultats.

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.Division du jeu de résultats à l'aide de PARTITION BY

L'exemple suivant ajoute l'argument PARTITION BY au code de l'exemple A. Les lignes sont d'abord partitionnées par PostalCode, puis divisées en quatre groupes dans chaque PostalCode. L'exemple déclare également une variable @NTILE\_Var et utilise cette variable pour spécifier la valeur du paramètre 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

Voici l'ensemble des résultats.

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)