NTILE
Contents
Syntax
NTILE (expr) OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] )
Description
NTILE() is a window function 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.
Examples
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 | +----+------+------+-----------------------------+
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.