1

PERCENTILE CONT: примеры (SQL)

Функция PERCENTILE_CONT для расчета процентилей в SQL Server
Раздел: Аналитические функции, Оконные
PERCENTILE_CONT(numeric_literal WITHIN GROUP ORDER BY expression OVER [PARTITION BY ]): Same as expression
типовые примерыPERCENTILE CONT на mySQL, Oracle, PostgreSQL, SQLitePERCENTILE CONT изменения sqlпримеры расширенные

Описание функции PERCENTILE_CONT

Функция PERCENTILE_CONT в MS SQL Server является аналитической функцией, которая вычисляет процентиль на основе непрерывного распределения значений указанного столбца. Её часто применяют для статистического анализа данных, например, для расчета медианы, квартилей или других заданных процентов.

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

Синтаксис и аргументы

PERCENTILE_CONT ( numeric_literal )
    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
    OVER ( [ <partition_by_clause> ] )

Аргументы:

  • numeric_literal: Константа с плавающей запятой или имеющая тип, который может быть неявно преобразован в float. Значение должно находиться в диапазоне от 0.0 до 1.0 включительно. Этот аргумент задает вычисляемый процентиль (например, 0.5 для медианы).
  • WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] ): Определяет сортировку списка значений для вычисления процентиля. Может быть указано только одно выражение order_by_expression. Порядок по умолчанию — ASC. Тип данных выражения должен быть числовым, даты или любого типа, для которого определено сравнение.
  • OVER ( [ <partition_by_clause> ] ): Определяет секции (группы) строк, в рамках которых вычисляется функция. Если предложение PARTITION BY не указано, функция обрабатывает все строки результирующего набора как одну группу.

Возвращаемое значение: Тип возвращаемого значения определяется типом данных order_by_expression в предложении WITHIN GROUP. Функция возвращает интерполированное значение, которое может быть NULL, если в секции нет строк.

Краткие примеры использования

Пример 1: Вычисление медианы (50-го процентиля) зарплат по всем сотрудникам.

SELECT DISTINCT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary)
        OVER () AS MedianSalary
FROM Employees;
MedianSalary
-------------
48000.00

Пример 2: Вычисление медианы зарплаты в каждом отделе.

SELECT DepartmentID,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary)
           OVER (PARTITION BY DepartmentID) AS DeptMedianSalary
FROM Employees
ORDER BY DepartmentID;
DepartmentID  DeptMedianSalary
------------  ----------------
1             42000.00
1             42000.00
2             65000.00
2             65000.00
3             32000.00

Пример 3: Вычисление 90-го процентиля (верхнего дециля) с сортировкой по убыванию.

SELECT DISTINCT
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY HireDate DESC)
        OVER () AS LatestHirePercentile
FROM Employees;
LatestHirePercentile
-------------------
2022-03-15

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

  • PERCENTILE_DISC: Вычисляет процентиль на основе дискретного распределения. В отличие от PERCENTILE_CONT, которая интерполирует значение, PERCENTILE_DISC возвращает конкретное значение из набора, соответствующее заданному процентилю. Используют, когда требуется получить фактически существующее в данных значение.
  • MEDIAN: В MS SQL Server отсутствует отдельная функция MEDIAN, но медиану вычисляют с помощью PERCENTILE_CONT(0.5). В других СУБД (например, Oracle) функция MEDIAN может присутствовать как синоним.
  • Агрегатные функции с предложением OVER: Для простых статистических показателей (среднее, максимум) иногда подходят функции AVG или MAX. Однако они не дают процентильного распределения.

Выбор между PERCENTILE_CONT и PERCENTILE_DISC зависит от задачи. Если необходим результат, который мог бы быть получен при непрерывном распределении (например, для последующих расчетов), подходит PERCENTILE_CONT. Для получения реального наблюдаемого значения, например, зарплаты конкретного сотрудника, выбирают PERCENTILE_DISC.

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

Ошибка 1: Указание значения процентиля вне диапазона от 0.0 до 1.0.

SELECT PERCENTILE_CONT(1.5) WITHIN GROUP (ORDER BY Salary) OVER () FROM Employees;
Msg 8116, Level 16, State 1, Line 1
Argument 'percentile' must be between 0.0 and 1.0.

Ошибка 2: Использование нескольких столбцов в предложении ORDER BY внутри WITHIN GROUP.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary, HireDate) OVER () FROM Employees;
Msg 10758, Level 16, State 1, Line 1
Within group clause may contain only one order by expression.

Ошибка 3: Попытка использования функции без предложения OVER. Это обязательный синтаксис для PERCENTILE_CONT в MS SQL.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) FROM Employees;
Msg 10753, Level 15, State 3, Line 1
The function 'PERCENTILE_CONT' must have an OVER clause.

