NTILE() function is a window function that distributes rows of an ordered partition into a pre-defined number of roughly equal groups. It assigns each group a number_expression ranging from 1. NTILE() function assigns a number_expression for every row in a group, to which the row belongs.
NTILE(number_expression) OVER ( [PARTITION BY partition_expression ] ORDER BY sort_expression [ASC | DESC] )
Parameters of syntax in detail :
- The number_expression is the integer into which the rows are divided.
- PARTITION BY clause
- The PARTITION BY is optional, it differs the rows of a result set into partitions where the NTILE() function is used.
- ORDER BY clause
- The ORDER BY clause defines the order of rows in each partition where the NTILE() is used.
Let us create a table named hello_demo :
CREATE TABLE hello_demo ( ID INT NOT NULL ); INSERT INTO hello_demo(ID) VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
SELECT * FROM hello_demo;
1 2 3 4 5 6 7 8 9 10