The NTILE() function in SQL is used to distribute rows in a result set into a specified number of groups or buckets. It assigns a bucket number to each row based on the order specified in the ORDER BY clause. The rows are divided evenly into the specified number of groups, and any remainder rows are evenly distributed among the first few groups.
Syntax
NTILE(number_of_buckets) OVER (ORDER BY column_name [ASC|DESC])
Parameters:
- number_of_buckets: The number of groups you want to divide your result set into.
- ORDER BY column_name: The column(s) that define the order of the rows before they are assigned to buckets.
Example Use Case
Suppose you have a table called Sales and you want to distribute the rows into 4 groups based on the sales amount. The query would look something like this:
SELECT
SalesID,
Amount,
NTILE(4) OVER (ORDER BY Amount DESC) AS SalesGroup
FROM
Sales;
Explanation:
- NTILE(4): This means you want to divide the result set into 4 groups.
- OVER (ORDER BY Amount DESC): The rows will be ordered by the Amount column in descending order before they are divided into groups.
- The result will include a SalesGroup column that indicates which group each row belongs to (1 through 4).
Points to Note:
1. If the number of rows is not perfectly divisible by the number of buckets, some buckets will contain one more row than others. For example, if you have 10 rows and divide them into 3 buckets, the distribution will be: 4 in the first bucket, 3 in the second, and 3 in the third.
2. The buckets are numbered starting from 1.
3. The NTILE() function is typically used in analytical queries, useful for dividing data into quantiles, deciles, etc.
Complete Example
Here's a complete example using a hypothetical Sales table:
CREATE TABLE Sales (
SalesID INT,
Amount DECIMAL(10, 2)
);
INSERT INTO Sales (SalesID, Amount) VALUES
(1, 100.00),
(2, 200.00),
(3, 150.00),
(4, 300.00),
(5, 50.00),
(6, 400.00),
(7, 250.00),
(8, 350.00),
(9, 75.00),
(10, 500.00);
SELECT
SalesID,
Amount,
NTILE(4) OVER (ORDER BY Amount DESC) AS SalesGroup
FROM
Sales;
This would split the sales records into 4 groups based on the sales amount, where higher sales amounts will be in lower-numbered groups (i.e., Group 1 would contain the highest sales amounts).