In this tutorial, we will study Percent_Rank() function in MySQL. This function takes a column name as the argument and returns the number of rows with greater values than the sample value. Without further ado, let’s get started!
Introduction to MySQL Percent_Rank()
The Percent_Rank() function is one of many useful functions that are available in MySQL. This function works by taking a single column as an input, and returning the number of rows in that column that contains values that are greater than the value in the current row. In other words, if you have a column with values 1, 2, 3, and 4, and your current row has a value of 3, the Percent_Rank() function would return 0.75, or 3/4.
This function is useful for a 2 main reasons.
- First, it can be used to quickly identify the most popular values in a dataset. If you have hundreds, or even thousands of rows, using Percent_Rank() can help you find the most common values without having to go through every single row manually.
- Second, Percent_Rank() can be used as part of more advanced statistical analyses. For example, if you wanted to determine the correlation between two different columns in a dataset, you could use Percent_Rank() and other functions to calculate the Pearson coefficient of correlation.
In MySQL, the Percent_Rank() function is a window function that is used to calculate the percentile rank of a row within a result set.
The syntax for the following function is-
PERCENT_RANK() OVER ( PARTITION BY expr,... ORDER BY expr [ASC|DESC],... )Code language: SQL (Structured Query Language) (sql)
The result returned by the Percent_Rank() Function ranges from 0 to 1. The formula to calculate the Percent_Rank() function is as follows-
(rank - 1) / (total_rows - 1)
The Percent_Rank() function will always return 0 for the first row in the result set.
Here to illustrate the example of Percent_Rank() function we will use the classicmodels database. A new table named details will be created based on the prior tables named orders, orderDetails, and products. The code for the following is-
Create table details Select productLine, YEAR(orderDate) OrderYear, quantityOrdered * priceEach Value From orderDetails Inner Join orders Using (orderNumber) Inner Join products Using (productCode) Group By productLine, YEAR(orderDate);Code language: SQL (Structured Query Language) (sql)
Select * From details;Code language: SQL (Structured Query Language) (sql)
Query over result set using MySQL Percent_Rank() function
Now we will write a code that will find the percentile rank of every product.
With b As ( Select productLine, Sum(Value) Value From details Group By productLine ) Select productLine, Value, Round( Percent_Rank() OVER ( ORDER BY Value) ,2) percentile_rank From b;Code language: SQL (Structured Query Language) (sql)
Here we can see that we have calculated the percentile_rank of every product Line.
MySQL Percent_Rank() with Partition
Now we will write a query that will return the percentile rank of each product line arranged in order with respect to the values.
Select productLine, orderYear, Value, ROUND( PERCENT_RANK() OVER ( PARTITION BY orderYear ORDER By Value),2) percentile_rank From details;Code language: SQL (Structured Query Language) (sql)
In this tutorial, we studied how to use MySQL Percent_Rank() function. Overall, the Percent_Rank() function is a powerful tool that can be used for a variety of purposes. Whether you are analyzing data for business or academic purposes, it can help you gain deeper insights into your data and make more informed decisions. So why not give it a try today?