SQL FLOOR: Rounding Down to the Previous Integer and Integer Bucketing

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

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

Что делает FLOOR

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

SELECT
  FLOOR(4.9)  AS a,   -- 4
  FLOOR(4.1)  AS b,   -- 4
  FLOOR(4.0)  AS c,   -- 4
  FLOOR(-4.1) AS d;   -- -5

Ключевые свойства:

• Округление всегда вниз, а не к ближайшему: 4.9 превращается в 4, а не в 5.
• Для целого аргумента значение не меняется: FLOOR(4.0) это 4.
• Тип результата в PostgreSQL совпадает с типом аргумента: numeric остаётся numeric, double precision остаётся double precision. Чтобы получить честный integer, добавьте ::int.

Отрицательные числа уходят дальше от нуля

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

SELECT
  FLOOR(2.7)  AS pos,   -- 2
  FLOOR(-2.7) AS neg;   -- -3

Сравните с TRUNC, которая всегда отбрасывает дробную часть и движется к нулю:

SELECT
  TRUNC(-2.7) AS truncated,   -- -2
  FLOOR(-2.7) AS floored;     -- -3

Грабли: если вы вычисляете возрастные группы, баллы или координаты, где встречаются отрицательные значения, FLOOR и TRUNC дадут разные результаты. Для «отрезать дробь» берите TRUNC, для «округлить вниз по оси» берите FLOOR.

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

Бакетирование: FLOOR(x/n)*n

Самый практичный приём — раскладывать значения по равным интервалам. Чтобы сгруппировать суммы заказов в корзины по 100, делим на 100, берём FLOOR и умножаем обратно.

SELECT
  FLOOR(amount / 100) * 100 AS bucket,
  COUNT(*)                  AS orders
FROM orders
WHERE status = 'paid'
GROUP BY FLOOR(amount / 100) * 100
ORDER BY bucket;

Заказ на 250 попадёт в корзину 200, на 99 — в корзину 0. Тот же приём раскладывает зарплаты сотрудников по диапазонам в 10 тысяч:

SELECT
  FLOOR(salary / 10000) * 10000 AS salary_band,
  COUNT(*)                      AS headcount
FROM employees
GROUP BY FLOOR(salary / 10000) * 10000
ORDER BY salary_band;

Грабли: следите за целочисленным делением. Если amount имеет тип integer, то amount / 100 уже округлится до целого ещё до FLOOR, и бакеты поедут. Приводите к дробному типу: FLOOR(amount::numeric / 100) * 100.

Пара с CEIL и различия в СУБД

FLOOR всегда ходит в паре с CEIL (он же CEILING), который округляет вверх. Вместе они зажимают значение в целочисленный диапазон.

SELECT
  FLOOR(4.2) AS low,   -- 4
  CEIL(4.2)  AS high;  -- 5

Различия по диалектам:

• PostgreSQL: FLOOR сохраняет числовой тип аргумента, отрицательные обрабатываются строго по математике.
• MySQL: FLOOR над DECIMAL возвращает целое, но для DOUBLE результат тоже DOUBLE; будьте внимательны при сравнении типов.
• ClickHouse: есть floor(x) и расширение floor(x, N) с округлением до N знаков после запятой, чего нет в стандартном SQL.

Частый полезный шаблон — равномерное распределение по N группам для семплирования или шардирования:

SELECT
  id,
  FLOOR(RANDOM() * 4)::int AS shard   -- 0..3
FROM users;

Запомните три вещи: FLOOR округляет вниз, отрицательные уходят дальше от нуля, а для «обрезать дробь к нулю» нужна TRUNC, а не FLOOR.

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