MySQL PERCENT_RANK Function

 

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 ordersorderDetails, 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 summary 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 decimal for a better representation.

Here is the output:

MySQL PERCENT_RANK Function

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 lines 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:

MySQL PERCENT_RANK function over partition example

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.

Reactions

Post a Comment

0 Comments