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],...
    )

The PERCENT_RANK() the 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 the number of rows in the evaluated partition or query result set:

(rank - 1) / (total_rows - 1)

In this formula, rank is the rank of a specified row and total_rows is the number of rows being evaluated.

The PERCENT_RANK() the 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);

The productLineSales the 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;

In this example:

• First, we used a common table expression to summarize the order values by product lines.
• Second, we used the PERCENT_RANK() to calculate the percentile rank of the order value of each product. In addition, we used the ROUND() function to round the values to 2 decimals for a better representation.

Here is the output:

Here are some analyses from the output:

• The order values of Trains were not better than any other product lines, which was represented with a zero.
•  Vintage Cars performed better than 50% of other products.
•  Classic Cars performed better than any other product line so its percentile 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;

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.