NTILE (Transact-SQL)
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.
.gif "Icône Lien de rubrique")
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 RANK 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. Pour plus d'informations, consultez Clause ORDER BY (Transact-SQL). 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.
Exemple
A. Division des lignes en groupes
L'exemple suivant divise les lignes en 4 groupes. Dans la mesure où le nombre total de lignes n'est pas divisible par le nombre de groupes, le premier groupe contient 4 lignes tandis que les autres en possèdent 3 chacun.
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
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 4 groupes à l'intérieur de 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 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
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)
(14 ligne(s) affectée(s))