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

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

Пример 1: Медиана зарплат по отделам

SELECT DepartmentID, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER (PARTITION BY DepartmentID) AS MedianSalary FROM Employees;

DepartmentID | MedianSalary 1 | 45000.00 1 | 45000.00 2 | 52000.00

Пример 2: 90-й процентиль с сортировкой по убыванию

SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY Score DESC) AS TopPercentile FROM TestResults;

TopPercentile 92

Пример 3: Использование без секционирования

SELECT DISTINCT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY Price) OVER () AS Q1 FROM Products;

Q1 15.99

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

• PERCENTILE_CONT - вычисляет непрерывный процентиль, может возвращать интерполированное значение между точками данных. Предпочтительнее для непрерывных распределений.
• MEDIAN - доступна с SQL Server 2022, специализированная функция для медианы. Упрощает синтаксис для распространенного случая процентиля 0.5.
• NTILE - распределяет строки на указанное количество групп. Полезен для разбиения на квантили, но не вычисляет конкретные процентильные значения.

PERCENTILE_DISC лучше использовать для дискретных данных (например, целые оценки), PERCENTILE_CONT - для непрерывных показателей (температура, время).

Распространенные ошибки

Ошибка 1: Процентиль вне диапазона

SELECT PERCENTILE_DISC(1.5) WITHIN GROUP (ORDER BY Value) FROM table;

Сообщение об ошибке: Значение процента должно находиться в диапазоне от 0.0 до 1.0.

Ошибка 2: Несовместимые типы данных

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY TextColumn) FROM table;

Сообщение об ошибке: Аргумент функции ORDER BY должен иметь тип данных, поддерживающий операцию сортировки.

Ошибка 3: Отсутствие ORDER BY

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP () FROM table;

Сообщение: Неправильный синтаксис около ')'

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

SQL Server 2022 представил функцию MEDIAN, которая является частным случаем PERCENTILE_DISC(0.5). Синтаксис стал проще для вычисления медианы:

-- До SQL Server 2022 SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER () AS Median -- SQL Server 2022 и выше SELECT MEDIAN(Salary) OVER () AS Median

Производительность и функциональность PERCENTILE DISC остались без изменений.

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

Пример 1: Динамическое определение процентиля

DECLARE @percentile FLOAT = 0.75; SELECT ProductID, PERCENTILE_DISC(@percentile) WITHIN GROUP (ORDER BY Discount) OVER (PARTITION BY CategoryID) AS DiscountThreshold FROM Products;

Пример 2: Несколько процентилей в одном запросе

SELECT EmployeeID, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY Sales) OVER () AS Q1, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Sales) OVER () AS Median, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY Sales) OVER () AS Q3 FROM SalesData;

EmployeeID | Q1 | Median | Q3 101 | 150 | 300 | 450 102 | 150 | 300 | 450

Пример 3: Процентиль с условием в оконной функции

SELECT OrderID, PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY Amount) OVER (PARTITION BY YEAR(OrderDate)) AS Percentile90 FROM Orders WHERE Status = 'Completed';

Пример 4: Использование с пользовательской сортировкой

SELECT Department, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY CASE WHEN Score < 0 THEN 0 ELSE Score END) OVER () AS AdjustedMedian FROM Evaluations;

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

PostgreSQL и Oracle

-- PostgreSQL, Oracle PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY column) OVER ()

Синтаксис идентичен SQL Server. В PostgreSQL требуется поддержка оконных функций.

MySQL

-- Альтернатива через переменные SET @p = 0.5; SELECT MAX(column) FROM ( SELECT column, @rownum:=@rownum+1 AS row_number, @total_rows:=@rownum FROM table, (SELECT @rownum:=0) r ORDER BY column ) AS temp WHERE row_number >= @p * @total_rows;

SQLite

-- Использование агрегатной функции NTH_VALUE SELECT NTH_VALUE(column, CEIL(0.5 * COUNT(*))) OVER (ORDER BY column) FROM table;

В SQLite нет встроенной функции, используют комбинации стандартных функций.