Função MySQL PERCENT_RANK

Resumo : neste tutorial, você aprenderá como usar a PERCENT_RANK()função MySQL para calcular a classificação percentual de uma linha dentro de uma partição ou conjunto de resultados.

É PERCENT_RANK()uma função de janela que calcula a classificação percentual de uma linha em uma partição ou conjunto de resultados.

O seguinte mostra a sintaxe da PERCENT_RANK()função:

PERCENT_RANK() OVER (
  PARTITION BY partition_expression 
  ORDER BY 
    sort_expression [ASC | DESC]
)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A PERCENT_RANK()função retorna um número que varia de zero a um.

Para uma linha especificada, PERCENT_RANK()calcula a classificação dessa linha menos um, dividida por 1 a menos que o número de linhas na partição avaliada ou no conjunto de resultados da consulta:

(rank - 1) / (total_rows - 1)Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta fórmula, ranké a classificação de uma linha especificada e total_rowsé o número de linhas que estão sendo avaliadas.

A PERCENT_RANK()função sempre retorna zero para a primeira linha em uma partição ou conjunto de resultados. Os valores repetidos da coluna receberão o mesmo PERCENT_RANK()valor.

Semelhante a outras funções de janela, a PARTITION BYcláusula distribui as linhas em partições e ORDER BYespecifica a ordem lógica das linhas em cada partição. A PERCENT_RANK()função é calculada para cada partição ordenada de forma independente.

Ambas as cláusulas PARTITION BYe ORDER BYsão opcionais. No entanto, PERCENT_RANK()é uma função sensível à ordem, portanto, você deve sempre usar a ORDER BYcláusula.

Exemplos de funções PERCENT_RANK() do MySQL

Vamos criar uma nova tabela nomeada productLineSalescom base nas tabelas orders, orderDetailse productsdo banco de dados de exemplo :

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), 
    orderValue;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A productLineSalestabela armazena o resumo dos dados de vendas, incluindo linha de produto, ano do pedido e valor do pedido.

1) Usando MySQL PERCENT_RANK() no conjunto de resultados da consulta

A consulta a seguir encontra a classificação percentual de cada linha de produto por valores de pedido:

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;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste exemplo:

• Primeiro, usamos uma expressão de tabela comum para resumir os valores dos pedidos por linhas de produtos.
• Em segundo lugar, usamos PERCENT_RANK()para calcular a classificação percentual do valor do pedido de cada produto. Além disso, utilizamos a ROUND()função de arredondar os valores para 2 casas decimais para uma melhor representação.

Aqui está a saída:

+------------------+------------+-----------------+
| productLine      | orderValue | percentile_rank |
+------------------+------------+-----------------+
| Trains           |  188532.92 |               0 |
| Ships            |  661622.34 |            0.17 |
| Planes           |  944295.37 |            0.33 |
| Trucks and Buses | 1014787.52 |             0.5 |
| Motorcycles      | 1114192.13 |            0.67 |
| Vintage Cars     | 1782234.23 |            0.83 |
| Classic Cars     | 3782065.50 |               1 |
+------------------+------------+-----------------+
7 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Aqui estão algumas análises da saída:

• Os valores dos pedidos Trainsnão eram melhores do que quaisquer outras linhas de produtos, o que foi representado com zero.
•  Vintage Carsteve desempenho melhor que 50% de outros produtos.
•  Classic Carsteve melhor desempenho do que qualquer outra linha de produtos, portanto sua classificação percentual é 1 ou 100%

2) Usando MySQL PERCENT_RANK() na partição

A instrução a seguir retorna a classificação percentual das linhas de produtos por valores de pedido em cada ano:

SELECT
    productLine,
    orderYear,
    orderValue,
    ROUND(
    PERCENT_RANK()
    OVER (
        PARTITION BY orderYear
        ORDER BY orderValue
    ),2) percentile_rank
FROM
    productLineSales;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

Neste exemplo, dividimos os valores dos pedidos das linhas de produtos por ano do pedido. O PERCENT_RANK()então aplicado a cada partição.

Por exemplo, em 2003, Vintage Cars o desempenho foi superior a 50% de outras linhas de produtos, enquanto em 2004, os Navios tiveram um desempenho superior a 50% de outros produtos.

Resumo

• Use a função MySQL PERCENT_RANK()para calcular a classificação percentual de uma linha em uma partição ou conjunto de resultados.