Quartiles, deciles, and other equal-sized bands are a common way to segment data: the top quarter of customers by spend, the bottom tenth of routes by load. NTILE divides the ordered rows of each partition into a given number of buckets of as equal a size as possible, and returns each row's bucket number.
Code for This Guide
The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
ntile(n) over ([partition by expr [, ...]] order by expr [, ...])
n is the number of buckets. When the rows do not divide evenly, the first buckets get one row more.
Salary Quartiles
Example:
select last_name, salary,
ntile(4) over (order by salary desc) as quartile
from employees
where department_id = 30
order by salary desc;Output:
LAST_NAME SALARY QUARTILE _______________ _________ ___________ Rahman 22000 1 Taylor 11800 1 Okafor 11600 1 Laurent 7200 2 Sharma 7000 2 van der Berg 6900 2 Zhang 6800 3 Aziz 6700 3 Bianchi 6500 3 Brown 6400 4 O'Brien 6200 4 11 rows selected.
Eleven employees in four buckets make 3, 3, 3, and 2. Quartile 1 holds the highest salaries because the order is descending.
Describe Each Bucket
Grouping by the bucket number shows how many rows each holds and its range of values.
Example:
-- bookings in four equal-sized groups by amount, with each group's range
select quartile, count(*) as bookings, min(total_amount) as from_amount, max(total_amount) as to_amount
from (select total_amount, ntile(4) over (order by total_amount) as quartile
from bookings where status <> 'CANCELLED')
group by quartile
order by quartile;Output:
QUARTILE BOOKINGS FROM_AMOUNT TO_AMOUNT
___________ ___________ ______________ ____________
1 168 118.51 524.79
2 167 528.09 1001.29
3 167 1004.58 1997.01
4 167 1998.4 10756.82The 669 bookings split into one bucket of 168 and three of 167. The first quarter ranges up to about 525 and the last from about 1,998 upward.
Things to Know
- NTILE splits by row count, not by value: tied values can land in different buckets at a boundary.
- For buckets of equal value ranges rather than equal sizes, use WIDTH_BUCKET.
- For the boundary values themselves, such as the quartile limits, use PERCENTILE_CONT or PERCENTILE_DISC.
Related Guides
Conclusion
NTILE(n) divides ordered rows into n buckets of nearly equal size and numbers them, with the first buckets taking any extra rows. Use it to segment data into quartiles, deciles, or any number of equal bands.
