WIDTH_BUCKET в PostgreSQL: как построить гистограмму прямо в SQL

Иногда одного среднего значения мало.

Допустим, вы смотрите на заказы в интернет-магазине. Средний чек получился 870. Звучит полезно, но за этой цифрой может прятаться что угодно:

• много заказов около 800;
• половина заказов по 200, половина по 1500;
• один огромный заказ вытянул среднее вверх;
• есть провал в середине диапазона;
• большинство заказов дешёвые, а дорогих совсем мало.

Среднее значение сжимает картину до одной цифры. А нам часто нужно увидеть форму распределения: где данных много, где мало, где хвосты, где выбросы.

Для этого строят гистограммы.

В PostgreSQL для такой задачи есть функция WIDTH_BUCKET. Она отвечает на простой вопрос:

в какую корзину попадёт число, если разрезать диапазон значений на равные части?

Например, можно взять суммы заказов от 0 до 1000, разрезать этот диапазон на 10 равных корзин и посчитать, сколько заказов попало в каждую.

Получится примерно такая логика:

Корзина	Диапазон суммы
1	от 0 до 100
2	от 100 до 200
3	от 200 до 300
...	...
10	от 900 до 1000

И всё это можно сделать прямо в SQL, без Python, Excel или BI-инструмента.

Что делает WIDTH_BUCKET

Функция WIDTH_BUCKET принимает значение, нижнюю границу, верхнюю границу и количество корзин.

Базовый пример:

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

Читается так:

возьми значение score, посмотри на диапазон от 0 до 100, разрежь его на 10 равных частей и верни номер корзины.

Если диапазон от 0 до 100 разделить на 10 корзин, ширина каждой корзины будет равна 10.

То есть:

Значение	Корзина
0	1
5	1
10	2
25	3
99	10
100	11

Последняя строка выглядит неожиданно: почему 100 попало в корзину 11, если корзин всего 10?

Потому что у WIDTH_BUCKET нижняя граница интервала включается, а верхняя не включается.

Проще говоря:

[0, 10)   -> bucket 1
[10, 20)  -> bucket 2
[20, 30)  -> bucket 3
...
[90, 100) -> bucket 10

Значение 100 уже не входит в диапазон [90, 100), поэтому попадает в специальную корзину переполнения.

Служебные корзины 0 и n + 1

У WIDTH_BUCKET есть важная особенность: она не теряет значения за пределами диапазона.

Если значение меньше нижней границы, функция вернёт 0.

Если значение больше или равно верхней границе, функция вернёт n + 1, где n — количество обычных корзин.

Например:

SELECT
    WIDTH_BUCKET(-5, 0, 100, 10) AS below_range,
    WIDTH_BUCKET(45, 0, 100, 10) AS inside_range,
    WIDTH_BUCKET(100, 0, 100, 10) AS above_range;

Результат будет таким:

below_range	inside_range	above_range
0	5	11

Если корзин 10, то:

• 0 — значение ниже диапазона;
• 1..10 — обычные корзины;
• 11 — значение выше диапазона или ровно на верхней границе.

Это удобно для отчётов: выбросы не исчезают, а попадают в отдельные строки.

Гистограмма сумм заказов

Представим таблицу заказов:

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    created_at  date NOT NULL,
    status      text NOT NULL,
    amount      numeric(10,2) NOT NULL
);

Хотим посмотреть распределение оплаченных заказов по сумме: от 0 до 1000, десять корзин по 100.

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

Что здесь происходит:

WIDTH_BUCKET(amount, 0, 1000, 10) вычисляет номер корзины для каждого заказа.

GROUP BY bucket собирает заказы с одинаковым номером корзины.

COUNT(*) показывает, сколько заказов попало в каждую корзину.

MIN(amount) и MAX(amount) помогают проверить реальные минимальные и максимальные суммы внутри корзины.

Такой запрос уже даёт гистограмму в табличном виде.

Например:

bucket	orders	min_amount	max_amount
1	128	0.00	99.90
2	241	100.00	199.50
3	193	200.00	299.99
10	12	910.00	999.00
11	4	1000.00	2500.00

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

Как подписать границы корзин

Номер корзины сам по себе не очень удобен для читателя. Корзина 3 — это что? От 200 до 300? От 300 до 400?

Лучше сразу вывести границы диапазона.

