Что такое FLOOR в SQL: округление вниз без сюрпризов

FLOOR округляет число вниз до ближайшего целого.

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

Представьте числовую прямую. FLOOR всегда двигает число влево — к меньшему целому.

Для положительных чисел это выглядит как обычное «отрезали хвостик»:

• 4.9 превращается в 4;
• 4.1 превращается в 4;
• 4.0 остаётся 4.

Но на отрицательных числах начинается главный подвох:

• -4.1 превращается не в -4, а в -5.

Потому что -5 меньше, чем -4.1, а -4 уже больше.

Именно поэтому FLOOR нужно понимать не как «убрать дробь», а как «уйти вниз по числовой оси».

Что делает FLOOR

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

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

Результат:

a	b	c	d
4	4	4	-5

Разберём по-человечески.

FLOOR(4.9) возвращает 4, потому что 4 — ближайшее целое снизу.

FLOOR(4.1) тоже возвращает 4.

FLOOR(4.0) возвращает 4, потому что число уже целое.

А вот FLOOR(-4.1) возвращает -5, потому что вниз для отрицательных чисел — это дальше от нуля.

FLOOR не округляет к ближайшему

Новички часто ждут, что FLOOR(4.9) даст 5, потому что 4.9 почти 5.

Но FLOOR так не работает.

SELECT
  FLOOR(4.9) AS floor_value,
  ROUND(4.9) AS rounded_value;

Результат:

floor_value	rounded_value
4	5

ROUND округляет к ближайшему значению.

FLOOR всегда округляет вниз.

Это разные задачи. Если вы хотите получить ближайшее целое — берите ROUND. Если хотите получить нижнюю границу интервала — берите FLOOR.

Почему отрицательные числа опасны

Главная ловушка FLOOR — отрицательные значения.

Для положительных чисел кажется, что функция просто отбрасывает дробную часть:

SELECT FLOOR(2.7) AS value;

Результат:

value
2

Но для отрицательных чисел всё иначе:

SELECT FLOOR(-2.7) AS value;

Результат:

value
-3

Почему не -2?

Потому что -2 больше, чем -2.7. А FLOOR должен вернуть целое число, которое не превышает исходное значение.

На числовой оси это выглядит так:

-4       -3       -2       -1        0        1        2
          ^
        -2.7 goes down to -3

Для отчётов это может быть критично. Если у вас есть возвраты, отрицательные скидки, координаты, поправки или любые значения ниже нуля, FLOOR может дать результат, который визуально кажется неожиданным.

FLOOR и TRUNC: в чём разница

Если вам нужно именно отрезать дробную часть, часто лучше подходит TRUNC.

Сравним:

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

Результат:

truncated	floored
-2	-3

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

FLOOR округляет вниз и для отрицательных чисел движется от нуля.

Ещё пример:

SELECT
  TRUNC(2.7) AS trunc_positive,
  FLOOR(2.7) AS floor_positive,
  TRUNC(-2.7) AS trunc_negative,
  FLOOR(-2.7) AS floor_negative;

Результат:

trunc_positive	floor_positive	trunc_negative	floor_negative
2	2	-2	-3

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

Простое правило:

если нужно «отрезать дробную часть» — используйте TRUNC.

Если нужно «найти нижнюю границу на числовой оси» — используйте FLOOR.

FLOOR для бакетов и интервалов

Самый практичный сценарий для FLOOR — разложить значения по равным интервалам.

Например, у нас есть заказы, и мы хотим сгруппировать их по диапазонам сумм:

• от 0 до 99;
• от 100 до 199;
• от 200 до 299;
• от 300 до 399.

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

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

Результат может быть таким:

bucket	orders
0	18
100	42
200	35
300	21

Что означает bucket?

Это нижняя граница интервала.

Например:

amount	bucket
25	0
99	0
100	100
150	100
250	200

Сумма 250 попадает в корзину 200, потому что находится в диапазоне от 200 до 299.

Сумма 99 попадает в корзину 0, потому что находится в диапазоне от 0 до 99.

Как работает формула FLOOR(x / n) * n

Формула для бакетов выглядит так:

FLOOR(value / step) * step

Где:

• value — исходное значение;
• step — размер интервала;
• результат — нижняя граница корзины.

Например, для шага 100:

SELECT
  amount,
  FLOOR(amount / 100) * 100 AS bucket
FROM orders
ORDER BY amount;

Если amount равен 345, SQL делает так:

Сначала делит:

345 / 100

Получается 3.45.

Потом применяет FLOOR:

FLOOR(3.45)

Получается 3.

Потом умножает обратно:

3 * 100

Получается 300.

Значит, сумма 345 попадает в корзину 300.

Пример с зарплатами

Допустим, у нас есть таблица сотрудников. Нужно понять, сколько людей попадает в диапазоны зарплат по 10 000.

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

Результат:

salary_band	headcount
50000	3
60000	8
70000	11
80000	6

Здесь salary_band — это нижняя граница зарплатного диапазона.

Например, зарплата 73500 попадёт в группу 70000.

Такой отчёт удобнее читать, чем список всех уникальных зарплат. Вместо хаоса из сотен чисел мы получаем аккуратные полки.

Важный нюанс: целочисленное деление

В бакетах есть одна техническая ловушка: тип данных.

