Как только вы поняли, как писать программу, заставьте сделать это кого-нибудь другого. (Алан.Дж.Перлис)

Функция NTILE в MySQL

1 мин для чтения

Добавить в избранное

Главное меню » Базы данных » База данных MySQL » Функция NTILE в MySQL

(3 оценок, среднее: 5,00 из 5)

Загрузка...

23.10.2019

Описание : в этой статьи вы узнаете, как использовать функцию NTILE() в MySQL для разделения строк на определенное количество групп.

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

Функция NTILE() делит строки в отсортированном разделе на определенное количество групп. Каждой группе присваивается номер сегмента, начинающийся с единицы. Для каждой строки функция NTILE() возвращает номер сегмента, представляющий группу, к которой принадлежит строка.

Ниже показан синтаксис функции NTILE():

NTILE(n) OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

В этом синтаксисе:

n — это буквальное целое положительное число. Номер ковша находится в диапазоне от 1 до n.
PARTITION BY делит набор результатов, возвращаемые из FROMпункта на разделы , к которым применяется функция NTILE().
Предложение ORDER BY указывает порядок, в котором значения NTILE() присваиваются строкам в разделе.

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

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

Посмотрите следующую таблицу, в которой хранятся девять целых чисел от одного до девяти:

CREATE TABLE t (
val INT NOT NULL
);

INSERT INTOt(val)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT * FROM t;

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

Смотрите следующую демонстрацию:

SELECT
val,
NTILE (4) OVER (
ORDER BY val
) bucket_no
FROM
t;

Вот вывод:

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

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

SELECT
val,
NTILE (3) OVER (
ORDER BY val
) bucket_no
FROM
t;

В результирующем наборе теперь есть три группы с одинаковым количеством строк.

Пример функции NTILE() в MySQL

Мы будем использовать таблицы orders, orderDetails и products из базы данных для демонстрации.

Смотрите следующий запрос:

WITH productline_sales AS (
SELECT productline,
year(orderDate) order_year,
ROUND(SUM(quantityOrdered * priceEach),0) order_value
FROM orders
INNER JOIN orderdetails USING (orderNumber)
INNER JOIN products USING (productCode)
GROUP BY productline, order_year
)
SELECT
productline,
order_year,
order_value,
NTILE(3) OVER (
PARTITION BY order_year
ORDER BY order_value DESC
) product_line_group
FROM
productline_sales;

В этом примере:

Во-первых, в общем табличном выражении productline_sales мы получаем итоговую стоимость заказа каждой линейки продуктов по годам.
Затем мы используем функцию NTILE(), чтобы разделить продажи по линейке продуктов в каждом году на три группы.

Вывод: