SQL Server Percent_rank()函数

在本教程中，将学习如何使用SQL Server PERCENT_RANK()函数来计算一组行中行的相对排名。

SQL Server PERCENT_RANK()函数简介

PERCENT_RANK()函数类似于CUME_DIST()函数。 PERCENT_RANK()函数计算结果集的分区中值的相对位置。

SQL Server PERCENT_RANK()函数的语法如下：

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

在上面语法中，

• PARTITION BY子句将行分配到应用PERCENT_RANK()函数的多个分区。 PARTITION BY子句是可选的。 如果未指定它，该函数会将整个结果集视为单个分区。
• ORDER BY子句指定每个分区中行的逻辑顺序。由于PERCENT_RANK()对顺序敏感，因此需要order_by_clause。

返回值

PERCENT_RANK()的结果大于0且小于或等于1。

0 < PERCENT_RANK() <= 1

第一行的等级值为零。 绑定值评估为相同的累积分布值。PERCENT_RANK()函数默认包含NULL值，并将它们视为最低值。

SQL Server PERCENT_RANK()示例

下面来看一些使用PERCENT_RANK()函数的例子。以下语句为演示创建名为sales.vw_staff_sales的新视图。

CREATE VIEW sales.vw_staff_sales(
    staff_id, 
    year, 
    net_sales
) AS
SELECT 
    staff_id, 
    YEAR(order_date), 
    ROUND(SUM(quantity*list_price*(1-discount)),0)
FROM 
    sales.orders o
INNER JOIN sales.order_items i on i.order_id = o.order_id
WHERE 
    staff_id IS NOT NULL
GROUP BY 
    staff_id, 
    YEAR(order_date);

1. 在结果集中使用PERCENT_RANK()函数示例
此示例使用PERCENT_RANK()函数来计算2016年每位销售人员的销售百分位数：

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    PERCENT_RANK() OVER (
        ORDER BY net_sales DESC
    ) percent_rank
FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    YEAR = 2016;

执行上面查询语句，得到以下结果：

要使输出更具可读性，可以使用FORMAT()函数格式化百分比排名百分比(％)：

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    FORMAT(
        PERCENT_RANK() OVER (
            ORDER BY net_sales DESC
        ) ,
    'P') percent_rank

FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    YEAR = 2016;

执行上面查询语句，得到以下结果：

2. 在分区示例上使用PERCENT_RANK()函数
以下示例使用PERCENT_RANK()计算2016年和2017年每位员工的销售百分位数。

SELECT 
    year,
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    FORMAT(
        PERCENT_RANK() OVER (
            PARTITION BY year
            ORDER BY net_sales DESC
        ) ,
    'P') percent_rank

FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    YEAR IN (2016,2017);

执行上面查询语句，得到以下结果：

在这个例子中：

• PARTITION BY子句按年将行分为两个分区，一个用于2016年，另一个用于2017年。
• ORDER BY子句按净销售额从高到低对每个分区中的行进行排序。
• PERCENT_RANK()函数分别应用于每个分区，并在跨越分区边界时重新计算等级。

在本教程中，学习了如何使用SQL Server PERCENT_RANK()函数计算一组行中行的相对排名。