Collections:
NTILE() - Dividing Window into N Tiles
How to divid result set window into N tiles and return the tile position using the NTILE() function?
✍: FYIcenter.com
NTILE(n) is a MySQL built-in window function that divides result set window into n tiles and returns the tile position of the current row. For example:
SELECT help_topic_id AS tip, help_category_id AS cid, NTILE(5) OVER w, ROW_NUMBER() OVER W, COUNT(*) OVER W FROM mysql.help_topic WINDOW w AS (PARTITION BY help_category_id); -- +-----+-----+-----------------+---------------------+-----------------+ -- | tip | cid | NTILE(5) OVER w | ROW_NUMBER() OVER W | COUNT(*) OVER W | -- +-----+-----+-----------------+---------------------+-----------------+ -- | 0 | 1 | 1 | 1 | 2 | -- | 1 | 1 | 2 | 2 | 2 | -- | 2 | 2 | 1 | 1 | 35 | -- | 6 | 2 | 1 | 2 | 35 | -- | 7 | 2 | 1 | 3 | 35 | -- | 8 | 2 | 1 | 4 | 35 | -- | 9 | 2 | 1 | 5 | 35 | -- | 10 | 2 | 1 | 6 | 35 | -- | 11 | 2 | 1 | 7 | 35 | -- | 12 | 2 | 2 | 8 | 35 | -- | 13 | 2 | 2 | 9 | 35 | -- ... -- +-----+-----+-----------------+---------------------+-----------------+
Reference information of the NTILE() function:
NTILE(n): pos Divides result set window into n tiles and returns the tile position of the current row. Arguments, return value and availability: n: Required. The number of tiles to be divided into. pos: Return value. The position of the current tile. Available since MySQL 8.
⇒ PERCENT_RANK() - Rank Percentage of Sorted Values
⇐ NTH_VALUE() - N-th Value of Result Set Window
2023-12-19, 356🔥, 0💬
Popular Posts:
How AdventureWorksLT tables are related in SQL Server? There are 12 user tables defined in Adventure...
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...
What Is an Oracle Tablespace in Oracle? An Oracle tablespace is a big unit of logical storage in an ...
Where to find answers to frequently asked questions on CREATE, ALTER and DROP Statements in MySQL? H...
How To Query Tables and Loop through the Returning Rows in MySQL? The best way to query tables and l...