В PostgreSQL, если поделить целое число на целое число, результат тоже будет целым.

Например:

SELECT 250 / 100 AS value;

Результат:

value
2

Не 2.5, а 2.

Это значит, что дробная часть потеряется ещё до вызова FLOOR.

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

Безопаснее явно привести число к дробному типу:

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

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

FLOOR и координаты

FLOOR часто используют не только в деньгах, но и в координатах.

Например, есть точки на плоскости, и мы хотим разложить их по ячейкам сетки шириной 1.

SELECT
  FLOOR(x) AS cell_x,
  FLOOR(y) AS cell_y,
  COUNT(*) AS points
FROM points
GROUP BY FLOOR(x), FLOOR(y)
ORDER BY cell_x, cell_y;

Для положительных координат всё интуитивно:

x	cell_x
0.2	0
0.9	0
1.1	1

Но около нуля с отрицательной стороны нужно быть внимательнее:

x	cell_x
-0.1	-1
-0.9	-1
-1.1	-2

Точка -0.1 попадает в ячейку -1, а не в 0.

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

FLOOR и CEIL

FLOOR часто идёт в паре с CEIL, или CEILING.

FLOOR округляет вниз.

CEIL округляет вверх.

SELECT
  FLOOR(4.2) AS low,
  CEIL(4.2) AS high;

Результат:

low	high
4	5

Можно сказать, что они зажимают число между двумя соседними целыми:

4 < 4.2 < 5

Для 4.2 нижняя граница — 4, верхняя — 5.

С отрицательными числами логика сохраняется:

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

Результат:

low	high
-5	-4

-5 ниже, чем -4.2.

-4 выше, чем -4.2.

FLOOR для случайного распределения по группам

Иногда FLOOR используют, чтобы получить номер случайной группы.

Например, в PostgreSQL функция RANDOM() возвращает случайное число от 0 до 1. Если умножить его на 4, получится значение от 0 до 4. Затем FLOOR превратит его в один из номеров 0, 1, 2, 3.

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

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

Но есть важный нюанс: такой результат не будет стабильным. При следующем запуске запроса пользователь может попасть уже в другую группу.

Если нужна стабильная группа, лучше использовать остаток от деления по id или хэш:

SELECT
  id,
  MOD(id, 4) AS shard
FROM users;

RANDOM() подходит для случайной выборки. Для постоянного распределения пользователей по группам нужна стабильная формула.

Тип результата

В PostgreSQL FLOOR обычно сохраняет числовой тип аргумента.

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

Если передали double precision, результат тоже будет double precision.

То есть визуально вы можете получить целое число, но тип при этом не обязательно станет integer.

Например:

SELECT FLOOR(4.9::numeric) AS value;

Результат будет выглядеть как 4, но по типу это всё ещё числовое значение, а не обязательно обычный integer.

Если вам нужен именно целочисленный тип, добавьте явное приведение:

SELECT FLOOR(4.9)::int AS value;

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

Различия между СУБД

В PostgreSQL FLOOR работает строго по математическому смыслу: округляет вниз, а не к нулю. Для отрицательных чисел это значит, что FLOOR(-2.7) вернёт -3.

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

В ClickHouse есть обычная функция floor(x), а также расширенный вариант с указанием точности:

SELECT floor(123.456, 1) AS value;

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

Частые ошибки с FLOOR

Первая ошибка — думать, что FLOOR округляет к ближайшему числу.

SELECT FLOOR(9.9) AS value;

Результат:

value
9

Не 10, а 9.

Вторая ошибка — забыть про отрицательные числа.

SELECT FLOOR(-9.1) AS value;

Результат:

value
-10

Не -9, а -10.

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

SELECT
  TRUNC(-9.1) AS truncated,
  FLOOR(-9.1) AS floored;

Результат:

truncated	floored
-9	-10

Четвёртая ошибка — не определить смысл корзины.

Когда вы пишете такой запрос:

SELECT FLOOR(amount / 100) * 100 AS bucket
FROM orders;

нужно понимать, что bucket — это нижняя граница интервала.

200 здесь означает не «примерно 200», а диапазон от 200 до 299, если шаг равен 100 и значения положительные.

Когда использовать FLOOR

FLOOR хорошо подходит, когда вам нужна нижняя граница.

Например:

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

Но перед использованием стоит честно ответить на два вопроса.

Первый: в данных бывают отрицательные значения?

Если да, проверьте примеры около нуля.

Второй: вам нужно округлить вниз или просто убрать дробную часть?

Если округлить вниз — берите FLOOR.

Если убрать дробь — чаще нужен TRUNC.

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

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

SELECT FLOOR(4.9) AS value;

Результат:

value
4

FLOOR не округляет к ближайшему числу. Для этого есть ROUND.

SELECT ROUND(4.9) AS value;

Результат:

value
5

С отрицательными числами FLOOR уходит дальше от нуля.

SELECT FLOOR(-2.7) AS value;

Результат:

value
-3

Если нужно просто отбросить дробную часть, используйте TRUNC.

SELECT TRUNC(-2.7) AS value;

Результат:

value
-2

Для бакетов часто используют формулу:

SELECT FLOOR(amount::numeric / 100) * 100 AS bucket
FROM orders;

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

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