MySQL PERCENT_RANK Function
Summary: in this tutorial, you will learn how to use the MySQL PERCENT_RANK() function to calculate the percentile ranking of a row within a partition or result set.
The PERCENT_RANK() is a window function that calculates the percentile rank of a row within a partition or result set.
The following shows the syntax of the PERCENT_RANK() function:
PERCENT_RANK()
OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)
Code language: SQL (Structured Query Language) (sql)
The PERCENT_RANK() function returns a number that ranges from zero to one.
For a specified row, PERCENT_RANK() calculates the rank of that row minus one, divided by 1 less than number of rows in the evaluated partition or query result set:
(rank - 1) / (total_rows - 1)Code language: SQL (Structured Query Language) (sql)
In this formula, rank is the rank of a specified row and total_rows is the number of rows being evaluated.
The PERCENT_RANK() function always returns zero for the first row in a partition or result set. The repeated column values will receive the same PERCENT_RANK() value.
Similar to other window functions, the PARTITION BY clause distributes the rows into partitions and the ORDER BY clause specifies the logical order of rows in each partition. The PERCENT_RANK() function is calculated for each ordered partition independently.
Both PARTITION BY and ORDER BY clauses are optional. However, the PERCENT_RANK() is an order sensitive function, therefore, you should always use the ORDER BY clause.
MySQL PERCENT_RANK() function examples
Let’s create a new table named productLineSales based on the orders, orderDetails, and products tables from the sample database:
CREATE TABLE productLineSales
SELECT
productLine,
YEAR(orderDate) orderYear,
quantityOrdered * priceEach orderValue
FROM
orderDetails
INNER JOIN
orders USING (orderNumber)
INNER JOIN
products USING (productCode)
GROUP BY
productLine ,
YEAR(orderDate);Code language: SQL (Structured Query Language) (sql)
The productLineSales table stores the summary of the sales data including product line, order year, and order value.
Using MySQL PERCENT_RANK() over the query result set
The following query finds the percentile rank of every product line by order values:
WITH t AS (
SELECT
productLine,
SUM(orderValue) orderValue
FROM
productLineSales
GROUP BY
productLine
)
SELECT
productLine,
orderValue,
ROUND(
PERCENT_RANK() OVER (
ORDER BY orderValue
)
,2) percentile_rank
FROM
t;Code language: SQL (Structured Query Language) (sql)
In this example:
- First, we used a common table expression to summary the order values by product lines.
- Second, we used the
PERCENT_RANK()to calculate the percentile rank of order value of each product. In addition, we used theROUND()function to round the values to 2 decimal for a better representation.
Here is the output:
Here are some analyses from the output:
- The order values of
Trainswere not better than any other product lines, which was represented with a zero. -
Vintage Carsperformed better than 50% of other products. -
Classic Carsperformed better than any other product lines so its percent rank is 1 or 100%
Using MySQL PERCENT_RANK() over the partition
The following statement returns the percentile ranking of product lines by order values in each year:
SELECT
productLine,
orderYear,
orderValue,
ROUND(
PERCENT_RANK()
OVER (
PARTITION BY orderYear
ORDER BY orderValue
),2) percentile_rank
FROM
productLineSales;Code language: SQL (Structured Query Language) (sql)
Here is the output:
In this example, we divided the order values of the product lines by order year. The PERCENT_RANK() then applied to each partition.
For example, in 2013 Vintage Cars performed better than 50% of other product lines while in 2014 Ships performed better than 50% other products.
In this tutorial, you have learned how to use the MySQL PERCENT_RANK() function to calculate the percentile rank of a row within a partition or result set.