Oracle SQL Analytic functions : NTILE and WIDTH_BUCKET

Lets try to learn about Analytic functions NTILE and WIDTH_BUCKET

NTILE Anlytic Function:

NTILE is an analytic function.
It divides ordered data set into number of buckets and assigns appropriate bukcet number to each row.
It can be used to divide rows into equal sets and assign a number to each row.

Lets take all_objects view, and group them into balanced sets and assign bucket number to each.

select object_id, ntile(8) over (order by object_id) group_id
from all_objects where rownum<20;
Output will be as below:

OBJECT_ID	group_id
2	1
3	1
4	1
5	2
6	2
7	2
8	3
9	3
10	3
11	4

As we can see,oracle has grouped rows equally and assigned numbers for each row (column group_id)

WIDTH_BUCKET Function:
This is similar to NTILE function, but here we can actually supply the range of values (start and end values),
It takes the raanges and splits it into N groups.

   
select object_id,
width_bucket( object_id, 1, 20, 3 ) width_bucket,
ntile(8) over (order by object_id) ntile
from all_objects where rownum<20;

OBJECT_ID	WIDTH_BUCKET	NTILE
2	1	1
3	1	1
4	1	1
5	1	2
6	1	2
7	1	2
8	2	3
9	2	3
10	2	3
11	2	4
12	2	4
13	2	5
14	3	5
15	3	6
16	3	6

As we can see from above output. NTILE can be used to get even distribution and WIDTH_BUCKET can be used for user provided range of values.

Some useful links:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:47910227585839
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:30966075177614

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions101.htm