Если диапазон от 0 до 1000, а корзин 10, ширина одной корзины равна 100.

WITH bucketed_orders AS (
    SELECT
        amount,
        WIDTH_BUCKET(amount, 0, 1000, 10) AS bucket
    FROM orders
    WHERE status = 'paid'
)
SELECT
    bucket,
    CASE
        WHEN bucket = 0 THEN NULL
        WHEN bucket = 11 THEN 1000
        ELSE (bucket - 1) * 100
    END AS range_from,
    CASE
        WHEN bucket = 0 THEN 0
        WHEN bucket = 11 THEN NULL
        ELSE bucket * 100
    END AS range_to,
    COUNT(*) AS orders
FROM bucketed_orders
GROUP BY bucket
ORDER BY bucket;

Для обычных корзин получится понятная разметка:

bucket	range_from	range_to
1	0	100
2	100	200
3	200	300
...	...	...
10	900	1000

А служебные корзины читаются так:

• bucket = 0 — всё, что меньше 0;
• bucket = 11 — всё, что от 1000 и выше.

В реальном отчёте можно сделать ещё красивее и добавить текстовую подпись:

WITH bucketed_orders AS (
    SELECT
        amount,
        WIDTH_BUCKET(amount, 0, 1000, 10) AS bucket
    FROM orders
    WHERE status = 'paid'
)
SELECT
    CASE
        WHEN bucket = 0 THEN 'below 0'
        WHEN bucket = 11 THEN '1000 and more'
        ELSE CONCAT((bucket - 1) * 100, '-', bucket * 100)
    END AS amount_range,
    COUNT(*) AS orders
FROM bucketed_orders
GROUP BY bucket
ORDER BY bucket;

Такую таблицу уже можно показывать менеджеру, аналитику или в админке продукта.

Почему границы лучше задавать руками

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

Например: сегодня минимальный заказ 80, максимальный 1240, значит, построим диапазон от 80 до 1240.

Технически так можно. Но для регулярных отчётов это часто плохая идея.

Представьте, что в прошлом месяце корзины были такими:

Корзина	Диапазон
1	0-100
2	100-200
3	200-300

А в этом месяце из-за новых минимума и максимума стали такими:

Корзина	Диапазон
1	20-170
2	170-320
3	320-470

Теперь сравнивать отчёты неудобно. Корзина 2 в прошлом месяце и корзина 2 в этом месяце означают разные диапазоны.

Поэтому для бизнес-аналитики лучше задавать границы осознанно:

• заказы от 0 до 1000;
• зарплаты от 30000 до 150000;
• баллы теста от 0 до 100;
• время ответа от 0 до 5000 миллисекунд.

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

Пример с зарплатами сотрудников

WIDTH_BUCKET полезен не только для заказов.

Допустим, есть таблица сотрудников:

CREATE TABLE employees (
    id     bigint PRIMARY KEY,
    dept   text NOT NULL,
    salary numeric(10,2) NOT NULL
);

Хотим разложить зарплаты от 30000 до 150000 на 6 равных диапазонов.

Ширина одной корзины будет:

(150000 - 30000) / 6 = 20000

Значит, диапазоны будут примерно такие:

Корзина	Зарплата
1	30000-50000
2	50000-70000
3	70000-90000
4	90000-110000
5	110000-130000
6	130000-150000

Запрос:

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

Так можно быстро увидеть, где сосредоточена основная масса сотрудников.

А если добавить отдел, получится распределение по зарплатным вилкам внутри каждого отдела:

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

Теперь видно не только общее распределение зарплат, но и различия между отделами.

Например, может оказаться, что в разработке большинство людей в верхних корзинах, а в поддержке — в средних. Это уже не одна сухая цифра, а нормальная аналитическая картина.

WIDTH_BUCKET и NTILE: в чём разница

WIDTH_BUCKET легко спутать с NTILE, потому что обе функции как будто «делят данные на группы».

Но делят они совершенно по-разному.

WIDTH_BUCKET делит ось значений на равные интервалы.

Например:

0-100
100-200
200-300
300-400

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

NTILE делит строки на группы примерно одинакового размера.

Например, если есть 1000 клиентов и мы используем NTILE(4), получится четыре группы примерно по 250 клиентов.

Пример с NTILE:

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

