Финансовая арифметика в PostgreSQL27.05.2024 00:00

Вступление

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

Типы данных

Несмотря на то, что во многих источниках для таких расчетов явно рекомендуется использовать десятичные типы данных (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;

Пояснения к SQL запросу

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

Так как у нас есть внешний ключ и строк на заголовок, для заполнение его в строках удобно использовать возможность указать в CTE INSERT … RETURNING

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

HdrId	Line	SKU	Price	Qty	Amt	Vat
1	1	001–001–0001–01	0.03	10	0.3	0.05
1	2	001–001–0002–01	0.03	10	0.4	0.07

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

Id	Num	Amt	Vat
1	001/001–2024	0.7	0.12

Вроде бы все хорошо. Но есть проблема. 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;

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

HdrId	Line	SKU	Price	Qty	Amt	Vat
1	1	001–001–0001–01	0.03	10	0.3	0.05
1	2	001–001–0002–01	0.03	10	0.4	0.08

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

Id	Num	Amt	Vat
1	001/001–2024	0.7	0.13

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

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

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