Функция 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 является необязательным. Функция CUME_DIST() будет обрабатывать весь результирующий набор как один раздел, если вы опустите предложение PARTITION BY.

Предложение ORDER BY

Предложение ORDER BY определяет логический порядок строк в каждой секции, к которой применяется функция CUME_DIST(). Предложение ORDER BY рассматривает значения NULL как наименьшие возможные значения.

Возвращаемое значение

Результат CUME_DIST() больше 0 и меньше или равен 1.

0 < CUME_DIST() <= 1

Функция возвращает те же самые значения кумулятивного распределения для тех же самых значений связей.

Примеры SQL Server CUME_DIST()

Давайте рассмотрим несколько примеров использования функции CUME_DIST().

Пример использования функции 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% торгового персонала имеют чистый объем продаж более 285 тыс.

Использование функции 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().

Чтобы получить 20% лучших сотрудников отдела продаж по чистым продажам за 2016 и 2017 годы, используйте следующий запрос:

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() для расчета кумулятивного распределения значения в группе значений.