Saturday, 14 June 2008

Divide a result set into groups using NTILE

The NTILE analytic function divides a result set into a specified number of groups or buckets.

The following SQL takes a generated result set and, using NTILE, adds a column containing three distinct values with an equal number of rows for each value.
SELECT id, val, ntile(3) OVER(ORDER BY id) nt
FROM (
SELECT rownum id, 'xyz' || rownum val
FROM dual
CONNECT BY LEVEL <=12
)
ORDER BY id;
ID       VAL          NT
-------- ------------ ---------
1 xyz1 1
2 xyz2 1
3 xyz3 1
4 xyz4 1
5 xyz5 2
6 xyz6 2
7 xyz7 2
8 xyz8 2
9 xyz9 3
10 xyz10 3
11 xyz11 3
12 xyz12 3
12 rows selected

No comments: