SQL Studio / Разборы SQL / Денежная арифметика
На ценнике 16,15 ₽, а после CAST(price * 100 AS INTEGER) получилось 1614 копеек. Если исходная цена хранится как REAL, она может быть лишь приблизительным представлением десятичной дроби, а приведение к INTEGER отбрасывает дробную часть, не округляя. Для отчётов в рублях с точностью до копейки практичнее хранить уже проверенные целые копейки, складывать и вычитать их целочисленно и только в выводе делить на 100.0. Это не значит, что каждая операция с REAL неправильна: ошибка проявляется на определённых значениях и действиях.
1. REAL: равенство может не сойтись
В SQLite REAL — это двоичное число с плавающей точкой (IEEE 754 binary64), а не встроенный точный десятичный тип денег. Например, 0,1 и 0,2 рубля в бинарном формате нельзя представить точно. Первый SQL ниже намеренно использует ненадёжное прямое сравнение; оно успешно выполняется, но даёт неожиданный ответ.
WITH prices(id,price_rub) AS (VALUES (1,0.1),(2,0.2))
SELECT SUM(price_rub) AS total_rub,
SUM(price_rub)=0.3 AS exactly_30_kopecks
FROM prices;
| total_rub | exactly_30_kopecks |
|---|---|
| 0.30000000000000004 | 0 |
exactly_30_kopecks = 0 не означает, что покупатель недоплатил: это эффект двоичного представления. Если вывести только два знака, будет видно 0,30, но форматирование не превращает число в точное денежное значение. И наоборот, некоторые числа вроде 0,25 и 0,5 представляются точно; утверждать «каждое REAL-значение ошибочно» нельзя.
2. Ошибка чека: CAST не равен округлению
WITH prices(id,price_rub) AS (VALUES (1,16.15),(2,0.25))
SELECT id,price_rub,
CAST(price_rub*100 AS INTEGER) AS truncated_kopecks,
CAST(ROUND(price_rub*100) AS INTEGER) AS rounded_kopecks
FROM prices ORDER BY id;
| id | price_rub | truncated_kopecks | rounded_kopecks |
|---|---|---|---|
| 1 | 16.15 | 1614 | 1615 |
| 2 | 0.25 | 25 | 25 |
Для 16,15 ₽ итог CAST(... AS INTEGER) — 1614 копеек, а результат CAST(ROUND(... ) AS INTEGER) — 1615. У 0,25 ₽ оба способа дают 25. Следовательно, ошибка не возникает на любой цене, но молчаливое усечение недопустимо как правило перевода рублей в копейки. ROUND помогает этому демонстрационному случаю, но не задаёт все правила округления денег (например, налогов или скидок) и не исправляет потерянную точность хранения. Валидируйте и переводите десятичный ввод в копейки до расчётов, по установленной политике округления.
3. Целочисленный расчёт: итог 302 копейки
Для позиционного чека используем количество и цену одной штуки в копейках. В маленькой базе: три штуки по 33 копейки; одна за 1 копейку; две по 101 копейке. Здесь стоимости операций целые и достаточно малы, чтобы переполнения не было.
WITH lines(line_id,quantity,unit_kopecks) AS (
VALUES (1,3,33),(2,1,1),(3,2,101)
)
SELECT SUM(quantity*unit_kopecks) AS total_kopecks,
SUM(quantity*unit_kopecks)/100.0 AS total_rub,
typeof(SUM(quantity*unit_kopecks)) AS sum_storage_class
FROM lines;
| total_kopecks | total_rub | sum_storage_class |
|---|---|---|
| 302 | 3.02 | integer |
Проверим руками: 3 × 33 + 1 × 1 + 2 × 101 = 302 копейки. Выражение SUM(quantity*unit_kopecks) остаётся INTEGER в нашем наборе. Деление /100.0 даёт представление для отчёта 3,02 ₽; деление двух целых в SQLite (/100) отсекло бы дробную часть и вернуло 3. Результат /100.0 уже имеет тип REAL, поэтому для финансового хранения и дальнейшего сложения продолжайте использовать total_kopecks, а не отображаемую величину.
4. Нечётная копейка: как поделить счёт, не потеряв остаток
Отдельная проблема — распределение точной суммы между людьми. Если счёт 101 копейка делится на двоих, нельзя записать каждому 50,5 копейки. Для неотрицательных целочисленных сумм и двух получателей применим принцип: сначала целое деление, а оставшуюся копейку выдаём первому. Это явно выбранное бизнес-правило, а не обязательный стандарт.
WITH bills(bill_id,total_kopecks) AS (VALUES (1,101),(2,0),(3,5)),
recipients(pos) AS (VALUES(1),(2))
SELECT b.bill_id,r.pos,
b.total_kopecks/2 + CASE WHEN r.pos<=b.total_kopecks%2 THEN 1 ELSE 0 END
AS assigned_kopecks
FROM bills b CROSS JOIN recipients r
ORDER BY b.bill_id,r.pos;
| bill_id | pos | assigned_kopecks |
|---|---|---|
| 1 | 1 | 51 |
| 1 | 2 | 50 |
| 2 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 1 | 3 |
| 3 | 2 | 2 |
Для счёта 1 сумма частей 51 + 50 = 101, для счёта 3 — 3 + 2 = 5. Так сохраняется исходный целочисленный итог. Для отрицательных сумм, разного числа получателей, весов или налоговой базы нужны другие правила — наш SQL нельзя применять без изменения.
Упражнение: откройте итог на разных масштабах
Замените в последнем примере 101 копейку на 103. Как распределятся суммы и сохранится ли общая величина? А если цена позиции станет 1 копейка при количестве 300 — какое значение даст SUM? В тренажёре попробуйте сумму позиций заказа или средний доставленный чек, затем посмотрите на денежные единицы и округление в своём запросе. Решения этих заданий здесь не раскрываются.
Первоисточники SQLite: как представляются числа REAL · классы хранения · арифметика и целочисленное деление. Примеры не претендуют на работу без изменений в MySQL или PostgreSQL.
Здесь выполняем SQL на SQLite через браузер. Для системного обучения отдельно существует курс SQL для аналитика на Stepik, в котором используется MySQL 8 и отличаются некоторые числовые правила.