Такой запрос делит клиентов на четыре квартиля по суммарной выручке.

То есть:

• WIDTH_BUCKET отвечает на вопрос: «в какой фиксированный диапазон попало значение?»;
• NTILE отвечает на вопрос: «в какую равную по количеству строк группу попала строка?».

Если нужны фиксированные пороги вроде «заказы от 0 до 100, от 100 до 200, от 200 до 300», берите WIDTH_BUCKET.

Если нужны процентили, квартилы или группы вроде «верхние 25% клиентов», берите NTILE.

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

Самая частая неожиданность — верхняя граница диапазона.

Посмотрим ещё раз:

SELECT WIDTH_BUCKET(100, 0, 100, 10) AS bucket;

Результат:

bucket
11

Хотя кажется, что 100 должно попасть в десятую корзину.

Но диапазоны устроены так:

[0, 10)
[10, 20)
[20, 30)
...
[90, 100)

Правая граница не входит в интервал. Поэтому 100 считается уже переполнением.

Что делать, если вы хотите включить максимум в последнюю корзину?

Есть несколько вариантов.

Первый — чуть расширить верхнюю границу:

SELECT WIDTH_BUCKET(score, 0, 101, 10) AS bucket
FROM exam_results;

Для тестовых баллов это может быть удобно, если значения целые и максимум равен 100.

Второй — аккуратно подрезать значение сверху:

SELECT WIDTH_BUCKET(LEAST(score, 99.999), 0, 100, 10) AS bucket
FROM exam_results;

Но такой приём нужно использовать осознанно. Вы фактически говорите: «всё, что равно верхней границе, считать частью последней обычной корзины».

Для денежных значений иногда делают так:

SELECT WIDTH_BUCKET(LEAST(amount, 999.99), 0, 1000, 10) AS bucket
FROM orders;

Но если вам важно отдельно видеть заказы от 1000 и выше, лучше оставить корзину переполнения как есть. Она покажет выбросы отдельной строкой.

Подводный камень: NULL

Если в WIDTH_BUCKET передать NULL, результат тоже будет NULL.

SELECT WIDTH_BUCKET(NULL, 0, 100, 10) AS bucket;

Такая строка не попадёт ни в обычные корзины, ни в underflow, ни в overflow.

Если вы группируете результат, NULL станет отдельной группой:

SELECT
    WIDTH_BUCKET(amount, 0, 1000, 10) AS bucket,
    COUNT(*) AS orders
FROM orders
GROUP BY bucket
ORDER BY bucket;

Если в amount есть NULL, в результате может появиться строка с пустым bucket.

Обычно лучше решить явно, что с такими строками делать.

Можно отфильтровать:

SELECT
    WIDTH_BUCKET(amount, 0, 1000, 10) AS bucket,
    COUNT(*) AS orders
FROM orders
WHERE amount IS NOT NULL
GROUP BY bucket
ORDER BY bucket;

Можно заменить NULL на отдельное значение, если это имеет смысл для вашей задачи:

SELECT
    COALESCE(WIDTH_BUCKET(amount, 0, 1000, 10), -1) AS bucket,
    COUNT(*) AS orders
FROM orders
GROUP BY bucket
ORDER BY bucket;

Здесь -1 будет означать «значение неизвестно». Это не стандартная корзина WIDTH_BUCKET, а ваша собственная договорённость для отчёта.

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

У WIDTH_BUCKET порядок аргументов важен:

WIDTH_BUCKET(value, min_value, max_value, bucket_count)

Сначала идёт само значение, потом нижняя граница, потом верхняя, потом количество корзин.

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

Например:

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

Формально это допустимый вызов, но для обычной гистограммы баллов от 0 до 100 он почти наверняка не тот, который вы хотели написать.

Поэтому в рабочих запросах лучше давать границам понятные имена, особенно если они вычисляются заранее:

WITH params AS (
    SELECT
        0 AS min_score,
        100 AS max_score,
        10 AS bucket_count
)
SELECT
    WIDTH_BUCKET(score, min_score, max_score, bucket_count) AS bucket,
    COUNT(*) AS results
FROM exam_results
CROSS JOIN params
GROUP BY bucket
ORDER BY bucket;

Так запрос читается спокойнее: меньше шансов перепутать, где минимум, где максимум.

Как эмулировать WIDTH_BUCKET в MySQL

