NTILE function
The NTILE function is an OLAP ranking function that classifies the rows in each partition into N ranked categories, called tiles, where each category includes an approximately equal number of rows.
Syntax
NTILE function |--NTILE--(--unsigned--)----------------------------------------> (1) >--| OVER clause for Ranking functions |------------------------|
Element | Description | Restrictions | Syntax |
---|---|---|---|
unsigned | Unsigned integer that specifies how many categories, or tiles, to rank | Cannot be zero | Literal integer |
Usage
The number of ranked categories, or tiles, is set by the unsigned integer argument to the function, and on the ORDER BY expression in the OVER clause.
NTILE(100) OVER(ORDER BY dollars)
When the argument is 4, the returned values sort the rows in each partition that the OVER clause defines into four quartiles. When a set of values is not divisible by the specified integer argument, the NTILE function puts leftover rows in the lower-ranked tiles.
Example: NTILE function
The following query ranks employees in departments by employee salary, and calculates the tile number of 1 through 5 for each department.
SELECT name, salary,
NTILE(5) OVER (PARTITION BY dept ORDER BY salary)
FROM employee;
name salary (ntile)
John 35,000 1
Jack 38,400 1
Julie 41,200 2
Manny 45,600 2
Nancy 47,300 3
Pat 49,500 4
Ray 51,300 5
The salaries are ordered from lowest to highest because the default ordering direction for the ORDER BY clause is ascending. If you include the DESC keyword in the ORDER BY clause, the salaries are ordered from highest to lowest.