MySQL PERCENT_RANK函数

时间:2019-11-20 08:52:28  来源:igfitidea点击:

简介:在本教程中,您将学习如何使用MySQL PERCENT_RANK()函数来计算分区或结果集中某行的百分比排名。

PERCENT_RANK()是一个窗口函数,用于计算分区或结果集中某行的百分数等级。

下面显示了PERCENT_RANK()函数的语法:

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

PERCENT_RANK()函数返回一个从零到一的数字。

对于指定的行,PERCENT_RANK()计算该行的等级减去一,再除以所评估的分区或查询结果集中的行数减去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()函数示例

让我们根据示例数据库中的orders,orderDetails和products表创建一个名为productLineSales的新表:

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表存储销售数据的摘要,包括产品线,订单年和订单价值。

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

以下查询按订单值查找每个产品系列的百分等级:

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位,以获得更好的表示形式。

这是输出:

以下是输出的一些分析:

  • 火车的订单值并不比其他任何产品线都要好,这些产品线用零表示。

  • 老爷车的性能优于其他产品的50%。

  • 老爷车的表现优于其他任何产品线,因此其百分数排名是1或100%

在分区上使用MySQL PERCENT_RANK()

以下语句返回每年按订单值列出的产品线的百分位排名:

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

这是输出:

在此示例中,我们将产品系列的订单价值除以订单年份。
然后将PERCENT_RANK()应用于每个分区。

例如,2013年老式汽车的性能优于其他产品线的50%,而2014年船舶的性能优于其他产品的50%。

在本教程中,您学习了如何使用MySQL PERCENT_RANK()函数来计算分区或结果集中某行的百分数等级。