Функция SQL Server NTILE

В этом руководстве вы узнаете, как использовать функцию SQL Server NTILE() для распределения строк упорядоченного раздела по указанному количеству сегментов.

Введение в функцию SQL Server NTILE()

SQL Server NTILE() — это оконная функция, которая распределяет строки упорядоченного раздела по указанному числу приблизительно равных групп или сегментов. Она назначает каждой группе номер сегмента, начиная с единицы. Для каждой строки в группе функция NTILE() назначает номер сегмента, представляющий группу, к которой принадлежит строка.

Синтаксис функции NTILE() следующий:

NTILE(buckets) OVER(
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Давайте рассмотрим синтаксис подробно:

ведра

Количество сегментов, на которые разделены строки. Сегменты могут быть выражением или подзапросом, результатом которого является положительное целое число. Это не может быть оконная функция.

РАЗДЕЛЕНИЕ ПО пункту

Предложение PARTITION BY распределяет строки результирующего набора по разделам, к которым применяется функция NTILE().

Предложение ORDER BY

Предложение ORDER BY определяет логический порядок строк в каждом разделе, к которому применяется NTILE().

Если количество строк не делится на количество ячеек, функция NTILE() возвращает группы двух размеров с разницей в один. Большие группы всегда идут перед меньшими в порядке, указанном ORDER BY в предложении OVER().

С другой стороны, если общее количество строк делится на количество блоков, функция равномерно распределяет строки между блоками.

Иллюстрация функции SQL Server NTILE()

Следующий оператор создает новую таблицу с именем ntile_demo, в которой хранится 10 целых чисел:

CREATE TABLE sales.ntile_demo(
 v INT NOT NULL
);
INSERT INTO sales.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT * FROM sales.ntile_demo;

Этот оператор использует функцию NTILE() для разделения десяти строк на три группы:

SELECT 
 v, 
 NTILE(3) OVER(
 ORDER BY v
 ) buckets
FROM 
 sales.ntile_demo;

Вот что получилось:

Как ясно видно из выходных данных, первая группа состоит из четырех строк, а две другие группы — из трех строк.

Следующий оператор использует функцию NTILE() для распределения строк по пяти группам:

SELECT 
 v, 
 NTILE(5) OVER(
 ORDER BY v
 ) buckets
FROM 
 sales.ntile_demo;

Вывод следующий:

Как видите, на выходе получается пять групп с одинаковым количеством строк в каждой.

Примеры функций SQL Server NTILE()

Давайте создадим представление для демонстрации функции NTILE().

Следующий оператор создает представление, которое возвращает чистые продажи за 2017 год по месяцам.

CREATE VIEW sales.vw_netsales_2017 AS
SELECT 
 c.category_name,
 DATENAME(month, o.shipped_date) month, 
 CONVERT(DEC(10, 0), SUM(i.list_price * quantity *(1 - discount))) net_sales
FROM 
 sales.orders o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN production.products p on p.product_id = i.product_id
INNER JOIN production.categories c on c.category_id = p.category_id
WHERE 
 YEAR(shipped_date) = 2017
GROUP BY
 c.category_name,
 DATENAME(month, o.shipped_date);

SELECT 
    category_name, 
    month,
    net_sales 
FROM 
   sales.vw_netsales_2017 
ORDER BY 
   category_name, 
   net_sales;

Вот результат:

Пример использования функции SQL Server NTILE() для набора результатов запроса

В следующем примере функция NTILE() используется для распределения месяцев по 4 сегментам на основе чистых продаж:

WITH cte_by_month AS(
 SELECT
 month, 
 SUM(net_sales) net_sales
 FROM 
 sales.vw_netsales_2017
 GROUP BY 
 month
)
SELECT
 month, 
 FORMAT(net_sales,'C','en-US') net_sales,
 NTILE(4) OVER(
 ORDER BY net_sales DESC
 ) net_sales_group
FROM 
 cte_by_month;

Вот что получилось:

Пример использования функции SQL Server NTILE() для разделов

В этом примере функция NTILE() используется для разделения чистых продаж по месяцам на 4 группы для каждой категории продуктов:

SELECT
 category_name,
 month, 
 FORMAT(net_sales,'C','en-US') net_sales,
 NTILE(4) OVER(
 PARTITION BY category_name
 ORDER BY net_sales DESC
 ) net_sales_group
FROM 
 sales.vw_netsales_2017;

На следующем рисунке показан частичный вывод:

В этом руководстве вы узнали, как использовать функцию SQL Server NTILE() для распределения строк упорядоченного раздела по указанному количеству сегментов.