{"id":1728,"date":"2018-11-11T11:11:23","date_gmt":"2018-11-11T04:11:23","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1728"},"modified":"2024-04-16T10:10:29","modified_gmt":"2024-04-16T03:10:29","slug":"sqlite-ntile","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-window-functions\/sqlite-ntile\/","title":{"rendered":"SQLite NTILE() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite NTILE()<\/code> function to divide a result set into a number of buckets and assign a bucket number to each row.<\/p>\n\n\n\n

Introduction to SQLite NTILE() function<\/h2>\n\n\n\n

SQLite NTILE()<\/code> function is a window function<\/a> that divides an ordered result set into a number of buckets by a specified expression and assigns an appropriate bucket number to each row.<\/p>\n\n\n\n

The following shows the syntax of the NTILE()<\/code> function:<\/p>\n\n\n

NTILE(expression) OVER ( \n\tPARTITION BY expression1, expression2,...\n\tORDER BY expression1 [ASC | DESC]expression2,\n)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
expression<\/h3>\n\n\n\n
The expression<\/code> can be a literal positive integer or any valid expression that resolves to a positive integer.<\/p>\n\n\n\n
The NTILE()<\/code> function assigns numbers from 1 through the value of the expression<\/code> to each row. The number of rows in buckets can differ by at most 1. The remainder of the number of rows divided by buckets is allocated to each bucket, starting with bucket 1.<\/p>\n\n\n\n
For example, if you have 10 rows and 4 buckets. Each bucket will have 2 rows. The remainder of 10\/4 is 2. So the first bucket will have 2 + 1 = 3 rows, the second bucket will also have 3 rows.<\/p>\n\n\n\n
The following picture illustrates the idea:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
If the value of the expression is greater than the number of rows, then the NTILE()<\/code> function will fill the number of buckets equal to the number of rows. Hence, the remaining buckets will be empty.<\/p>\n\n\n\n
For example, if you have 10 rows and 11 buckets, each row will be assigned a bucket number from 1 to 10. The 11th bucket will have no row.<\/p>\n\n\n\n
Notice that you cannot use a subquery<\/a> or a window function<\/a> in the expression<\/code>.<\/p>\n\n\n\n
PARTITION BY<\/h3>\n\n\n\n
The PARTITION BY<\/code> clause divides the result sets into partitions to which the NTILE<\/code> function applies.<\/p>\n\n\n\n
ORDER BY<\/h3>\n\n\n\n
The ORDER BY<\/code> clause specifies the order of rows in each partition to which the NTILE()<\/code> applies.<\/p>\n\n\n\n
SQLite\u00a0NTILE() function examples<\/h2>\n\n\n\n
We will use the tracks table from the sample database to demonstrate the NTILE()<\/code> function:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using the NTILE() function with ORDER BY example<\/h3>\n\n\n\n
The following statement uses the NTILE()<\/code> function to divide into 4 buckets the values in the Milliseconds<\/code> column of the tracks<\/code> table from the album 1:<\/p>\n\n\n
SELECT<\/span>\n  Name<\/span>,\n  Milliseconds,\n  NTILE (4<\/span>) OVER<\/span> (\n    ORDER<\/span> BY<\/span>\n      Milliseconds\n  ) LengthBucket\nFROM<\/span>\n  tracks\nWHERE<\/span>\n  AlbumId = 1<\/span>;<\/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
\"SQLite<\/figure>\n\n\n\n
2) Using the NTILE() function with PARTITION BY clause example<\/h3>\n\n\n\n
The following statement uses the NTILE()<\/code> function to divide the tracks of each album into 3 buckets by the values in the Bytes<\/code> column:<\/p>\n\n\n
SELECT<\/span>\n  AlbumId,\n  Name<\/span>,\n  Milliseconds,\n  NTILE (3<\/span>) OVER<\/span> (\n    PARTITION<\/span> BY<\/span>\n      AlbumId\n    ORDER<\/span> BY<\/span>\n      Bytes<\/span>\n  ) SizeBucket\nFROM<\/span>\n  tracks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
In this example, <\/p>\n\n\n\n
    \n
  • The PARTITION BY<\/code> clause distributes the rows into partitions or albums. <\/li>\n\n\n\n
  • The ORDER BY<\/code> clause sorts the tracks in each album by their sizes in bytes.<\/li>\n<\/ul>\n\n\n\n
    The NTILE()<\/code> function assigned a bucket number to each track and reset the bucket number for each album.<\/p>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the SQLite NTILE()<\/code> function to divide a result set into a number of buckets and assign a bucket number to each row.<\/li>\n<\/ul>\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