{"id":7079,"date":"2018-08-29T06:58:11","date_gmt":"2018-08-29T13:58:11","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7079"},"modified":"2024-01-04T04:07:12","modified_gmt":"2024-01-04T11:07:12","slug":"mysql-percent_rank-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-percent_rank-function\/","title":{"rendered":"MySQL PERCENT_RANK Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL PERCENT_RANK()<\/code> function to calculate the percentile ranking of a row within a partition or result set.<\/p>\n\n\n\n

The PERCENT_RANK()<\/code> is a window function<\/a> that calculates the percentile rank<\/a> of a row within a partition or result set.<\/p>\n\n\n\n

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

PERCENT_RANK() OVER (\n  PARTITION BY partition_expression \n  ORDER BY \n    sort_expression [ASC | DESC]\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The PERCENT_RANK()<\/code> function returns a number that ranges from zero to one.<\/p>\n\n\n\n
(rank - 1) \/ (total_rows - 1)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this formula, rank<\/code> is the rank of a specified row and total_rows<\/code> is the number of rows being evaluated.<\/p>\n\n\n\n
The PERCENT_RANK()<\/code> function always returns zero for the first row in a partition or result set. The repeated column values will receive the same PERCENT_RANK()<\/code> value.<\/p>\n\n\n\n
Similar to other window functions, the PARTITION BY<\/code> clause distributes the rows into partitions and the ORDER BY<\/code> clause specifies the logical order of rows in each partition. The PERCENT_RANK()<\/code> function is calculated for each ordered partition independently.<\/p>\n\n\n\n
Both PARTITION BY<\/code> and ORDER BY<\/code> clauses are optional. However, the PERCENT_RANK()<\/code> is an order-sensitive function, therefore, you should always use the ORDER BY<\/code> clause.<\/p>\n\n\n\n
MySQL PERCENT_RANK() function examples<\/h2>\n\n\n\n
Let’s create a new table named productLineSales<\/code> based on the orders<\/code>, orderDetails<\/code>, and products<\/code> tables from the sample database<\/a>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> productLineSales\nSELECT<\/span>\n    productLine,\n    YEAR<\/span>(orderDate) orderYear,\n    quantityOrdered * priceEach orderValue\nFROM<\/span>\n    orderDetails\n        INNER<\/span> JOIN<\/span>\n    orders USING<\/span> (orderNumber)\n        INNER<\/span> JOIN<\/span>\n    products USING<\/span> (productCode)\nGROUP<\/span> BY<\/span>\n    productLine ,\n    YEAR<\/span>(orderDate), \n    orderValue;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The productLineSales<\/code> table stores the summary of the sales data including product line, order year, and order value.<\/p>\n\n\n\n
1) Using MySQL PERCENT_RANK() over the query result set<\/h3>\n\n\n\n
The following query finds the percentile rank of every product line by order values:<\/p>\n\n\n
WITH<\/span> t AS<\/span> (\n    SELECT<\/span>\n        productLine,\n        SUM<\/span>(orderValue) orderValue\n    FROM<\/span>\n        productLineSales\n    GROUP<\/span> BY<\/span>\n        productLine\n)\nSELECT<\/span>\n    productLine,\n    orderValue,\n    ROUND<\/span>(\n       PERCENT_RANK<\/span>() OVER<\/span> (\n          ORDER<\/span> BY<\/span> orderValue\n       )\n    ,2<\/span>) percentile_rank\nFROM<\/span>\n    t;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example:<\/p>\n\n\n\n
For a specified row, PERCENT_RANK()<\/code> calculates the rank<\/a> of that row minus one, divided by 1 less than the number of rows in the evaluated partition or query result set:<\/p>\n\n\n