SQL server NTILE() is a window function that distributes row of an ordered partition right into a unique quantity of approximately same partition or group or buckets. The input into function is a position integer which represents the number of equably divided.
The NTILE function is considered
to be a ranking function, but with a twist. The numbers applied are used to
divide the results into equal buckets. We must specify how many buckets are
needed and an ORDER BY expression is required in the OVER clause. Here is the
syntax for NTILE:
|
NTILE(<buckets>) OVER([PARTITION BY
<col1>[,<col2>,...<n>]]
ORDER BY
<col3>[,<col4>])
|
Within the parentheses after the
word NTILE, supply the number of buckets. The PARTITION BY is optional, and the
ORDER BY is required.
SQL script
|
WITH Orders AS (
SELECT MONTH(OrderDate) AS OrderMonth,
FORMAT(SUM(TotalDue),'C') AS Sales
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2013/01/01' and OrderDate < '2014/01/01'
GROUP BY MONTH(OrderDate))
SELECT OrderMonth, Sales, NTILE(4) OVER(ORDER BY Sales) AS Bucket
FROM Orders;
|
See the result
The query aggregates the sales
from 2013 into months inside a CTE called Orders. In the outer query, the NTILE the function is applied. The bucket that each month falls into depends on the sales
for that month. Bucket #1 contains three months with the lowest sales.
Bucket #4 contains three months with the highest sales.
