NTILE Function - SQL
Overview
The NTILE function in SQL distributes rows into a specified number of groups, or "tiles," based on an ordering within each partition of a result set. It is useful for dividing data evenly across partitions.
Example:
SELECT department_id, first_name, salary,
NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM company.employees
Syntax:
SELECT column_name,
NTILE(number_of_tiles) OVER (PARTITION BY partition_column[s] ORDER BY order_column[s])
FROM table_name
partition_column[s] is the column or columns that divide the result set into partitions.
order_column[s] is the column or columns that specify the order of rows within each partition.
NTILE(number_of_tiles) distributes rows into the specified number of groups (number_of_tiles) based on the ordering within each partition.
Sample Data:
Before
| department_id | first_name | salary |
|---|---|---|
| 3 | Frank | 123000 |
| 2 | Jane | 135000 |
| 3 | Ashley | 115000 |
| NULL | Glenn | 115000 |
| 2 | Kelly | 125000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 5 | Kyle | 200000 |
| 2 | James | 107000 |
| 1 | Gustavo | 100000 |
After
| department_id | first_name | salary |
|---|---|---|
| NULL | Glenn | 115000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 1 | Gustavo | 100000 |
| 2 | Jane | 135000 |
| 2 | Kelly | 125000 |
| 2 | James | 107000 |
| 3 | Ashley | 115000 |
| 3 | Frank | 123000 |
| 5 | Kyle | 200000 |
Example: Query without the WHERE Statement
In this example, we are returning the department_id, first_name, and salary column, along with a NTILE column to divide the employees within each department into 2 approximately equal-sized groups based on their salary, ordering them in descending order. The result is a ranking of 1 or 2 for each employee, indicating which half of the salary distribution they fall into within their department.
Example: Query with the WHERE Statement
In this example, we are filtering for only rows in department_id 1. We are returning the department_id, first_name, and salary column, along with a NTILE column to divide the employees within each department into 2 approximately equal-sized groups based on their salary, ordering them in descending order. The result is a ranking of 1 or 2 for each employee, indicating which half of the salary distribution they fall into within their department.
