NTILE: примеры (SQL)
NTILE(integer_expression OVER [PARTITION BY ] ORDER BY ): bigintФункция NTILE в MS SQL Server относится к категории оконных функций и распределяет строки упорядоченного результирующего набора на указанное количество групп (корзин). Каждой строке присваивается номер группы, начиная с единицы.
Основные сведения о функции NTILE
NTILE применяется для деления набора данных на примерно равные по количеству строк части. Это полезно для создания перцентилей, квартилей или других сегментов данных. Функция используется в аналитических запросах, отчетах и при подготовке данных для дальнейшего анализа.
Синтаксис функции: NTILE (number_of_groups) OVER ( [partition_by_clause] order_by_clause )
number_of_groups – целочисленное положительное выражение, определяющее количество групп, на которое нужно разделить результат. Если число строк не делится нацело на количество групп, то первые группы будут содержать на одну строку больше, чем последующие.
OVER ( [partition_by_clause] order_by_clause ) – определяет порядок и секционирование данных. partition_by_clause делит результирующий набор на секции, к которым независимо применяется функция. order_by_clause определяет порядок сортировки строк внутри каждой секции, что влияет на распределение по группам.
Возвращаемое значение: целое число от 1 до number_of_groups, указывающее номер группы, к которой принадлежит строка.
Примеры использования NTILE
Простое деление на группы:
SELECT EmployeeID, Salary,
NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile
FROM Employees;EmployeeID Salary Quartile ---------- ------ -------- 101 95000 1 102 87000 1 103 82000 2 104 78000 2 105 72000 3 106 65000 3 107 58000 4 108 52000 4
Использование с секционированием:
SELECT DepartmentID, EmployeeID, Salary,
NTILE(3) OVER (PARTITION BY DepartmentID ORDER BY Salary) AS GroupNum
FROM Employees
ORDER BY DepartmentID, Salary;DepartmentID EmployeeID Salary GroupNum ------------ ---------- ------ -------- 1 107 52000 1 1 108 58000 1 1 106 65000 2 1 105 72000 2 1 104 78000 3 1 103 82000 3 2 102 87000 1 2 101 95000 2
Похожие функции в MS SQL
ROW_NUMBER() – последовательная нумерация строк в секции. В отличие от NTILE, не ограничивает количество уникальных значений и всегда возвращает уникальные номера.
RANK() и DENSE_RANK() – ранжирование строк с учетом одинаковых значений. Полезны для выделения позиций в рейтингах, но не делят данные на равные группы по количеству.
PERCENT_RANK() и CUME_DIST() – вычисляют относительный ранг и кумулятивное распределение. Возвращают дробные значения от 0 до 1, что удобно для процентного анализа.
NTILE предпочтительнее, когда требуется именно количественное разделение на равные группы. Для нумерации или ранжирования используются другие функции.
Аналоги в других СУБД и языках
PostgreSQL, Oracle, MySQL (с версии 8.0) – поддерживают функцию NTILE с идентичным синтаксисом.
-- PostgreSQL, Oracle, MySQL
SELECT id, value,
NTILE(4) OVER (ORDER BY value) AS quartile
FROM sample_table;SQLite – не имеет встроенной функции NTILE. Реализация требует использования дополнительных оконных функций и вычислений.
-- SQLite (эмуляция NTILE(3))
SELECT id, value,
1 + ((ROW_NUMBER() OVER (ORDER BY value) - 1) * 3 / COUNT(*) OVER ()) AS tile
FROM sample_table;Sybase ASE – поддерживает NTILE, но может требовать указания дополнительных параметров в оконной спецификации.
В языках программирования (Python, R) аналогичные операции выполняются библиотеками анализа данных (pandas, dplyr) с помощью функций qcut, ntile.
Типичные ошибки при работе с NTILE
Использование неположительного аргумента или NULL приводит к ошибке.
-- Ошибка: Аргумент number_of_groups должен быть положительным целым числом.
SELECT NTILE(0) OVER (ORDER BY id) FROM table;
SELECT NTILE(NULL) OVER (ORDER BY id) FROM table;Отсутствие ORDER BY в оконной спецификации вызывает ошибку, так как функция требует упорядоченного набора.
-- Ошибка: Функция оконного агрегата NTILE требует наличия предложения ORDER BY.
SELECT NTILE(4) OVER () FROM table;Некорректное понимание распределения строк при неравном делении. Первые группы всегда получают дополнительную строку.
Изменения в последних версиях
В MS SQL Server 2012 и более поздних версиях функция NTILE была существенно оптимизирована в составе расширенных оконных возможностей. Изменений в синтаксисе или поведении функции с момента её появления не зафиксировано. Улучшения касаются в основном производительности и параллельного выполнения запросов.
Расширенные примеры применения NTILE
Создание динамического количества групп в зависимости от данных:
DECLARE @GroupCount INT = (SELECT COUNT(*)/10 FROM Sales WHERE Year = 2023);
SELECT SaleID, Amount,
NTILE(@GroupCount) OVER (ORDER BY Amount DESC) AS DecileGroup
FROM Sales
WHERE Year = 2023;Использование в составе CTE для сложного анализа:
WITH RankedProducts AS (
SELECT ProductID, CategoryID, Sales,
NTILE(100) OVER (PARTITION BY CategoryID ORDER BY Sales DESC) AS PercentileRank
FROM ProductStats
)
SELECT CategoryID,
COUNT(CASE WHEN PercentileRank <= 20 THEN 1 END) AS Top20PercentCount
FROM RankedProducts
GROUP BY CategoryID;Комбинирование с другими оконными функциями для детализации:
SELECT OrderID, CustomerID, OrderAmount,
NTILE(5) OVER (ORDER BY OrderAmount) AS AmountQuintile,
LAG(OrderAmount) OVER (ORDER BY OrderAmount) AS PrevAmount
FROM Orders;OrderID CustomerID OrderAmount AmountQuintile PrevAmount ------- ---------- ----------- -------------- ---------- 1001 23 150.00 1 NULL 1005 41 200.00 1 150.00 1003 56 250.00 2 200.00 1008 19 300.00 2 250.00 1002 34 350.00 3 300.00