mysql percent_MySQL PERCENT_RANK 函数

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()来计算每种产品的订单价值的百分等级。此外，我们使用

这是输出：

+------------------+------------+-----------------+

| 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()函数计算分区或结果集中行的百分位数。