SQL Server Cume_dist()函數

在本教學中，將學習如何使用SQL Server CUME_DIST()函數計算一組值中值的累積分佈。

SQL Server CUME_DIST()函數簡介

有時，希望生成一個報表，其中包含數據集中的頂部或底部有x%值，例如，銷售人員占淨銷售額的前5%。 SQL Server實現此目的的一種方法是使用CUME_DIST()函數。

CUME_DIST()函數計算一組值中的值的累積分佈。 簡單地說，它計算一組值中值的相對位置。

以下是CUME_DIST()函數的語法：

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

下麵來詳細研究一下這個語法。

• PARTITION BY子句將行分配到應用了CUME_DIST()函數的多個分區。PARTITION BY子句是可選的。如果省略PARTITION BY子句，CUME_DIST()函數會將整個結果集視為單個分區。
• ORDER BY子句指定應用CUME_DIST()函數的每個分區中行的邏輯順序。 ORDER BY子句將NULL值視為最低值。
• 返回值 - CUME_DIST()求值結果大於0且小於或等於1。

0 < CUME_DIST() <= 1

該函數為相同的綁定值返回相同的累積分佈值。

SQL Server CUME_DIST()示例

下麵來看一些使用CUME_DIST()函數的例子。

1. SQL Server CUME_DIST()函數應用在結果集示例
以下語句計算2017年每位銷售人員的銷售百分位數：

SELECT
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales,
    CUME_DIST() OVER (
        ORDER BY net_sales DESC
    ) cume_dist
FROM
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE
    year = 2017;

執行上面查詢語句，得到以下結果：

如輸出所示，50％的銷售人員淨銷售額超過28.5萬。

2. SQL Server CUME_DIST()函數在分區上使用示例

此示例使用CUME_DIST()函數計算2016年和2017年每個銷售人員的銷售百分位數。

SELECT
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales,
    year,
    CUME_DIST() OVER (
        PARTITION BY year
        ORDER BY net_sales DESC
    ) cume_dist
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子句按照應用CUME_DIST()函數的淨銷售額從高到低對每個分區中的行進行排序。

查詢在2016年和2017年通過淨銷售額獲得前20％的銷售人員，請使用以下查詢：

WITH cte_sales AS (
    SELECT
        CONCAT_WS(' ',first_name,last_name) full_name,
        net_sales,
        year,
        CUME_DIST() OVER (
            PARTITION BY year
            ORDER BY net_sales DESC
        ) cume_dist
    FROM
        sales.vw_staff_sales t
        INNER JOIN sales.staffs m
            ON m.staff_id = t.staff_id
    WHERE
        year IN (2016,2017)
)
SELECT
    *
FROM
    cte_sales
WHERE
    cume_dist <= 0.20;

執行上面查詢語句，得到以下結果：

在本教學中，學習了如何使用SQL Server CUME_DIST()函數計算一組值中值的累積分佈。