Функция 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

Первая строка имеет значение ранга 0. Совпадающие значения оцениваются с одинаковым значением кумулятивного распределения.

Функция 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);

Пример использования функции SQL Server 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;

На следующем рисунке показан новый результат:

Пример использования функции SQL Server 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() для вычисления относительного ранга строки в группе строк.