NTILE — SQL: Оконные функции

В этом уроке мы разберем оконную функцию NTILE(), которая позволяет разбивать окно на процентили. Процентиль - это значение, которое заданная случайная величина не превышает с фиксированной вероятностью, заданной в процентах.

Допустим, у нас есть данные о продажах машин. Они могут быть совершены в разное время в различных магазинах, машины могут относиться к разным категориям. Наша задача состоит в том, чтобы распределить сделки по разным категориям — дате покупки, сумме сделки и так далее, статистически их категоризировать. Мы это можем сделать как по сумме, так и по дате, а затем расположить их в интервалах. ДЛя этого воспользуемся процентилями.

Процентиль задается числом от 1 до 100. Это число как раз показывает на сколько категорий нам нужно разбить нашу конкретную сущность. Наиболее распространенный N-тиль — это процентиль, то есть само число 100. То есть мы категоризируем наши данные в окне на N-категорий и выводим номер категории, к которой относится каждая строка.

Помимо процентилей есть дециль – группировка данных по 10 определенным категориям.

Еще есть:

• Квантиль, который составляет 25 процентов — данные делятся на 4 части.
• Вентиль, который составляет 5 процентов — данные делятся на 20 частей.

Вернемся к задаче с машинами. Если мы рассматриваем дециль по сумме, то в первом дециле у нас будут самые недорогостоящие модели, в последнем дециле — самые дорогостоящие. В пятом или шестом дециле у нас будут автомобили средней ценовой категории. Также и с любым другим квантилем, процентилем и так далее.

Для чего это нужно? Во-первых, статистика – это самое основное, главное применение. Процентили важны в статистических вычислениях, потому что первые и последние процентили относятся к выбросам — то есть к экстремальным значениям, которые не входят в основную массу по нормальному распределению. Так же из статистики выбрасываются первые и последние децили, пентили и квантили.

Во-вторых, N-тили применяются в продуктовой аналитике продаж — они показывают, насколько широкий у нас ценовой диапазон. То есть мы можем продавать машины как за 100 тысяч, так и за десятки миллионов – это вполне нормальная история для автосалонов. В таких случаях нужно в каждом из этих процентилей какие-то отдельные расчеты производить — например, считать конверсию и делать выводы о том, какие машины покупают чаще всего.

Функция NTILE() позволяет определить, в какой группе относится текущая строка. Также мы можем указать на какой NTILE() требуется разделить — количество строк в каждой группе равно количеству строк в наборе, разделенному на количество указанных групп. Если группы не делятся нацело, то некоторые группы могут быть больше или меньше других на одну строку.

Представим, что мы хотим разделить 10 записей на квантили. Нам нужно разделить 10 на 4 часть – это по 2,5 записи на квантиль, что физически невозможно. В таких случаях функция NTILE() делает так, что первые две категории сохраняли по 3 значения, а последние две — по 2 значения.

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

SELECT
    s.id,
    (
        s.quantity * s.price
    ) AS amount,
    NTILE(4) OVER (
        ORDER BY
            s.quantity * s.price
    ) AS ntile_amount
FROM
    sales AS s;

View on DB Fiddle

В результате запроса мы получили 4 группы, в каждой из которых содержится примерно одинаковое количество записей. Первая группа содержит записи с наименьшей суммой продажи, а последняя — с наибольшей.

Выводы

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

Дополнительные материалы

1. Квантиль