MySQL PERCENT_RANK 函数

---

简介：在本教程中，您将学习如何使用MySQL PERCENT_RANK()函数计算分区或结果集中行的百分位数排名。 这PERCENT_RANK()是一个窗口函数，用于计算分区或结果集中行的百分位数。 以下显示了PERCENT_RANK()函数的语法：

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

PERCENT_RANK()函数返回一个从0到1的数字。 对于指定的行，PERCENT_RANK()计算行的等级减1，除以评估的分区或查询结果集中的行数减1：

(rank - 1) / (total_rows - 1)

在此公式中，rank是指定行的等级，total_rows是要计算的行数。 PERCENT_RANK()对于分区或结果集中的第一行，函数始终返回零。重复的列值将接收相同的PERCENT_RANK()值。 与其他窗口函数类似，PARTITION BY子句将行分配到分区中，ORDER BY子句指定每个分区中行的逻辑顺序。PERCENT_RANK()为每个有序分区独立计算函数。 两个PARTITION BY和ORDER BY子句都是可选项。但是，它PERCENT_RANK()是一个顺序敏感函数，因此，您应始终使用ORDER BY子句。

---

MySQL PERCENT_RANK() 函数示例

让我们创建一个名为新表productLineSales基础上的orders，orderDetails以及products从表中示例数据库：

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);

productLineSales表存储销售数据的摘要，包括产品系列，订单年份和订单值。

+------------------+-----------+------------+
| productLine      | orderYear | orderValue |
+------------------+-----------+------------+
| Vintage Cars     |      2013 |    4080.00 |
| Classic Cars     |      2013 |    5571.80 |
| Trucks and Buses |      2013 |    3284.28 |
| Trains           |      2013 |    2770.95 |
| Ships            |      2013 |    5072.71 |
| Planes           |      2013 |    4825.44 |
| Motorcycles      |      2013 |    2440.50 |
| Classic Cars     |      2014 |    8124.98 |
| Vintage Cars     |      2014 |    2819.28 |
| Trains           |      2014 |    4646.88 |
| Ships            |      2014 |    4301.15 |
| Planes           |      2014 |    2857.35 |
| Motorcycles      |      2014 |    2598.77 |
| Trucks and Buses |      2014 |    4615.64 |
| Motorcycles      |      2015 |    4004.88 |
| Classic Cars     |      2015 |    5971.35 |
| Vintage Cars     |      2015 |    5346.50 |
| Trucks and Buses |      2015 |    6295.03 |
| Trains           |      2015 |    1603.20 |
| Ships            |      2015 |    3774.00 |
| Planes           |      2015 |    4018.00 |
+------------------+-----------+------------+
21 rows in set (0.02 sec)

PERCENT_RANK()在查询结果集上使用MySQL

以下查询按订单值查找每个产品系列的百分位数排名：

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;

在这个例子中：

• 首先，我们使用公用表表达式按产品线汇总订单值。
• 其次，我们用它PERCENT_RANK()来计算每种产品的订单价值的百分等级。此外，我们使用ROUND()函数将值舍入为2十进制，以获得更好的表示。

这是输出：

+------------------+------------+-----------------+
| productLine      | orderValue | percentile_rank |
+------------------+------------+-----------------+
| Trains           |    9021.03 |            0.00 |
| Motorcycles      |    9044.15 |            0.17 |
| Planes           |   11700.79 |            0.33 |
| Vintage Cars     |   12245.78 |            0.50 |
| Ships            |   13147.86 |            0.67 |
| Trucks and Buses |   14194.95 |            0.83 |
| Classic Cars     |   19668.13 |            1.00 |
+------------------+------------+-----------------+
7 rows in set (0.01 sec)

以下是输出中的一些分析：

• 订单价值Trains并不比任何其他产品线更好，后者用零表示。
•  Vintage Cars 表现优于50％的其他产品。
•  Classic Cars 表现优于任何其他产品系列，因此其百分比等级为1或100％

PERCENT_RANK()在分区上使用MySQL

以下语句按年度中的订单值返回产品系列的百分位数排名：

SELECT
    productLine,
    orderYear,
    orderValue,
    ROUND(
    PERCENT_RANK()
    OVER (
        PARTITION BY orderYear
        ORDER BY orderValue
    ),2) percentile_rank
FROM
    productLineSales;

这是输出：

+------------------+-----------+------------+-----------------+
| productLine      | orderYear | orderValue | percentile_rank |
+------------------+-----------+------------+-----------------+
| Motorcycles      |      2013 |    2440.50 |            0.00 |
| Trains           |      2013 |    2770.95 |            0.17 |
| Trucks and Buses |      2013 |    3284.28 |            0.33 |
| Vintage Cars     |      2013 |    4080.00 |            0.50 |
| Planes           |      2013 |    4825.44 |            0.67 |
| Ships            |      2013 |    5072.71 |            0.83 |
| Classic Cars     |      2013 |    5571.80 |            1.00 |
| Motorcycles      |      2014 |    2598.77 |            0.00 |
| Vintage Cars     |      2014 |    2819.28 |            0.17 |
| Planes           |      2014 |    2857.35 |            0.33 |
| Ships            |      2014 |    4301.15 |            0.50 |
| Trucks and Buses |      2014 |    4615.64 |            0.67 |
| Trains           |      2014 |    4646.88 |            0.83 |
| Classic Cars     |      2014 |    8124.98 |            1.00 |
| Trains           |      2015 |    1603.20 |            0.00 |
| Ships            |      2015 |    3774.00 |            0.17 |
| Motorcycles      |      2015 |    4004.88 |            0.33 |
| Planes           |      2015 |    4018.00 |            0.50 |
| Vintage Cars     |      2015 |    5346.50 |            0.67 |
| Classic Cars     |      2015 |    5971.35 |            0.83 |
| Trucks and Buses |      2015 |    6295.03 |            1.00 |
+------------------+-----------+------------+-----------------+
21 rows in set (0.01 sec)

在此示例中，我们按订单年划分产品线的订单值。将PERCENT_RANK()然后被施加到每个分区。 例如，2013年Vintage Cars 表现优于其他产品线的50％，而在2014年，船舶表现优于其他产品的50％。 在本教程中，您学习了如何使用MySQL PERCENT_RANK()函数计算分区或结果集中行的百分位数。

---