WIDTH_BUCKET in SQL: Equal-Width Histogram Buckets and Distributions

WIDTH_BUCKET — это функция PostgreSQL, которая отвечает ровно на один вопрос: в какую корзину гистограммы попадёт число, если разрезать заданный диапазон на равные по ширине части? Вы передаёте ей значение, нижнюю и верхнюю границы и количество корзин, а на выходе получаете номер интервала. Это самый прямой способ построить гистограмму прямо в SQL: когда непрерывную величину — сумму заказа, зарплату, баллы теста — надо разложить по одинаковым корзинам и увидеть форму распределения целиком, без выгрузки данных в Python или BI-инструмент.

Среднее значение здесь не помогает: оно прячет и хвосты, и горбы, и провалы. WIDTH_BUCKET же показывает, сколько строк попало в каждый отрезок оси значений, поэтому распределение видно сразу. Ключевая особенность — равная ширина корзин: все интервалы одинаковы по размеру, а значит, форму распределения можно честно сравнивать от отчёта к отчёту, если границы зафиксированы как бизнес-пороги, а не подогнаны под текущую выборку.

Сигнатура и логика

Канонический вызов в PostgreSQL берёт четыре аргумента:

SELECT WIDTH_BUCKET(score, 0, 100, 10) AS bucket;  -- bucket 1..10

Аргументы идут строго в этом порядке: само значение, нижняя граница диапазона, верхняя граница и число корзин. Читается так: возьми диапазон от 0 до 100, разрежь его на 10 равных кусков шириной 10 каждый и верни номер интервала для score. Значение из 0..10 попадёт в корзину 1, из 10..20 — в корзину 2, и далее по списку до корзины 10. Нижняя граница каждого интервала включается, верхняя — нет; именно эта несимметричность и есть источник большинства недоразумений, но к ней мы ещё вернёмся. Ширина одной корзины здесь равна (100 - 0) / 10, то есть ровно 10 единицам, и она постоянна для всех интервалов — в этом и состоит «равная ширина», давшая функции имя.

Главная фишка — две служебные корзины по краям:

• если значение меньше нижней границы, вернётся 0 (underflow);
• если значение больше или равно верхней границе, вернётся n + 1 (overflow).

Благодаря им строки не теряются молча: всё, что вылезло за рамки, аккуратно складывается в крайние ящики, и в отчёте сразу видно, сколько данных не уместилось в выбранный диапазон.

Распределение через GROUP BY

Самый ходовой сценарий — гистограмма сумм заказов. Раскладываем amount от 0 до 1000 на 10 корзин и считаем наполнение каждой:

SELECT
  WIDTH_BUCKET(amount, 0, 1000, 10) AS bucket,
  COUNT(*) AS orders,
  ROUND(MIN(amount), 2) AS lo,
  ROUND(MAX(amount), 2) AS hi
FROM orders
WHERE status = 'paid'
GROUP BY bucket
ORDER BY bucket;

Здесь GROUP BY bucket группирует строки по номеру корзины, а COUNT(*) даёт наполнение каждого интервала — это и есть готовая гистограмма. Корзина 0 соберёт возвраты и нулевые суммы, корзина 11 — крупные заказы свыше 1000, так что выбросы видно отдельной строкой, а не размазанными по распределению. Сам по себе номер корзины ничего не скажет читателю отчёта, поэтому подпишите границы прямо в запросе:

SELECT
  bucket,
  (bucket - 1) * 100 AS range_lo,
  bucket * 100       AS range_hi,
  COUNT(*)           AS orders
FROM (
  SELECT WIDTH_BUCKET(amount, 0, 1000, 10) AS bucket
  FROM orders
  WHERE status = 'paid'
) t
GROUP BY bucket
ORDER BY bucket;

Тот же приём отлично ложится на зарплаты: разбейте employees.salary на шесть вилок от 30000 до 150000 — и сразу видно, в каких диапазонах сидит основная масса сотрудников и где зарплатная сетка проседает. Поскольку WIDTH_BUCKET возвращает обычное целое, его можно положить в GROUP BY рядом с любой другой колонкой, например dept, и получить распределение по зарплатным полосам в разрезе отделов.

SELECT
  WIDTH_BUCKET(salary, 30000, 150000, 6) AS band,
  dept,
  COUNT(*) AS headcount
FROM employees
GROUP BY band, dept
ORDER BY band, dept;

WIDTH_BUCKET против NTILE

Эти две функции легко спутать, но режут они данные вдоль разных осей:

• WIDTH_BUCKET нарезает ось значений на равные по ширине отрезки. Корзины могут оказаться пустыми или переполненными — и это нормально, ведь форма распределения как раз и есть то, что мы хотим разглядеть.
• NTILE(n) нарезает набор строк на n групп примерно равного размера (квантили). Ширина интервалов гуляет, зато в каждой группе почти поровну строк.

Бери WIDTH_BUCKET, когда важны фиксированные пороги («заказы 0–100, 100–200…»). Бери NTILE, когда нужны процентили («верхние 10% клиентов по выручке»):

SELECT
  user_id,
  SUM(amount) AS lifetime,
  NTILE(4) OVER (ORDER BY SUM(amount)) AS quartile
FROM orders
GROUP BY user_id;

Подводный камень: границы и NULL

Несколько мест, где легко обжечься:

• Верхняя граница исключена. Ровно WIDTH_BUCKET(100, 0, 100, 10) вернёт 11, а не 10, — и максимум неожиданно улетит в корзину переполнения. Чтобы затащить его в последнюю настоящую корзину, либо чуть расширьте верхний предел, либо подрежьте вход через LEAST(amount, 999.99).
• NULL на входе даёт NULL на выходе, так что такие строки тихо выпадут из распределения. Отфильтруйте их явно или сверните в нулевую корзину через COALESCE.
• Границы можно передать наоборот (верхняя меньше нижней) — тогда нумерация корзин пойдёт в обратную сторону. Это почти никогда не то, что вам нужно, так что лишний раз сверьте порядок аргументов.

Отличия в других СУБД

• PostgreSQL и Oracle поддерживают четырёхаргументную форму WIDTH_BUCKET из коробки.
• MySQL функции WIDTH_BUCKET не знает. Эмулируйте её арифметикой: FLOOR((amount - 0) / 100) + 1, не забыв вручную обработать underflow и overflow.
• ClickHouse для той же задачи предлагает roundDown(amount, [0, 100, 200, ...]) и обычный floor(amount / 100).

Главный вывод прост: задавайте min, max и число корзин как осознанные бизнес-пороги, а не подгоняйте их под текущую выборку. Иначе стоит данным чуть сместиться — и два соседних отчёта перестанут сравниваться между собой, а гистограмма из инструмента анализа превратится в повод для споров.