Ошибка 4: Использование типа данных, который не может быть неявно преобразован для сортировки или вычислений (например, ntext).

История изменений функции

Функция PERCENTILE_CONT была введена в MS SQL Server начиная с версии 2012 (11.x). В более ранних версиях для вычисления процентилей требовалось писать сложные запросы с использованием ранжирующих функций и агрегации.

Начиная с SQL Server 2022 (16.x), не было внесено значительных изменений в синтаксис или поведение функции. Однако общие улучшения производительности обработки аналитических функций могут затрагивать и выполнение PERCENTILE_CONT.

Важно учитывать, что функция доступна не во всех выпусках SQL Server. Она присутствует в Enterprise, Developer и Evaluation выпусках. В Standard выпуске она появилась начиная с версии SQL Server 2016 SP1.

Расширенные примеры применения

Пример 1: Сравнение медианы и среднего значения по отделам.

Пример sql
SELECT DISTINCT
    DepartmentID,
    AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgSalary,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary)
        OVER (PARTITION BY DepartmentID) AS MedianSalary
FROM Employees
ORDER BY DepartmentID;
DepartmentID  AvgSalary    MedianSalary
------------  ---------    ------------
1             45000.00     42000.00
2             70000.00     65000.00
3             32000.00     32000.00

Пример 2: Вычисление нескольких процентилей (медиана, нижний и верхний квартили) в одном запросе.

Пример sql
SELECT DISTINCT
    DepartmentID,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Salary)
        OVER (PARTITION BY DepartmentID) AS Q1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary)
        OVER (PARTITION BY DepartmentID) AS Median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Salary)
        OVER (PARTITION BY DepartmentID) AS Q3
FROM Employees;
DepartmentID  Q1        Median    Q3
------------  --------  --------  --------
1             38000.00  42000.00  50000.00
2             55000.00  65000.00  80000.00
3             31000.00  32000.00  33000.00

Пример 3: Использование с типом данных даты для анализа сроков.

Пример sql
SELECT DISTINCT
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY OrderDate)
        OVER (PARTITION BY CustomerID) AS P90_OrderDate
FROM Orders;
-- Возвращает дату, к которой выполнено 90% заказов каждого клиента.

Пример 4: Фильтрация строк на основе вычисленного процентиля (требуется вложенный запрос или CTE).

Пример sql
WITH SalaryStats AS (
    SELECT EmployeeID, Salary, DepartmentID,
           PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Salary)
               OVER (PARTITION BY DepartmentID) AS DeptQ3Salary
    FROM Employees
)
SELECT EmployeeID, Salary, DepartmentID, DeptQ3Salary
FROM SalaryStats
WHERE Salary > DeptQ3Salary
ORDER BY DepartmentID, Salary DESC;
-- Находит сотрудников с зарплатой выше 3-го квартиля в своем отделе.
EmployeeID  Salary    DepartmentID  DeptQ3Salary
----------  --------  ------------  ------------
105         85000.00  2             80000.00
103         52000.00  1             50000.00

Пример 5: Обработка значений NULL. Функция игнорирует значения NULL при сортировке. Если все значения в секции — NULL, результат также будет NULL.

Пример sql
-- Вставка строки с NULL зарплатой
INSERT INTO Employees (EmployeeID, Salary, DepartmentID) VALUES (999, NULL, 1);

SELECT DepartmentID,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary)
           OVER (PARTITION BY DepartmentID) AS MedianSalary
FROM Employees
WHERE DepartmentID = 1;
DepartmentID  MedianSalary
------------  ------------
1             42000.00
1             42000.00
... (строки с NULL в Salary не влияют на расчет)

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

  • Oracle: Имеет идентичные функции PERCENTILE_CONT и PERCENTILE_DISC с аналогичным синтаксисом. Пример:
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
    FROM employees;
  • PostgreSQL: Поддерживает PERCENTILE_CONT и PERCENTILE_DISC с версии 9.4. Синтаксис совпадает со стандартом SQL. Пример:
    SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY salary)
    FROM employees;
  • MySQL: Не имеет встроенных функций для вычисления процентиля. Для расчета медианы часто используют комбинацию агрегатных функций и подзапросов. Пример вычисления медианы:
    SELECT AVG(dd.salary) as median_val
    FROM (
      SELECT salary, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
      FROM employees, (SELECT @rownum:=0) r
      ORDER BY salary
    ) as dd
    WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );
  • SQLite: Также не имеет прямой функции. Используют пользовательские агрегатные функции или вычисления на стороне приложения.
  • Sybase ASE: Не поддерживает PERCENTILE_CONT. Для вычисления процентилей применяют сложные запросы с ранжированием.

MS SQL PERCENTILE_CONT function comments

En
PERCENTILE CONT Calculates a percentile based on a continuous distribution of the column value