{"id":1749,"date":"2018-11-12T20:30:21","date_gmt":"2018-11-12T13:30:21","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1749"},"modified":"2024-04-16T10:12:05","modified_gmt":"2024-04-16T03:12:05","slug":"sqlite-percent_rank","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-window-functions\/sqlite-percent_rank\/","title":{"rendered":"SQLite PERCENT_RANK() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite PERCENT_RANK()<\/code> function to calculate the percent rank of a row in an ordered result set.<\/p>\n\n\n\n

Introduction to the PERCENT_RANK() function<\/h2>\n\n\n\n

The PERCENT_RANK()<\/code> is a window function<\/a> that calculates the percent rank of a given row using the following formula:<\/p>\n\n\n

(r - 1) \/ (the number of rows in the window or partition - r)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
where r<\/code> is the rank of the current row.<\/p>\n\n\n\n
The PERCENT_RANK()<\/code> function returns a value that ranges from 0 to 1. The first row in any set has the percent rank of 0.<\/p>\n\n\n\n
Here is the syntax of the PERCENT_RANK()<\/code> function:<\/p>\n\n\n
PERCENT_RANK()  \n    OVER ( \n        [PARTITION BY partition_expression]\n        [ORDER BY order_list]\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
()<\/h3>\n\n\n\n
The PERCENT_RANK()<\/code> function takes no argument. However, empty parentheses are required.<\/p>\n\n\n\n
 PARTITION BY<\/h3>\n\n\n\n
The PARTITION BY<\/code> clause divides the rows into partitions to which the function applies. The PARTITION BY<\/code> clause is optional. If you omit the PARTITION BY<\/code> clause, the function will treat the whole result set as a single partition.<\/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 function applies. The ORDER BY<\/code> clause is also optional. If you skip it, the function will return zero for all rows.<\/p>\n\n\n\n
SQLite PERCENT_RANK() function example<\/h2>\n\n\n\n
We will use the following tracks<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using PERCENT_RANK() function over the query result set<\/h3>\n\n\n\n
The following statement uses the PERCENT_RANK()<\/code>function to find the percent rank of each track’s length within the album id 1:<\/p>\n\n\n
SELECT<\/span>\n    Name<\/span>,\n    Milliseconds,\n    PERCENT_RANK<\/span>() OVER<\/span>( \n        ORDER<\/span> BY<\/span> Milliseconds \n    ) LengthPercentRank\nFROM<\/span>\n    tracks \nWHERE<\/span>\n    AlbumId = 1<\/span>;\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
\"SQLite<\/figure>\n\n\n\n
To make the output more readable, you can use the printf()<\/code> function to format the percent ranks:<\/p>\n\n\n
SELECT<\/span>\n    Name<\/span>,\n    Milliseconds,\n    printf('%.2f'<\/span>,PERCENT_RANK<\/span>() OVER<\/span>( \n        ORDER<\/span> BY<\/span> Milliseconds \n    )) LengthPercentRank\nFROM<\/span>\n    tracks \nWHERE<\/span>\n    AlbumId = 1<\/span>;\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
\"SQLite<\/figure>\n\n\n\n
2) Using PERCENT_RANK() function over the partitions<\/h3>\n\n\n\n
The following statement uses the PERCENT_RANK()<\/code> function to calculate the percent rank of the track’s size in each album:<\/p>\n\n\n
SELECT<\/span>\n  AlbumId,\n  Name<\/span>,\n  Bytes<\/span>,\n  printf  (\n    '%.2f'<\/span>,\n    PERCENT_RANK<\/span>() OVER<\/span> (\n      PARTITION<\/span> BY<\/span>\n        AlbumId\n      ORDER<\/span> BY<\/span>\n        Bytes<\/span>\n    )\n  ) SizePercentRank\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
Summary<\/h2>\n\n\n\n
    \n
  • Use the PERCENT_RANK()<\/code> function to calculate the percent rank of a row in an ordered result set.<\/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