SQL-рецепт: сегментация данных

Это заметка из серии «SQL-рецепты», в которой я описываю решения распространенных задач анализа данных в SQL.

Предположим, мы хотим разбить данные на несколько сегментов, ориентируясь на значения в одном или нескольких столбцах. Например, распределить клиентов или продукты по категориям для маркетинга.

Решение — использовать функцию ntile() поверх SQL-окна, упорядоченного по целевым столбцам.

Пример

Разобьем сотрудников из таблицы employees на три группы в зависимости от размера зарплаты:

• высокооплачиваемые,
• средние,
• низкооплачиваемые.

select
  ntile(3) over w as tile,
  name, salary
from employees
window w as (order by salary desc)
order by salary desc, id;

ntile(n) разбивает все записи на n групп и возвращает номер группы для каждой записи. Если общее количество записей (10 в нашем случае) не делится на размер группы (3), то первые группы будут крупнее последних.

Альтернативы

ntile() всегда старается разбить данные так, чтобы группы были одинакового размера. Поэтому записи с одинаковым значением з/п вполне могут попасть в разные (соседние) группы:

select
  ntile(2) over w as tile,
  name, salary
from employees
window w as (order by salary desc, id)
order by salary desc, tile;

Чтобы избежать этого, придется использовать более сложную формулу:

1 + ((rank() over w) - 1) * N / count(*) over () as tile

Для n = 2:

select
  1 + ((rank() over w) - 1) * 2 / count(*) over () as tile,
  name, salary
from employees
window w as (order by salary desc)
order by salary desc, id;

Совместимость

Все основные СУБД поддерживают оконные функцию ntile(). Некоторые из них, такие как MS SQL и Oracle, не поддерживают инструкцию window. В этих случаях можно перенести определение окна в основную часть запроса:

select
  ntile(3) over (
    order by salary desc
  ) as tile,
  name, salary
from employees
order by salary desc, id;

Можно переписать запрос вообще без оконных функций:

select
  ceil(
    (select count(*) from employees as e2 where e2.salary > e1.salary) * 3 /
    (select count(*) from employees)
  ) + 1 as tile,
  name, salary
from employees as e1
order by salary desc, id;

Хотите узнать больше об оконных функциях? Записывайтесь на курс Оконные функции SQL.