SQL NTILE Function

The NTILE function groups rows into a number of parts of equal size.

The parts (or tiles) are numbered, starting at 1.

Further grouping can be applied to groups (partitions) within the rows.

If the row count is not divisible by the number of parts, rows will be distributed unevenly.

Note: The word NTILE is a general form of tertile, quartile, quintile, etc. For example, a population split into three equal parts is divided into tertiles.

Example

#

List the monthly sales for the year 2013.
Partition the list into three equal parts (i.e. tertiles).

SELECT MONTH(OrderDate) AS [Month], 
       SUM(TotalAmount) AS 'Total Sales', 
       NTILE(3) OVER(ORDER BY MONTH(OrderDate)) AS Tertile
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
Result:  12 records
Month Total Sales Tertile
1 66692.80 1
2 41207.20 1
3 39979.90 1
4 55699.39 1
5 56823.70 2
6 39088.00 2
7 55464.93 2
8 49981.69 2
9 59733.02 3
10 70328.50 3
11 45913.36 3
12 77476.26 3

Syntax

#

Syntax of the NTILE function.

NTILE(number) 
  OVER( PARTITION BY expression, ...
        ORDER BY expression, ... )

number -- a positive number that specifies the number of sections the rows will be divided into.

PARTITION BY expression -- optional. Organizes rows into different groups where the NTILE will be applied. If not specified, entire result set represents a single group.

ORDER BY expression -- set the sort order of the result set.

More Examples

NTILE with UNEQUAL DISTRIBUTION

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List all products with prices between $1 and $10. Group the list into four equal parts (i.e. quartiles).
SELECT ProductName, UnitPrice,
       NTILE(4) OVER(ORDER BY UnitPrice) AS Quartile
  FROM Product
 WHERE UnitPrice BETWEEN 1 AND 10
 ORDER BY UnitPrice
Result:  15 records
ProductName UnitPrice Quartile
Geitost 2.50 1
Guaraná Fantástica 4.50 1
Konbu 6.00 1
Filo Mix 6.00 1
Tourtière 7.45 2
Rhönbräu Klosterbier 7.75 2
Tunnbröd 9.00 2
Teatime Chocolate Biscuits 9.20 2
Rogede sild 9.50 3
Zaanse koeken 9.50 3
Jack's New England Clam Chowder 9.65 3
Stroopwafels 9.75 3
Longlife Tofu 10.00 4
Sir Rodney's Scones 10.00 4
Aniseed Syrup 10.00 4

Note: Since the row count is not divisible by the number of groups, the distribution is unequal. The last group does not have the same number of rows as the first three groups.

NTILE with OTHER RANKING FUNCTIONS

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products with prices between $8 and $12. Include some common ranking functions based on price.
SELECT ProductName, UnitPrice,
       ROW_NUMBER() OVER(ORDER BY UnitPrice) AS RowNumber,
       RANK() OVER(ORDER BY UnitPrice) AS Rank,
       DENSE_RANK() OVER(ORDER BY UnitPrice) AS DenseRank,
       NTILE(4) OVER(ORDER BY UnitPrice) AS Quartile
  FROM Product
 WHERE UnitPrice BETWEEN 8 AND 12
 ORDER BY UnitPrice
Result:  10 records
ProductName UnitPrice RowNumber Rank DenseRank Quartile
Tunnbröd 9.00 1 1 1 1
Teatime Chocolate Biscuits 9.20 2 2 2 1
Rogede sild 9.50 3 3 3 1
Zaanse koeken 9.50 4 3 3 2
Jack's New England Clam Chowder 9.65 5 5 4 2
Stroopwafels 9.75 6 6 5 2
Aniseed Syrup 10.00 7 7 6 3
Sir Rodney's Scones 10.00 8 7 6 3
Longlife Tofu 10.00 9 7 6 4
Spegesild 12.00 10 10 7 4

You may also like



Guides


vsn 3.1