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()函數計算一組行中行的相對排名。