The MySQL PERCENT_RANK function is used to calculate the relative rank of a row within a result set as a percentage. It is particularly useful in scenarios where you want to know the position of a specific row in relation to the entire result set.

The PERCENT_RANK function returns a value between 0 and 1, inclusive. A value of 0 indicates that the current row has the lowest value in the partition, while a value of 1 indicates that the current row has the highest value in the partition.

Syntax

Here is the syntax for the PERCENT_RANK function in MySQL:

PERCENT_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

PARTITION BY: This clause divides the result set into partitions to which the PERCENT_RANK function is applied independently. It is optional, and if not specified, the function treats the whole result set as a single partition.

ORDER BY: Specifies the columns by which the result set is ordered. The ASC (ascending) or DESC (descending) keyword can be used to define the sort order. It is mandatory for the PERCENT_RANK function.

Example

Now, let’s look at an example to illustrate the usage of the PERCENT_RANK function:

Assume we have a table named sales with the following data:

CREATE TABLE sales (
    product_id INT,
    sale_date DATE,
    revenue DECIMAL(10, 2)
);

INSERT INTO sales VALUES
(1, '2022-01-01', 1000.00),
(2, '2022-01-02', 1500.00),
(1, '2022-01-03', 800.00),
(2, '2022-01-04', 1200.00),
(1, '2022-01-05', 2000.00);

Now, let’s use the PERCENT_RANK function to find the percent rank of revenue for each sale date:

SELECT
    product_id,
    sale_date,
    revenue,
    PERCENT_RANK() OVER (ORDER BY revenue) AS percent_rank
FROM
    sales;

This query calculates the percent rank of revenue for each row in the sales table, ordering the result set by the revenue column in ascending order. The result will show the product_id, sale_date, revenue, and the corresponding percent rank.

In summary, the MySQL PERCENT_RANK function is a versatile window function for analyzing data distribution and identifying relative positions of values within a group. Its ability to calculate percentage ranks within partitions makes it particularly useful for evaluating performance and identifying trends in various data sets.