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()函数计算一组值中值的累积分布。