ROUND в SQL: округление, CEIL, FLOOR и ловушка целочисленного деления

Коротко

Округление — последний шаг расчёта, а не промежуточный. Всё остальное следует из этого правила и из типа числа, которое вы округляете.

• round(x, 2) — до двух знаков, round(x, -2) — до сотен. ceil округляет вверх, floor вниз, trunc отбрасывает хвост к нулю.
• На отрицательных числах floor и trunc расходятся: floor(-2.5) = -3, trunc(-2.5) = -2.
• DuckDB округляет половинки от нуля и для DOUBLE, и для DECIMAL. PostgreSQL так делает для numeric; для double precision правило зависит от платформы.
• count(*) / count(*) в PostgreSQL даёт целое число, и конверсия превращается в 0. Приводите операнд до деления: 100.0 * a / b.
• Округлённые доли не обязаны давать 100%. Считайте итоги из точных значений, а округляйте то, что показываете.
• Деньги — в DECIMAL/numeric. В DOUBLE сумма, которая должна заканчиваться на пятёрку, может округлиться не в ту сторону.

ROUND, CEIL, FLOOR и TRUNC: что делает каждая функция

Четыре функции отличаются тем, в какую сторону они сдвигают число. round берёт ближайшее значение. ceil (или ceiling, это синоним) — ближайшее не меньшее, floor — ближайшее не большее. trunc просто отрезает дробную часть, то есть двигает число к нулю.

Число знаков принимают только round и trunc. В DuckDB ceil(2.567, 1) падает с ошибкой, в PostgreSQL у ceil и floor тоже один аргумент. Если нужно «вверх до десятых», пишут ceil(x * 10) / 10.

Разница между функциями видна на отрицательных числах. Для положительных floor и trunc дают одно и то же, поэтому их легко перепутать. В разнице выручки к прошлому месяцу, в отклонении от плана, в дельте A/B-теста знак бывает любым, и там trunc(-2.5) вернёт −2, а floor(-2.5) — −3.

select
  round(2.567, 2) as round_2,    -- 2.57
  ceil(2.567)     as ceil_,      -- 3
  floor(2.567)    as floor_,     -- 2
  trunc(2.567, 2) as trunc_2,    -- 2.56
  round(-2.5)     as round_neg,  -- -3
  floor(-2.5)     as floor_neg,  -- -3
  trunc(-2.5)     as trunc_neg;  -- -2

Как округлить до 2 знаков в SQL

Средний платёж в учебной базе — avg(amount) = 24,491606714628297. Для отчёта его округляют до центов: round(avg(amount), 2) даёт 24,49. В DuckDB это работает с колонкой любого числового типа.

В PostgreSQL round(v, s) и trunc(v, s) с числом знаков определены только для numeric. Колонка amount в учебной базе имеет тип DOUBLE, и для PostgreSQL запрос пишут с приведением: round(avg(amount)::numeric, 2). Подробнее про эту и соседние ошибки типов — в статье про CAST.

Округление до десятков, сотен и тысяч: отрицательное число знаков

Отрицательный второй аргумент округляет слева от запятой: round(x, -1) — до десятков, round(x, -2) — до сотен, round(x, -3) — до тысяч. Так выручку готовят для слайда, где точность до доллара только мешает.

У такого округления тот же побочный эффект, что у любого другого: сумма округлённых месяцев не равна округлённой сумме. По сотням месяцы дают 4 000 + 10 900 + 15 800 = 30 700, а вся выручка $30 639 округляется до 30 600. Если на слайде есть строка «итого», её считают из точной суммы и подписывают, что значения округлены.

select
  cast(date_trunc('month', paid_at) as date) as month,
  sum(amount)            as revenue,
  round(sum(amount), -2) as revenue_100,
  round(sum(amount), -3) as revenue_1000
from payments
group by 1
order by 1;

Куда округляется 2.5: половинки в DuckDB и PostgreSQL

Для половинок есть два распространённых правила. «От нуля»: 2,5 → 3, −2,5 → −3. «К чётному», оно же банковское: 2,5 → 2, 3,5 → 4. Второе убирает систематический сдвиг вверх, когда половинок много.

DuckDB в round применяет правило «от нуля» и для DOUBLE, и для DECIMAL. Банковское округление там есть отдельной функцией round_even.

В PostgreSQL, по документации, round для numeric тоже округляет половинки от нуля. Для double precision поведение на половинках зависит от платформы, и чаще всего это округление к чётному. Значит, round(2.5::numeric) в PostgreSQL даёт 3, а round(2.5::float8) на типичном сервере — 2. Если результат важен до единицы, приводите к numeric и не полагайтесь на float.

