{"id":1251,"date":"2019-02-21T09:05:52","date_gmt":"2019-02-21T02:05:52","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1251"},"modified":"2020-07-11T04:06:15","modified_gmt":"2020-07-10T21:06:15","slug":"sql-server-ntile-function","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-window-functions\/sql-server-ntile-function\/","title":{"rendered":"SQL Server NTILE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server NTILE()<\/code> function to distribute rows of an ordered partition into a specified number of buckets.<\/p>\n\n\n\n

Introduction to SQL Server NTILE() function #<\/a><\/h2>\n\n\n\n

The SQL Server NTILE()<\/code> is a window function<\/a> that distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE()<\/code> function assigns a bucket number representing the group to which the row belongs.<\/p>\n\n\n\n

The syntax of the NTILE()<\/code> function is as follows:<\/p>\n\n\n

NTILE(buckets) OVER (\n    [PARTITION BY partition_expression, ... ]\n    ORDER BY sort_expression [ASC | DESC], ...\n)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine the syntax in detail:<\/p>\n\n\n\n
PARTITION BY clause<\/strong> #<\/a><\/h3>\n\n\n\n
The PARTITION BY<\/code> clause distributes rows of a result set into partitions to which the NTILE()<\/code> function is applied.<\/p>\n\n\n\n
ORDER BY clause #<\/a><\/h3>\n\n\n\n
The ORDER BY<\/code> clause specifies the logical order of rows in each partition to which the NTILE()<\/code> is applied.<\/p>\n\n\n\n
If the number of rows is not divisible by the buckets<\/code>, the NTILE()<\/code> function returns groups of two sizes with the difference by one. The larger groups always come before the smaller group in the order specified by the ORDER BY<\/code> in the OVER()<\/code> clause.<\/p>\n\n\n\n
On the other hand, if the total of rows is divisible by the buckets<\/code>, the function divides evenly the rows among buckets.<\/p>\n\n\n\n
SQL Server NTILE() function illustration #<\/a><\/h2>\n\n\n\n
The following statement creates a new table<\/a> named ntile_demo<\/code> that stores 10 integers:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.ntile_demo (\n\tv INT<\/span> NOT<\/span> NULL<\/span>\n);\n\t\nINSERT<\/span> INTO<\/span> sales.ntile_demo(v) \nVALUES<\/span>(1<\/span>),(2<\/span>),(3<\/span>),(4<\/span>),(5<\/span>),(6<\/span>),(7<\/span>),(8<\/span>),(9<\/span>),(10<\/span>);\n\t\n\t\nSELECT<\/span> * FROM<\/span> sales.ntile_demo;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This statement uses the NTILE()<\/code> function to divide ten rows into three groups:<\/p>\n\n\n
SELECT<\/span> \n\tv, \n\tNTILE (3<\/span>) OVER<\/span> (\n\t\tORDER<\/span> BY<\/span> v\n\t) buckets\nFROM<\/span> \n\tsales.ntile_demo;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
As clearly shown in the output, the first group has four rows and the other two groups have three rows.<\/p>\n\n\n\n
The following statement uses the NTILE()<\/code> function to distribute rows into five buckets:<\/p>\n\n\n
SELECT<\/span> \n\tv, \n\tNTILE (5<\/span>) OVER<\/span> (\n\t\tORDER<\/span> BY<\/span> v\n\t) buckets\nFROM<\/span> \n\tsales.ntile_demo;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is as follows:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
As you can see, the output has five groups with the same number of rows in each.<\/p>\n\n\n\n
SQL Server NTILE() function examples #<\/a><\/h2>\n\n\n\n
Let’s create a view to demonstrate the NTILE()<\/code> function.<\/p>\n\n\n\n
The following statement creates a view<\/a> that returns the net sales in 2017 by months.<\/p>\n\n\n
CREATE<\/span> VIEW<\/span> sales.vw_netsales_2017 AS<\/span>\nSELECT<\/span> \n\tc.category_name,\n\tDATENAME<\/span>(month<\/span>, o.shipped_date) month<\/span>, \n\tCONVERT<\/span>(DEC<\/span>(10<\/span>, 0<\/span>), SUM<\/span>(i.list_price * quantity * (1<\/span> - discount))) net_sales\nFROM<\/span> \n\tsales.orders o\nINNER<\/span> JOIN<\/span> sales.order_items i ON<\/span> i.order_id = o.order_id\nINNER<\/span> JOIN<\/span> production.products p on<\/span> p.product_id = i.product_id\nINNER<\/span> JOIN<\/span> production.categories c on<\/span> c.category_id = p.category_id\nWHERE<\/span> \n\tYEAR<\/span>(shipped_date) = 2017<\/span>\nGROUP<\/span> BY<\/span>\n\tc.category_name,\n\tDATENAME<\/span>(month<\/span>, o.shipped_date);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
SELECT<\/span> \n    category_name<\/span>, \n    month<\/span>,\n    net_sales<\/span> \nFROM<\/span> \n   sales<\/span>.vw_netsales_2017<\/span> \nORDER<\/span> BY<\/span> \n   category_name<\/span>, \n   net_sales<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Using SQL Server NTILE() function over a query result set example #<\/a><\/h3>\n\n\n\n
The following example uses the NTILE()<\/code> function to distribute the months to 4 buckets based on net sales:<\/p>\n\n\n
WITH<\/span> cte_by_month AS<\/span>(\n\tSELECT<\/span>\n\t\tmonth<\/span>, \n\t\tSUM<\/span>(net_sales) net_sales\n\tFROM<\/span> \n\t\tsales.vw_netsales_2017\n\tGROUP<\/span> BY<\/span> \n\t\tmonth<\/span>\n)\nSELECT<\/span>\n\tmonth<\/span>, \n\tFORMAT<\/span>(net_sales,'C'<\/span>,'en-US'<\/span>) net_sales,\n\tNTILE(4<\/span>) OVER<\/span>(\n\t\tORDER<\/span> BY<\/span> net_sales DESC<\/span>\n\t) net_sales_group\nFROM<\/span> \n\tcte_by_month;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
Using SQL Server NTILE() function over partitions example #<\/a><\/h3>\n\n\n\n
This example uses the NTILE()<\/code> function to divide the net sales by month into 4 groups for each product category:<\/p>\n\n\n
SELECT<\/span>\n\tcategory_name,\n\tmonth<\/span>, \n\tFORMAT<\/span>(net_sales,'C'<\/span>,'en-US'<\/span>) net_sales,\n\tNTILE(4<\/span>) OVER<\/span>(\n\t\tPARTITION<\/span> BY<\/span> category_name\n\t\tORDER<\/span> BY<\/span> net_sales DESC<\/span>\n\t) net_sales_group\nFROM<\/span> \n\tsales.vw_netsales_2017;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server NTILE()<\/code> function to distribute rows of an ordered partition into a specified number of buckets.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
buckets #<\/a><\/h3>\n\n\n\n
The number of buckets into which the rows are divided. The buckets can be an expression or subquery<\/a> that evaluates to a positive integer. It cannot be a window function.<\/p>\n\n\n\n