SQL ROUND(x, n): Rounding to Decimal Places, Negative n, and Currency Formatting

ROUND(x, n) округляет число x до n знаков после запятой и нужен везде, где результат показывают людям или хранят как деньги: цены, средние чеки, доли процентов. В PostgreSQL у этой функции есть подвох: двухаргументная форма работает только с типом numeric, а не с double precision, и из-за этого запрос падает на ровном месте.

Что делает ROUND(x, n)

Первый аргумент — число, второй — количество знаков после запятой. Округление идёт до ближайшего значения, а половина (ровно .5) уходит вверх: в PostgreSQL для типа numeric это округление от нуля, поэтому 2.5 превращается в 3, а -2.5 — в -3.

SELECT
  ROUND(3.14159, 2) AS pi2,   -- 3.14
  ROUND(3.14159, 0) AS pi0,   -- 3
  ROUND(2.5,     0) AS half;  -- 3

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

• n по умолчанию равен 0, то есть ROUND(x) округляет до целого.
• Результат сохраняет тип numeric и заданную точность: ROUND(2, 2) даёт 2.00, а не 2.
• Это округление, а не усечение: TRUNC(3.99, 1) даёт 3.9, тогда как ROUND(3.99, 1) даёт 4.0.

Отрицательное n: округление влево от запятой

Самая недооценённая возможность ROUND — отрицательное n. Оно округляет влево от десятичной точки: до десятков, сотен, тысяч. Это даёт готовые «корзины» для группировки сумм.

SELECT
  ROUND(12345.678, -1) AS tens,       -- 12350
  ROUND(12345.678, -2) AS hundreds,   -- 12300
  ROUND(12345.678, -3) AS thousands;  -- 12000

На практике отрицательное n помогает строить гистограмму сумм заказов без отдельной таблицы диапазонов:

SELECT
  ROUND(amount, -2) AS bucket,
  COUNT(*)          AS orders_in_bucket
FROM orders
WHERE status = 'paid'
GROUP BY ROUND(amount, -2)
ORDER BY bucket;

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

numeric против double precision: главная ловушка

Вот тот самый камень преткновения. В PostgreSQL есть round(numeric, integer), но нет round(double precision, integer). Если столбец имеет тип double precision или real, двухаргументный вызов ROUND не найдёт подходящую функцию:

-- ERROR: function round(double precision, integer) does not exist
SELECT ROUND(salary / 12.0, 2) FROM employees;

Здесь salary / 12.0 даёт double precision. Лечится приведением к numeric:

SELECT
  name,
  ROUND(salary::numeric / 12, 2) AS monthly_pay
FROM employees;

Коварство в том, что одноаргументная форма ROUND(double precision) существует и работает, поэтому ошибку легко не заметить, пока кто-то не добавит второй аргумент. Кроме того, double precision хранит число приближённо в двоичной плавающей точке, и такие значения, как 0.1, в нём непредставимы точно — для денег это прямой путь к копеечным расхождениям в суммах. Правило простое: для денег храните суммы в numeric(12, 2) с самого начала, тогда приводить к numeric ничего не придётся и округление будет десятичным, а не двоичным.

Форматирование валюты

ROUND приводит само число к нужной точности, но не добавляет знак валюты, не выравнивает разряды и не показывает завершающие нули у double precision. За внешний вид суммы отвечает не ROUND, а to_char.

SELECT
  u.name,
  SUM(o.amount)                              AS raw_total,
  ROUND(SUM(o.amount), 2)                     AS rounded_total,
  to_char(ROUND(SUM(o.amount), 2), 'FM999G999D00') AS pretty_total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.name;

Маска FM999G999D00 убирает лишние пробелы (FM), ставит разделитель тысяч (G) и фиксирует два знака после запятой (D00). Считайте сумму через ROUND, а отображайте через to_char: смешивать округление и форматирование в одном выражении не стоит.

Отличия MySQL и ClickHouse

Двухаргументный ROUND(x, n) есть и в MySQL, и в ClickHouse, но детали округления различаются.

• MySQL принимает ROUND и для целочисленных, и для дробных типов без приведения, а отрицательное n тоже работает. Для денежного формата с разделителями используйте FORMAT(x, 2).

SELECT FORMAT(ROUND(amount, 2), 2) AS pretty_total
FROM orders;

• ClickHouse для чисел с плавающей точкой по умолчанию использует банковское округление половины к чётному: round(2.5) даёт 2, а не 3. Функция roundBankers делает то же поведение явным в коде. Если запрос переносится из PostgreSQL или MySQL и вы ждёте привычного «половина вверх», проверьте поведение на значениях вроде 2.5 и 3.5, а не полагайтесь на название round.

SELECT
  round(2.5)        AS r,   -- 2 (half to even)
  roundBankers(3.5) AS rb;  -- 4

Запомните одно: ROUND отвечает за точность числа, а не за его внешний вид, и в PostgreSQL для второго аргумента вам почти всегда нужен numeric.