Топ-100
 
RDBMS Type: MySQL
Topic

NTILE(N) over_clause

Divides a partition into N groups (buckets), assigns each row in the
partition its bucket number, and returns the bucket number of the
current row within its partition. For example, if N is 4, NTILE()
divides rows into four buckets. If N is 100, NTILE() divides rows into
100 buckets.

N must be a literal positive integer. Bucket number return values range
from 1 to N.

Beginning with MySQL 8.0.22, N cannot be NULL. In addition, it must be
an integer in the range 1 to 263, inclusive, in any of the following
forms:

o an unsigned integer constant literal

o a positional parameter marker (?)

o a user-defined variable

o a local variable in a stored routine

This function should be used with ORDER BY to sort partition rows into
the desired order.

over_clause is as described in
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

Example

mysql> SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
NTILE(2) OVER w AS 'ntile2',
NTILE(4) OVER w AS 'ntile4'
FROM numbers
WINDOW w AS (ORDER BY val);
+------+------------+--------+--------+
| val | row_number | ntile2 | ntile4 |
+------+------------+--------+--------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 1 | 1 |
| 2 | 3 | 1 | 1 |
| 3 | 4 | 1 | 2 |
| 3 | 5 | 1 | 2 |
| 3 | 6 | 2 | 3 |
| 4 | 7 | 2 | 3 |
| 4 | 8 | 2 | 4 |
| 5 | 9 | 2 | 4 |
+------+------------+--------+--------+