Есть и третья проблема, не связанная с правилом. DOUBLE хранит число в двоичном виде, и «ровная» половинка часто хранится чуть меньше или чуть больше себя. 1,005 в DOUBLE — это 1,00499999…, поэтому round(1.005::double, 2) в DuckDB даёт 1,00, а для DECIMAL — 1,01. При этом round(2.675::double, 2) там же даёт 2,68. Предсказать заранее, в какую сторону уйдёт граница, по записи числа нельзя.

select
  round(2.5::double)                   as dbl,        -- 3
  round(2.5::decimal(4, 1))            as dec,        -- 3
  round_even(2.5, 0)                   as even,       -- 2
  round(1.005::double, 2)              as dbl_1005,   -- 1.0
  round(1.005::decimal(6, 3), 2)       as dec_1005,   -- 1.01
  printf('%.0f', 2.5::double)          as printf_25;  -- '2'

Ловушка целочисленного деления: конверсия равна нулю

Округлять бессмысленно, если дробная часть потерялась раньше. В PostgreSQL деление целого на целое отбрасывает остаток к нулю: по документации 5 / 2 = 2, (-5) / 2 = -2. count(*) возвращает целое, поэтому конверсия count(*) filter (where converted) / count(*) там равна 0 в каждой строке.

DuckDB делит оператором / всегда дробно и возвращает DOUBLE. Целочисленное деление там записывается отдельно, через //, и тоже идёт к нулю: -7 // 2 = -3. На нём удобно увидеть, что получит PostgreSQL. В эксперименте onboarding_checklist сконвертировались 467 из 1 541 участника checklist и 306 из 1 527 в control. Через // обе доли — 0, через / — 0,3030 и 0,2004.

Лечение одно: привести операнд до деления. 100.0 * a / b, a::numeric / b или a * 1.0 / b. Приведение результата, (a / b)::numeric, опоздало: к этому моменту ноль уже посчитан. Как собирать сами доли и конверсии, разобрано в статье про процент и долю в SQL.

select
  variant,
  count(*) filter (where converted) as converted,
  count(*) as exposed,
  count(*) filter (where converted) // count(*) as int_div,   -- так делит PostgreSQL
  count(*) filter (where converted) / count(*)  as share,
  round(100.0 * count(*) filter (where converted) / count(*), 1) as conversion_pct
from experiment_exposures
group by variant
order by variant;

-- checklist | 467 | 1541 | 0 | 0.3030… | 30.3
-- control   | 306 | 1527 | 0 | 0.2003… | 20

CEIL после деления: страница, которой не хватило

Та же ловушка ломает ceil. Нужно выгрузить 4 613 пользователей страницами по 20 строк: сколько страниц? В DuckDB ceil(count(*) / 20) — 231, потому что 4 613 / 20 = 230,65. В PostgreSQL деление целых сначала даёт 230, ceil(230) возвращает 230, и последние 13 пользователей не попадают ни на одну страницу.

Ошибка не заметна на круглых числах: 4 600 строк дали бы одинаковый ответ в обеих базах. Поэтому ceil после деления всегда пишут с дробным делимым: ceil(count(*) / 20.0).

