Финансовая арифметика в PostgreSQL

Вступление

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

Типы данных

Несмотря на то, что во многих источниках для таких расчетов явно рекомендуется использовать десятичные типы данных (decimal/numeric), все же проявим критическое мышление и убедимся в истинности такой рекомендации.

Для начала воспользуемся представлением чисел с плавающей запятой на максимальной точности, доступной в PostgreSQL. Попробуем сложить большое количество чисел. Понятно, что в базе данных это могут быть произвольные числа. Но, для простоты просто сложим миллион раз число 500.05 само собой используя арифметику с плавающей запятой и десятичную:

WITH Src AS (   SELECT 500.05::float8 AS flt_amt,     500.05::decimal(15,2) AS dec_amt   FROM generate_series(1,1000000) G(i) ) SELECT SUM(flt_amt), SUM(dec_amt) FROM Src;

При суммировании float8 (double precision) получаем 500 050 000.0081566, что, по правилам округления, будет 500 050 000.01. Это явно не корректно. За миллион сложений в формате float8 у нас накопилась ошибка на 1 копейку. Хотя 500 миллионов — совсем не такое уж большое число. А вот при суммировании десятичной арифметикой decimal(15,2), мы получили корректный результат 500 050 000.00

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

Иногда для рассматриваемых нами расчетов предлагается использовать арифметику с двоичной фиксированной запятой. То есть, все операции выполняются с целыми числами, а в уме мы держим, где находится десятичная точка для каждого числа. Представим ситуацию, когда в магазине на какой-то товар установлена цена 79 руб. 90 коп. Маркетинг подобные цены любит. А в кассовом чеке или отчете нам выделить НДС 18%, входящий в цену:

DO $block$ DECLARE   vat       integer = 18;   int_price integer = 7990;   int_vat   integer;   dec_price decimal(8,2) = 79.90;   dec_vat   decimal(8,2); BEGIN   int_vat=int_price*vat/(100+vat);   dec_vat=dec_price*vat/(100+vat);   RAISE NOTICE 'int %, dec %', int_vat, dec_vat; END $block$ LANGUAGE plpgsql;

Результат вычислений с двоичной фиксированной запятой оказался 1218, но так как мы держим в уме, что у нас там два знака после запятой, то интерпретируем его как 12 руб. 18 коп. А вот результат вычислений с десятичной арифметикой оказался 12 руб. 19 коп. Несложно проверить, что именно последний результат корректный, так как 79.90*18/118 ~= 12.1881. Что же произошло?

Проблема в том, двоичная арифметика с фиксированной запятой замечательно подходит для операций сложения, вычитания и умножения. А вот операция деления выполнятся по правилам целочисленного деления, когда результат не округляется, а дробная часть просто отбрасывается. И именно отброшенные 0.81 и привели к ошибке на одну копейку в меньшую сторону.

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

Значит следует просто использовать десятичную арифметику и все будет само собой хорошо? А вот и нет.

Перекрестные итоги

Пусть нам надо сформировать и сохранить в БД накладную, счет-фактуру, чек — любой документ, где есть несколько строк и в каждой строке выполняется, для примера, вычисление НДС 18%. При этом, исключительно в качестве примера, чтобы не тревожить Кодда, будем сохранять итоговые суммы в заголовке документа. Для этого создадим следующие таблицы в БД:

CREATE TABLE tmp_bill_hdr (   Id  serial        NOT NULL PRIMARY KEY,   Num varchar       NOT NULL UNIQUE, -- уникальный номер документа   Amt decimal(16,2) NOT NULL DEFAULT 0, -- Сумма по документу без НДС   Vat decimal(16,2) NOT NULL DEFAULT 0 -- Сумма НДС );  CREATE TABLE tmp_bill_det (   HdrId integer       NOT NULL REFERENCES tmp_bill_hdr(Id),   Line  integer       NOT NULL, -- порядковый номер строки   SKU   varchar       NOT NULL, -- описание единицы товара   Price decimal(16,2) NOT NULL, -- цена единицы товара   Qty   decimal(16,2) NOT NULL, -- количество   Amt   decimal(16,2) NOT NULL, -- сумма по строке без НДС   Vat   decimal(16,2) NOT NULL, -- НДС 18% по строке   CONSTRAINT tmp_bill_det_PK_Idx PRIMARY KEY (HdrId, Line) );

Попробуем сформировать новый документ из двух строк с ценой 3 коп. и 10 шт. в одной строке и 4 коп. и тоже 10 шт. во второй строке:

WITH Lines AS (   SELECT D.Line, D.SKU, D.Price, D.Qty,     ROUND(D.Price * D.Qty, 2) AS Amt,     ROUND(D.Price * D.Qty * 0.18, 2) AS Vat   FROM (VALUES     (1, '001-001-0001-01', 0.03, 10),     (2, '001-001-0002-01', 0.04, 10) ) D(Line, SKU, Price, Qty) ), Hdr AS (   INSERT INTO tmp_bill_hdr (Num, Amt, Vat)   SELECT '001/001-2024', SUM(L.Amt), SUM(L.Vat)   FROM Lines L   RETURNING Id ) INSERT INTO tmp_bill_det (HdrId, Line, SKU, Price, Qty, Amt, Vat) SELECT H.Id, L.Line, L.SKU, L.Price, L.Qty, L.Amt, L.Vat FROM Hdr H CROSS JOIN Lines L; 

В итоге у нас получились такие строки:

И такой заголовок:

Вроде бы все хорошо. Но есть проблема. 0.7*0.18=0.126. А значит НДС 18% по документу у нас должен быть 13 копеек, а вовсе на 12.

Что же произошло? А просто 0.3*0.18=0.054, а 0.3*0.18=0.072. Мы корректно округлили НДС до 5 и 7 коп. соответственно, но при этом потеряли копейку в итоге по документу.

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

WITH Lines AS (   SELECT D.Line, D.SKU, D.Price, D.Qty,     ROUND(D.Price*D.Qty, 2) AS Amt,     ROUND(       SUM(D.Price * D.Qty) OVER (ORDER BY D.Line) * 0.18, 2)       - COALESCE(           ROUND(             SUM(D.Price * D.Qty)               OVER (ORDER BY D.Line                 ROWS BETWEEN UNBOUNDED PRECEDING                   AND 1 PRECEDING) * 0.18, 2), 0) AS Vat   FROM (VALUES     (1, '001-001-0001-01', 0.03, 10),     (2, '001-001-0002-01', 0.04, 10) ) D(Line, SKU, Price, Qty) ), Hdr AS (   INSERT INTO tmp_bill_hdr (Num, Amt, Vat)   SELECT '001/002-2024', SUM(L.Amt), SUM(L.Vat)   FROM Lines L   RETURNING Id ) INSERT INTO tmp_bill_det (HdrId, Line, SKU, Price, Qty, Amt, Vat) SELECT H.Id, L.Line, L.SKU, L.Price, L.Qty, L.Amt, L.Vat FROM Hdr H CROSS JOIN Lines L; 

В итоге у нас получились такие строки:

И такой заголовок:

Теперь и НДС по документу стал правильный и ошибки округления по строкам мы относим по копейке на ту строку, на которой эта ошибка, с учетом всех предыдущих строк, накопилась.

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

Спасибо, если дочитали!