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 documentation

Possibly related:

Tagged , | Post a Comment