select
  count(*)              as users,           -- 4613
  count(*) / 20         as pages_raw,       -- 230.65
  ceil(count(*) / 20)   as pages,           -- 231
  ceil(count(*) // 20)  as pages_pg_style   -- 230
from users;

ROUND только в конце: почему доли дают 101%

Вернёмся к первой плитке. Доли платежей по странам, округлённые до целых, — 56, 22, 12, 10 и 1. Каждое число округлено верно, но четыре из пяти округлились вверх: 21,82 → 22, 11,51 → 12, 9,91 → 10, 0,72 → 1. Лишние пункты сложились в 101%.

С одним знаком после запятой ошибка меньше, но не исчезает. Доли платежей по тарифу и типу платежа, округлённые до десятых, дают 100,1%. Если отчёту нужна ровно сотня, округлённые доли подгоняют методом наибольших остатков — он показан в статье про процент и долю. Для остального хватает подписи «сумма может отличаться от 100% из-за округления».

Правило здесь шире процентов. Любое значение, от которого считают дальше — сумму, разницу, прирост, среднее, — берут неокруглённым. Округлённые числа годятся только для показа.

with by_country as (
  select coalesce(u.country, 'не указана') as country,
         count(*) as payments,
         100.0 * count(*) / sum(count(*)) over () as pct
  from payments p
  join users u on u.user_id = p.user_id
  group by 1
)
select
  country,
  payments,
  round(pct, 2) as pct_2,
  round(pct)    as pct_0,
  sum(round(pct)) over () as total_of_rounded
from by_country
order by payments desc;

Деньги: DOUBLE против DECIMAL и комиссия с каждого платежа

DOUBLE не хранит большинство десятичных дробей точно. Классический пример — 0.1::double + 0.2::double = 0,30000000000000004, и сравнение с 0,3 возвращает false. В DECIMAL(4, 2) та же сумма равна ровно 0,30.

На деньгах это доходит до отчёта. Пусть платёжный провайдер берёт 3,5% с каждого платежа. Точная комиссия со всей выручки — 1 072,365. В DOUBLE сумма накапливается как 1 072,3649999999886 и округляется до 1 072,36. В DECIMAL — 1 072,365 и 1 072,37. Цент разницы появился только из-за типа.

Третья колонка показывает обратную сторону правила «округлять в конце». Провайдер списывает комиссию с каждого платежа отдельно и округляет её до центов в момент списания. Все 1 251 комиссия — 0,665, 1,015 или 1,365 — стоят ровно на половинке и округляются вверх. Итог — 1 078,62, на 6,25 больше округлённой общей суммы. Для сверки с выпиской правильна именно эта цифра: когда деньги списываются построчно, округляют построчно. Правило «в конце» относится к аналитическим метрикам, а не к расчётам, которые воспроизводят биллинг.

select
  sum(amount * 0.035)                                   as fee_double,     -- 1072.3649999999886
  round(sum(amount * 0.035), 2)                         as fee_double_2,   -- 1072.36
  sum(cast(amount as decimal(12, 2)) * 0.035)           as fee_decimal,    -- 1072.36500
  round(sum(cast(amount as decimal(12, 2)) * 0.035), 2) as fee_decimal_2,  -- 1072.37
  sum(round(cast(amount as decimal(12, 2)) * 0.035, 2)) as fee_per_payment -- 1078.62
from payments;

Округление и форматирование — разные операции

round возвращает число. Число 20,0 и число 20 одинаковы, и клиент вправе показать его как 20: так учебный движок и вернул конверсию control в запросе выше. Если в отчёте нужен ровно один знак после запятой, это уже форматирование, и результат у него — текст.

В DuckDB для этого есть printf('%.1f', x) и format('{:.1f}', x): оба дают строку «20.0». printf('%.1f%%', 100.0 * 306 / 1527) сразу добавит знак процента, а format('{:,}', 30639) — разделитель тысяч: «30,639». В PostgreSQL ту же работу делает to_char с шаблоном вроде 'FM990.0', где 0 — обязательная цифра, а FM убирает ведущие пробелы.

Две оговорки. Отформатированную колонку нельзя суммировать и правильно сортировать: «9.9» окажется после «10.0». И форматирование округляет по своим правилам: в DuckDB round(2.5) = 3, а printf('%.0f', 2.5) — «2». Поэтому форматируют последним шагом, а лучше отдают в BI число и формат задают там.

FLOOR в GROUP BY: бакеты по диапазонам

Чтобы разложить значения по диапазонам, делят на ширину корзины, округляют вниз и умножают обратно: floor(x / 25) * 25 — нижняя граница корзины шириной 25. Значение 49 попадает в корзину 25–50, значение 50 — уже в 50–75. Это полуоткрытые интервалы без пересечений, и их удобно подписывать.

Вот распределение платящих пользователей по сумме их платежей. Чаще всего — 411 из 912 — платили от $25 до $50.

round(x, -1) для этой задачи хуже. Он кладёт значение в ближайший десяток, то есть корзина «40» — это диапазон от 35 до 45, а не от 40 до 50. В учебной базе туда попадают пользователи с суммами 38 и 39, а корзины 50 и 70 пустые. Подпись «40» читатель поймёт как «40 и выше», и график соврёт.

with per_user as (
  select user_id, sum(amount) as revenue
  from payments
  group by user_id
)
select
  floor(revenue / 25) * 25      as bucket_from,
  floor(revenue / 25) * 25 + 25 as bucket_to,
  count(*) as users
from per_user
group by 1, 2
order by 1;

Вопросы об округлении в SQL

Как округлить вверх? ceil(x) или ceiling(x). До десятых — ceil(x * 10) / 10, потому что числа знаков ceil не принимает.

Как отбросить дробную часть без округления? trunc(x) или trunc(x, 2) до двух знаков. В PostgreSQL вариант с числом знаков работает только для numeric.

Почему round в PostgreSQL падает с ошибкой function round(double precision, integer) does not exist? Округление до знаков определено только для numeric. Пишите round(x::numeric, 2).

Как округлить процент? round(100.0 * a / b, 1): умножение на 100.0 стоит до деления и заодно спасает от целочисленного деления.

Что почитать дальше

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