摘要:在本教程中,您将学习如何使用 MySQL PERCENT_RANK()
函数来计算分区或结果集中的行的百分位数排名。
PERCENT_RANK()
是一个窗口函数,用于计算分区或结果集中行的百分位数排名。
下面显示了PERCENT_RANK()
函数的语法:
PERCENT_RANK()
OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)
Code language: SQL (Structured Query Language) (sql)
PERCENT_RANK()
函数返回一个范围从 0 到 1 的数字。
对于指定的行, PERCENT_RANK()
计算该行的排名减一,再除以 1,小于评估的分区或查询结果集中的行数:
(rank - 1) / (total_rows - 1)
Code language: SQL (Structured Query Language) (sql)
在此公式中, 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);
Code language: SQL (Structured Query Language) (sql)
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;
Code language: SQL (Structured Query Language) (sql)
在这个例子中:
- 首先,我们使用通用表表达式来按产品线汇总订单值。
- 其次,我们使用
PERCENT_RANK()
计算每个产品的订单价值的百分位数排名。此外,我们使用ROUND()
函数将值四舍五入到小数点后 2 位,以便更好地表示。
这是输出:
以下是对输出的一些分析:
Trains
的订单价值并不比其他产品线好,用零来表示。-
Vintage Cars
表现优于 50% 的其他产品。 -
Classic Cars
表现比任何其他产品线都要好,因此其百分比排名为 1 或 100%
在分区上使用 MySQL PERCENT_RANK()
以下语句返回每年按订单值排列的产品线的百分位排名:
SELECT
productLine,
orderYear,
orderValue,
ROUND(
PERCENT_RANK()
OVER (
PARTITION BY orderYear
ORDER BY orderValue
),2) percentile_rank
FROM
productLineSales;
Code language: SQL (Structured Query Language) (sql)
这是输出:
在此示例中,我们按订单年份划分产品线的订单值。然后将PERCENT_RANK()
应用于每个分区。
例如,2013 年老Vintage Cars
表现优于其他产品线的 50%,而 2014 年船舶的表现优于其他产品的 50%。
在本教程中,您学习了如何使用 MySQL PERCENT_RANK()
函数来计算分区或结果集中的行的百分位数排名。