Cool SQL Analytic Function: NTILE
The NTILE function divides an ordered partition into buckets and assigns a bucket number to each row in the partition.
The buckets are calculated so that each bucket has exactly the same number of rows assigned to it.
If the number of rows in the partition does not divide evenly into the number of buckets, then the number of rows assigned for each bucket will differ by one at most. The extra rows will be distributed one for each bucket starting from the lowest bucket number.
For example, you want to organize the employees into four buckets:
select ntile(4) over (order by employee_id) bkt,
employee_id, first_name
from employees
where rownum <= 10
The number 4 in ntile(4) is the number of buckets. The result is:
BKT EMPLOYEE_ID FIRST_NAME
---------------------- ---------------------- --------------------
1 100 Steven
1 101 Neena
1 102 Lex
2 103 Alexander
2 104 Bruce
2 105 David
3 106 Valli
3 107 Diana
4 108 Nancy
4 109 Daniel
10 rows selected
Notice that since 10 does not divide evenly (without a remainder) by 4, the first two buckets (1 and 2) have 3 employee records each and the last two buckets (3 and 4) have 2 employee records each.
Have you used NTILE in a production application? If yes, please share with us what you used it for.
Related articles:
- Cool SQL Analytic Function: RATIO_TO_REPORT
- An Easy Way to Convert a Hierarchical Query Result to XML
- Beware of Comments in SQL
Been using it for quite some time to work out OUTLIERs for stats analysis of data – things outside the norm need to be discounted so we NTILE over 200 and drop the 1/200 buckets so we rule out the 0.5% on either end of the distribution. Gets rid of all the silly values basically and means the stats analysis is much more solid.
Thanks Jeff for sharing. Basically you use NTILE to exclude certain “buckets” that, by your definition, have “silly values”. Neat!