CUME DIST: примеры (SQL)

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

Пример 1: базовое использование для отдела продаж.

SELECT 
    EmployeeID,
    SalesAmount,
    CUME_DIST() OVER (ORDER BY SalesAmount) AS CumeDist
FROM SalesData;

EmployeeID  SalesAmount  CumeDist
----------- ------------ ----------------------
3           100.00       0.25
1           150.00       0.50
4           150.00       0.50
2           300.00       1.00

Пример 2: использование с секционированием (PARTITION BY).

SELECT 
    Department,
    EmployeeID,
    Salary,
    CUME_DIST() OVER (PARTITION BY Department ORDER BY Salary) AS DeptCumeDist
FROM Employees;

Department   EmployeeID  Salary   DeptCumeDist
------------ ----------- -------- ----------------------
IT           5           3000     0.333333333333333
IT           7           4000     0.666666666666667
IT           9           5000     1.000000000000000
Sales        2           2500     0.500000000000000
Sales        4           2500     0.500000000000000
Sales        6           3000     1.000000000000000

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

PERCENT_RANK: вычисляет относительный ранг строки в группе. Формула: (RANK - 1) / (общее количество строк - 1). Возвращает значения от 0 до 1, где 0 — первая строка. Отличие от CUME_DIST: PERCENT_RANK показывает позицию относительно других строк, а CUME_DIST — долю строк с меньшими или равными значениями.

NTILE: делит набор строк на указанное количество групп. Помогает в разбиении данных на квантили, но не вычисляет точное распределение.

Предпочтения: CUME_DIST удобна для анализа распределения значений, PERCENT_RANK — для сравнения относительных позиций, NTILE — для создания сегментов (кварталей, децилей).

Типичные ошибки при работе с CUME_DIST

Ошибка 1: пропуск ORDER BY в предложении OVER. Функция требует указания порядка.

-- Неправильно
SELECT CUME_DIST() OVER () FROM SalesData;
-- Правильно
SELECT CUME_DIST() OVER (ORDER BY SalesAmount) FROM SalesData;

Ошибка 2: неправильная интерпретация результатов для одинаковых значений. CUME_DIST для дубликатов возвращает одно значение, что может искажать ожидаемое распределение.

-- Если три строки имеют одинаковое значение, они получат одинаковый CUME_DIST
SELECT Value, CUME_DIST() OVER (ORDER BY Value) FROM TestTable;

Value  CUME_DIST
------ ----------------------
10     1.0  -- все три строки со значением 10 получат 1.0

Ошибка 3: использование NULL-значений. NULL включаются в расчет и считаются наименьшими значениями. Это может привести к неочевидным результатам.

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

CUME_DIST была введена в MS SQL Server с версии 2012 вместе с другими оконными функциями. С тех пор существенных изменений в синтаксисе или поведении не было. Основные обновления связаны с оптимизацией производительности в последних версиях SQL Server (2016, 2017, 2019, 2022).

В SQL Server 2012 добавлена возможность использования CUME DIST с секционированием (PARTITION BY). Все последующие версии сохраняют обратную совместимость.

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

Пример 1: анализ зарплатной вилки в компании.

WITH SalaryAnalysis AS (
    SELECT 
        EmployeeName,
        Salary,
        CUME_DIST() OVER (ORDER BY Salary) * 100 AS SalaryPercentile
    FROM Employees
)
SELECT 
    EmployeeName,
    Salary,
    SalaryPercentile,
    CASE 
        WHEN SalaryPercentile <= 25 THEN 'Нижний квартиль'
        WHEN SalaryPercentile <= 50 THEN 'Средний квартиль'
        WHEN SalaryPercentile <= 75 THEN 'Выше среднего'
        ELSE 'Верхний квартиль'
    END AS SalaryGroup
FROM SalaryAnalysis
ORDER BY SalaryPercentile;

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

SELECT 
    ManagerID,
    Quarter,
    Sales,
    CUME_DIST() OVER (PARTITION BY Quarter ORDER BY Sales) AS QuarterCumeDist,
    CUME_DIST() OVER (ORDER BY Sales) AS OverallCumeDist
FROM QuarterlySales
ORDER BY Quarter, Sales;

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

DECLARE @Threshold DECIMAL(3,2) = 0.9;
WITH RankedProducts AS (
    SELECT 
        ProductID,
        Revenue,
        CUME_DIST() OVER (ORDER BY Revenue) AS RevenueDistribution
    FROM Products
)
SELECT 
    ProductID,
    Revenue,
    RevenueDistribution
FROM RankedProducts
WHERE RevenueDistribution >= @Threshold;
-- Выбирает продукты с доходом выше 90-го процентиля

Пример 4: анализ временных рядов.

SELECT 
    OrderDate,
    DailyRevenue,
    CUME_DIST() OVER (ORDER BY DailyRevenue) AS RevenueCumeDist,
    AVG(DailyRevenue) OVER () AS AvgRevenue
FROM DailyOrders
ORDER BY OrderDate;

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

PostgreSQL и Oracle: поддерживают CUME_DIST с идентичным синтаксисом и поведением.

MySQL: не имеет встроенной CUME_DIST до версии 8.0. В MySQL 8.0 функция доступна с аналогичной MS SQL семантикой.

-- Пример для MySQL 8.0
SELECT 
    name,
    score,
    CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM students;

SQLite: отсутствует CUME_DIST, но можно эмулировать с помощью подзапросов или агрегатных функций.

Sybase ASE: не поддерживает CUME_DIST, используются пользовательские вычисления.

Общий принцип: в системах без CUME_DIST применяют подзапросы с COUNT и оконными функциями для расчета распределения.