How the PERCENT_RANK() function works in Mariadb?

The PERCENT_RANK() function is a built-in function in Mariadb that returns the relative rank of a row within a group of rows.

Posted on

The PERCENT_RANK() function is a built-in function in Mariadb that returns the relative rank of a row within a group of rows. The function is useful for calculating the percentile or the percentage of values that are less than or equal to a given value. The function is also known as PERCENTILE_RANK().

Syntax

The syntax of the PERCENT_RANK() function is as follows:

PERCENT_RANK() OVER (
  PARTITION BY expr1, expr2, ...
  ORDER BY expr3, expr4, ...
)

Where expr1, expr2, … are the expressions that define the partition or the group of rows, and expr3, expr4, … are the expressions that define the order or the ranking of the rows within each partition. The function returns a decimal value between 0 and 1, inclusive.

Examples

Example 1: Calculating the percent rank of students’ scores

The following example shows how to use the PERCENT_RANK() function to calculate the percent rank of students’ scores in a table called students:

SELECT name, score, PERCENT_RANK() OVER (ORDER BY score) AS PercentRank
FROM students;

The output is:

+-------+-------+-------------+
| name  | score | PercentRank |
+-------+-------+-------------+
| Bob   |    50 |      0.0000 |
| Alice |    60 |      0.2500 |
| Eve   |    70 |      0.5000 |
| Dave  |    80 |      0.7500 |
| Carol |    90 |      1.0000 |
+-------+-------+-------------+

The function returns the percent rank of each student’s score based on the ascending order of the scores. The percent rank is calculated as (rank - 1) / (total rows - 1), where rank is the ordinal rank of the row. For example, the percent rank of Bob’s score is (1 - 1) / (5 - 1) = 0.0000, and the percent rank of Carol’s score is (5 - 1) / (5 - 1) = 1.0000.

Example 2: Calculating the percent rank of products’ sales

The following example shows how to use the PERCENT_RANK() function to calculate the percent rank of products’ sales in a table called products:

SELECT product, category, sales, PERCENT_RANK() OVER (PARTITION BY category ORDER BY sales) AS PercentRank
FROM products;

The output is:

+---------+----------+-------+-------------+
| product | category | sales | PercentRank |
+---------+----------+-------+-------------+
| A       | Books    |   100 |      0.0000 |
| B       | Books    |   200 |      0.3333 |
| C       | Books    |   300 |      0.6667 |
| D       | Books    |   400 |      1.0000 |
| E       | Toys     |   50  |      0.0000 |
| F       | Toys     |   100 |      0.5000 |
| G       | Toys     |   150 |      1.0000 |
+---------+----------+-------+-------------+

The function returns the percent rank of each product’s sales based on the ascending order of the sales within each category. The percent rank is calculated as (rank - 1) / (total rows in partition - 1), where rank is the ordinal rank of the row within the partition. For example, the percent rank of product A’s sales is (1 - 1) / (4 - 1) = 0.0000, and the percent rank of product G’s sales is (3 - 1) / (3 - 1) = 1.0000.

There are some other functions in Mariadb that are related to the PERCENT_RANK() function. They are:

  • RANK(): This function returns the ordinal rank of a row within a group of rows. The function assigns the same rank to rows with the same values, and leaves gaps between ranks. The function is also known as RANK_DENSE().
  • DENSE_RANK(): This function returns the ordinal rank of a row within a group of rows. The function assigns the same rank to rows with the same values, and does not leave gaps between ranks. The function is also known as RANK_EQUAL().
  • ROW_NUMBER(): This function returns the sequential number of a row within a group of rows. The function assigns a unique number to each row, starting from 1.

Conclusion

The PERCENT_RANK() function is a useful function in Mariadb that allows you to calculate the relative rank of a row within a group of rows. The function is helpful for calculating the percentile or the percentage of values that are less than or equal to a given value. You can also use other functions like RANK(), DENSE_RANK(), and ROW_NUMBER() to assign ranks to rows in different ways. I hope this article helped you understand how the PERCENT_RANK() function works in Mariadb.