PERCENT RANK: примеры (SQL)

Использование PERCENT_RANK в SQL Server для анализа данных
Раздел: Аналитические функции, Оконные
PERCENT_RANK( OVER [PARTITION BY ] ORDER BY ): float
типовые примерыPERCENT RANK на mySQL, Oracle, PostgreSQL, SQLitePERCENT RANK изменения sqlпримеры расширенные

Функция PERCENT_RANK в MS SQL Server

Функция PERCENT_RANK относится к оконным функциям SQL Server и вычисляет относительный ранг строки в наборе результатов. Она возвращает значение от 0 до 1, представляющее позицию строки в виде процентного соотношения относительно других строк в секции.

Использование функции актуально при анализе распределения данных, определении перцентилей и сравнении относительного положения значений. Частое применение включает финансовый анализ, статистическую обработку и оценку производительности.

Синтаксис функции:

PERCENT_RANK() OVER ( [ partition_by_clause ] order_by_clause )

Функция не принимает аргументов, но требует обязательного указания предложения ORDER BY в рамках оператора OVER. Опциональное предложение PARTITION BY разделяет результирующий набор на секции, к которым применяются вычисления.

Возвращаемое значение вычисляется по формуле (RK - 1) / (NR - 1), где RK - ранг текущей строки, NR - общее количество строк в секции. Первая строка получает значение 0, последняя - 1. При NR = 1 функция возвращает 0.

Примеры применения PERCENT_RANK

Простой пример с ранжированием сотрудников по зарплате:

SELECT 
    EmployeeName,
    Salary,
    PERCENT_RANK() OVER (ORDER BY Salary) AS SalaryPercentRank
FROM Employees;
EmployeeName | Salary | SalaryPercentRank
-------------|--------|------------------
Иванов      | 30000  | 0.0
Петров      | 45000  | 0.25
Сидоров     | 60000  | 0.5
Кузнецов    | 75000  | 0.75
Васильев    | 90000  | 1.0

Использование с секционированием по отделам:

SELECT 
    Department,
    EmployeeName,
    Salary,
    PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Salary) AS DeptPercentRank
FROM Employees;

Похожие функции в MS SQL

Функция CUME_DIST вычисляет кумулятивное распределение, возвращая долю строк со значениями меньше или равными текущему. Различие заключается в формуле расчета: CUME_DIST = число строк с меньшим или равным значением / общее число строк.

Функция RANK присваивает ранг с пропусками при одинаковых значениях, а DENSE_RANK - без пропусков. Эти функции возвращают целые числа вместо процентных соотношений.

NTILE делит набор на указанное количество групп. Выбор функции зависит от задачи: PERCENT_RANK подходит для процентного позиционирования, CUME_DIST - для анализа распределения, RANK/DENSE_RANK - для ранжирования.

Типичные ошибки использования

Отсутствие предложения ORDER BY вызывает ошибку:

-- Неправильно
SELECT PERCENT_RANK() OVER () FROM Employees;
Сообщение об ошибке: Оконная функция "PERCENT_RANK" требует наличия предложения ORDER BY.

Неправильное понимание результатов при одинаковых значениях:

SELECT 
    Score,
    PERCENT_RANK() OVER (ORDER BY Score) AS PctRank
FROM (VALUES (70), (70), (85), (90)) AS T(Score);
Score | PctRank
------|--------
70    | 0.0
70    | 0.0
85    | 0.666666666666667
90    | 1.0

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

Изменения в последних версиях

Функция PERCENT RANK была представлена в SQL Server 2012 и с тех пор не претерпела значительных изменений в синтаксисе или поведении. В SQL Server 2017 и более поздних версиях улучшена производительность оконных функций при использовании в сочетании с columnstore индексами.

В SQL Server 2022 продолжена оптимизация производительности оконных функций, особенно при работе с большими наборами данных и в сочетании с другими аналитическими функциями.

Расширенные примеры использования

Анализ продаж с группировкой по кварталам:

Пример sql
SELECT 
    Year,
    Quarter,
    SalesAmount,
    PERCENT_RANK() OVER (PARTITION BY Year, Quarter ORDER BY SalesAmount DESC) AS SalesRankPercent
FROM Sales
ORDER BY Year, Quarter, SalesRankPercent;

Определение перцентилей успеваемости студентов:

Пример sql
WITH StudentRanks AS (
    SELECT 
        StudentID,
        AVG(Grade) AS AvgGrade,
        PERCENT_RANK() OVER (ORDER BY AVG(Grade)) AS GradePercentRank
    FROM Grades
    GROUP BY StudentID
)
SELECT 
    StudentID,
    AvgGrade,
    CASE 
        WHEN GradePercentRank >= 0.9 THEN 'Топ 10%'
        WHEN GradePercentRank >= 0.7 THEN 'Топ 30%'
        ELSE 'Остальные'
    END AS PerformanceGroup
FROM StudentRanks;

Сравнение относительной позиции сотрудников в разных отделах:

Пример sql
SELECT 
    Department,
    EmployeeName,
    Salary,
    PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Salary) AS DeptPercent,
    PERCENT_RANK() OVER (ORDER BY Salary) AS OverallPercent,
    PERCENT_RANK() OVER (ORDER BY Salary) - 
        PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Salary) AS PercentDiff
FROM Employees
ORDER BY Department, Salary;

Аналоги функции в других СУБД

В PostgreSQL и Oracle функция PERCENT_RANK имеет идентичный синтаксис и поведение:

-- PostgreSQL/Oracle
SELECT 
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) as pct_rank
FROM employees;

MySQL поддерживает PERCENT_RANK начиная с версии 8.0:

-- MySQL 8.0+
SELECT 
    name,
    score,
    PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM students;

SQLite не имеет встроенной функции PERCENT_RANK, но можно эмулировать вычисления:

-- SQLite
SELECT 
    value,
    (RANK() OVER (ORDER BY value) - 1.0) / (COUNT(*) OVER () - 1.0) AS pct_rank
FROM table;

MS SQL PERCENT_RANK function comments

En
PERCENT RANK Calculates the relative rank of a row within a group of rows