Dividing rows into buckets
NTILE is a window function that distributes the ordered rows of a partition into a given number of buckets, then labels each row with its bucket number. NTILE four creates quartiles, NTILE ten creates deciles, and NTILE one hundred approximates percentiles.
How rows are split
NTILE tries to make buckets as equal as possible. If the row count divides evenly, every bucket has the same size. When it does not divide evenly, the earlier buckets each get one extra row. For nine rows into four buckets, the sizes become three, two, two, two.
What it does not do
NTILE splits by position, not by value. Two rows with identical values can land in different buckets if they straddle a boundary. So NTILE answers where does this row sit in the ordering, not how does this value compare numerically. For value based thresholds use PERCENT RANK or CUME DIST instead.
Typical uses
- Tagging customers into spending quartiles.
- Sampling balanced subsets for analysis.
- Building simple percentile reports.
Key idea
NTILE divides ordered rows into nearly equal buckets by position, giving earlier buckets the extra rows, which makes it ideal for quartiles but unaware of actual value differences.