В MySQL нет встроенной функции WIDTH_BUCKET.

Но простую версию можно собрать арифметикой.

Допустим, нам нужны корзины по 100 для сумм от 0 до 1000.

Базовая формула:

FLOOR((amount - 0) / 100) + 1

Но она не обрабатывает аккуратно значения ниже и выше диапазона. Поэтому лучше использовать CASE:

SELECT
    CASE
        WHEN amount < 0 THEN 0
        WHEN amount >= 1000 THEN 11
        ELSE FLOOR((amount - 0) / 100) + 1
    END AS bucket,
    COUNT(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY bucket
ORDER BY bucket;

Логика такая же:

• меньше 0 — корзина 0;
• от 0 до 1000 — корзины 1..10;
• 1000 и выше — корзина 11.

Это не так красиво, как WIDTH_BUCKET, зато понятно и переносимо.

А что в других СУБД

В PostgreSQL WIDTH_BUCKET есть из коробки.

В Oracle тоже есть четырёхаргументная форма WIDTH_BUCKET.

В MySQL такой функции нет, поэтому обычно используют CASE, FLOOR и арифметику.

В ClickHouse похожие задачи часто решают через округление вниз, деление на ширину корзины или функции для работы с диапазонами. Конкретный способ зависит от того, какую именно гистограмму вы строите и какие типы данных используете.

Главная идея везде одна: значение нужно превратить в номер диапазона, а потом сгруппировать строки по этому номеру.

Практический пример: распределение баллов теста

Допустим, у вас есть результаты SQL-тренажёра:

CREATE TABLE exam_results (
    id      bigint PRIMARY KEY,
    user_id bigint NOT NULL,
    score   numeric(5,2)
);

Баллы идут от 0 до 100. Хотим увидеть распределение по десяти диапазонам.

SELECT
    WIDTH_BUCKET(score, 0, 100, 10) AS bucket,
    COUNT(*) AS attempts
FROM exam_results
WHERE score IS NOT NULL
GROUP BY bucket
ORDER BY bucket;

Но помним про верхнюю границу: ровно 100 попадёт в корзину 11.

Если для учебного отчёта нужно, чтобы 100 входило в последнюю корзину, можно расширить верхнюю границу до 101:

SELECT
    WIDTH_BUCKET(score, 0, 101, 10) AS bucket,
    COUNT(*) AS attempts
FROM exam_results
WHERE score IS NOT NULL
GROUP BY bucket
ORDER BY bucket;

Теперь балл 100 попадёт в обычную последнюю корзину.

Для целых баллов это выглядит естественно: диапазон фактически становится от 0 до 101, но пользователю отчёта вы можете подписать корзины привычно — 0-10, 10-20, ..., 90-100.

Когда WIDTH_BUCKET особенно полезен

WIDTH_BUCKET хорошо подходит, когда нужно быстро увидеть распределение числовой величины.

Например:

• суммы заказов;
• зарплаты;
• баллы теста;
• длительность обработки заявки;
• время ответа сервера;
• количество товаров в заказе;
• возраст пользователей;
• размер скидки.

Везде, где есть непрерывная или почти непрерывная числовая величина, гистограмма часто полезнее среднего.

Среднее говорит: «примерно вот столько».

Гистограмма говорит: «вот где живёт основная масса данных, вот где пусто, вот где выбросы».

Для аналитики это намного богаче.

Главное из статьи

WIDTH_BUCKET в PostgreSQL возвращает номер корзины, в которую попадает число.

Классический вызов выглядит так:

SELECT WIDTH_BUCKET(value, min_value, max_value, bucket_count) AS bucket;

Функция делит диапазон от нижней границы до верхней на равные по ширине интервалы.

Если значение меньше нижней границы, вернётся 0.

Если значение больше или равно верхней границе, вернётся bucket_count + 1.

Обычная гистограмма строится так: сначала вычисляем корзину через WIDTH_BUCKET, потом группируем по ней через GROUP BY и считаем строки через COUNT(*).

WIDTH_BUCKET отличается от NTILE: первая функция делит ось значений на равные интервалы, а вторая делит строки на группы примерно равного размера.

Самая частая ловушка — верхняя граница не включается. Поэтому WIDTH_BUCKET(100, 0, 100, 10) вернёт 11, а не 10.

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