SQL Interview Prep · Урок

NTILE для разбиения на группы

Разделяйте строки на квартили и интервалы процентилей.

«NTILE для разбиения на группы» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Когда нужны группы одинакового размера

Интервьюеры спрашивают: «Разделите клиентов на четыре равные по численности группы по расходам» или «К какому децилю относится каждая строка?» Для этого используется NTILE.

NTILE(n) распределяет упорядоченные строки по n группам настолько равномерно, насколько возможно, и присваивает каждой строке номер её группы — от 1 до n. В этом уроке рассматривается принцип разделения, обработка неравного количества строк и отличие этой функции от ранжирования.

Базовый синтаксис NTILE

Применение NTILE(4) к строкам, упорядоченным по значению, создаёт квартили. Как и любой оконной функции, ей требуется предложение OVER; находящийся внутри него ORDER BY определяет, какие строки попадут в группы с низкими, а какие — в группы с высокими значениями.

Сортировка по возрастанию помещает наименьшие значения в группу 1, а сортировка по убыванию меняет результат на противоположный.

SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customers;

Как NTILE распределяет строки

При наличии 12 строк и NTILE(4) каждая группа получает ровно 12 / 4 = 3 строки. Группа 1 содержит 3 наименьших значения, а группа 4 — 3 наибольших.

Главная идея заключается в том, что NTILE разделяет данные по количеству строк, а не по диапазонам значений. Две группы могут охватывать совершенно разные диапазоны значений, если в каждой из них находится одинаковое число строк.

Неравномерное деление

Что делать, если число строк не делится без остатка? При наличии 10 строк и NTILE(4) получаем 10 / 4 = 2 и остаток 2. NTILE отдаёт дополнительные строки первым группам.

Группа 1: 3 строки
Группа 2: 3 строки
Группа 3: 2 строки
Группа 4: 2 строки

Таким образом, размеры групп отличаются не более чем на одну строку, а самые большие группы находятся в начале. Это точное правило — любимая деталь на собеседованиях.

NTILE игнорирует совпадения значений

Важная ловушка: NTILE не сохраняет равные значения в одной группе. Строки распределяются по позициям, поэтому две строки с одинаковым total_spend могут попасть в разные группы только из-за порядка строк.

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

Децили и процентили

Количество групп — это просто передаваемое число. NTILE(10) создаёт децили, а NTILE(100) — интервалы процентилей. Так аналитики разделяют пользователей на уровни эффективности или диапазоны риска.

Результатом является номер группы, поэтому значение в группе 9 функции NTILE(10) находится во втором по величине дециле.

SELECT
  user_id,
  score,
  NTILE(10) OVER (ORDER BY score DESC) AS decile
FROM leaderboard;

Группы с разделением по категориям

Добавьте PARTITION BY, чтобы независимо формировать группы внутри каждой категории, например квартили расходов для каждого региона. В каждом регионе нумерация начинается с группы 1.

Так можно ответить на вопросы вроде «клиенты из верхнего квартиля в каждом регионе», когда даже регион с небольшими общими расходами имеет собственную группу 4.

SELECT
  region,
  customer_id,
  total_spend,
  NTILE(4) OVER (
    PARTITION BY region
    ORDER BY total_spend DESC
  ) AS regional_quartile
FROM customers;

Фильтрация по определённому уровню

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

Этот шаблон — «покажите мне клиентов из верхнего квартиля по расходам» — чаще всего используется с NTILE в реальных ответах.

WITH q AS (
  SELECT customer_id, total_spend,
         NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
  FROM customers
)
SELECT customer_id, total_spend
FROM q
WHERE quartile = 1;

NTILE и процентили на основе значений

NTILE формирует группы с одинаковым количеством строк. Если вместо этого Вам нужен настоящий статистический процентиль — значение на уровне 90-го процентиля, — используйте PERCENTILE_CONT или PERCENTILE_DISC.

NTILE(100): процентильный интервал, в который попадает строка, по её позиции в ранжировании.
PERCENTILE_CONT(0.9): фактическое значение на уровне 90-го процентиля.

Знание этого различия отличает уверенный ответ от догадки.

Когда важен ORDER BY

Для NTILE требуется ORDER BY внутри OVER; без заданного порядка группы не имеют смысла. Направление сортировки определяет, какой конец получает группу 1.

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

Присвоение группам названий

Исходные номера групп (1, 2, 3, 4) редко являются итоговым результатом. Обычно аналитики преобразуют их в бизнес-метки вроде «Низкий», «Средний», «Высокий» и «Наивысший» с помощью выражения CASE над результатом NTILE.

Вычислите NTILE в CTE, а затем преобразуйте число во внешнем запросе. Так логика окна остаётся чистой, а результат готов к представлению.

WITH q AS (
  SELECT customer_id, total_spend,
         NTILE(4) OVER (ORDER BY total_spend) AS bucket
  FROM customers
)
SELECT customer_id, total_spend,
  CASE bucket
    WHEN 1 THEN 'Low'
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'High'
    WHEN 4 THEN 'Top'
  END AS spend_tier
FROM q;

Быстрая проверка

Проверьте правило неравномерного распределения.

Итоги

NTILE распределяет упорядоченные строки по группам с одинаковым количеством строк:

NTILE(n) присваивает строкам номера от 1 до n; NTILE(4) создаёт квартили, а NTILE(10) — децили.
Функция разделяет строки по их количеству, а не по диапазонам значений, и отдаёт дополнительные строки первым группам.
Она не сохраняет связанные значения вместе и не может использоваться непосредственно в WHERE.
Чтобы получить настоящее значение процентиля, используйте PERCENTILE_CONT.

Далее: извлечение граничных значений с помощью FIRST_VALUE, LAST_VALUE и границ оконной рамки.

Часто задаваемые вопросы

Урок «NTILE для разбиения на группы» бесплатный?

Да — полный текст урока «NTILE для разбиения на группы» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «NTILE для разбиения на группы»?

Разделяйте строки на квартили и интервалы процентилей. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «NTILE для разбиения на группы»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к SQL Interview Prep