Distribute rows into buckets. This function divides the rows in an ordered partition into a specified number of approximately equal groups.
NTILE (expr) OVER (
[ PARTITION BY partition_expression ]
[ ORDER BY order_list ]
)NTILE() is a that returns an integer indicating which group a given row falls into. The number of groups is specified in the argument (expr), starting at one. Ordered rows in the partition are divided into the specified number of groups with as equal a size as possible.
This page is licensed: CC BY-SA / Gnu FDL
create table t1 (
pk int primary key,
a int,
b int
);
insert into t1 values
(11 , 0, 10),
(12 , 0, 10),
(13 , 1, 10),
(14 , 1, 10),
(18 , 2, 10),
(15 , 2, 20),
(16 , 2, 20),
(17 , 2, 20),
(19 , 4, 20),
(20 , 4, 20);
select pk, a, b,
ntile(1) over (order by pk)
from t1;
+----+------+------+-----------------------------+
| pk | a | b | ntile(1) over (order by pk) |
+----+------+------+-----------------------------+
| 11 | 0 | 10 | 1 |
| 12 | 0 | 10 | 1 |
| 13 | 1 | 10 | 1 |
| 14 | 1 | 10 | 1 |
| 15 | 2 | 20 | 1 |
| 16 | 2 | 20 | 1 |
| 17 | 2 | 20 | 1 |
| 18 | 2 | 10 | 1 |
| 19 | 4 | 20 | 1 |
| 20 | 4 | 20 | 1 |
+----+------+------+-----------------------------+
select pk, a, b,
ntile(4) over (order by pk)
from t1;
+----+------+------+-----------------------------+
| pk | a | b | ntile(4) over (order by pk) |
+----+------+------+-----------------------------+
| 11 | 0 | 10 | 1 |
| 12 | 0 | 10 | 1 |
| 13 | 1 | 10 | 1 |
| 14 | 1 | 10 | 2 |
| 15 | 2 | 20 | 2 |
| 16 | 2 | 20 | 2 |
| 17 | 2 | 20 | 3 |
| 18 | 2 | 10 | 3 |
| 19 | 4 | 20 | 4 |
| 20 | 4 | 20 | 4 |
+----+------+------+-----------------------------+