Использование аналитических функций SQL в Oracle для округления сумм

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

CREATE TABLE tmp_amounts AS
SELECT 1 rnum, 675.43 amount FROM dual
UNION ALL
SELECT 2 rnum, 1022.23 amount FROM dual
UNION ALL
SELECT 3 rnum, 507.39 amount FROM dual
UNION ALL
SELECT 4 rnum, 12101.31 amount FROM dual
UNION ALL
SELECT 5 rnum, 20.14 amount FROM dual
UNION ALL
SELECT 6 rnum, 2345.11 amount FROM dual
UNION ALL
SELECT 7 rnum, 34.93 amount FROM dual
UNION ALL
SELECT 8 rnum, 9327.98 amount FROM dual
UNION ALL
SELECT 9 rnum, 4390.32 amount FROM dual
UNION ALL
SELECT 10 rnum, 5094.22 amount FROM dual

Пусть будет некий курс валюты (32,7041) с 4 знаками после запятой, который даст значения сумм во внутренней валюте с точностью более двух знаков после запятой, но эти суммы необходимо округлить до 2 знаков.

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

SELECT nvl(to_char(rnum), 'Итого') rnum,
       SUM(amount_usd) amount_usd,
       SUM(amount_rur) amount_rur,
       SUM(amount_rur_math_rounded) amount_rur_math_rounded
FROM (SELECT rnum,
             amount amount_usd,
             amount * curr_rate amount_rur,
             ROUND(amount * curr_rate, 2) amount_rur_math_rounded
      FROM (SELECT t.*,
                   32.7041 curr_rate
            FROM tmp_amounts t
           )
     )
GROUP BY GROUPING SETS((rnum), ())

Результат выполнения:



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

Предлагаю решить эту проблему с помощью аналитической функции. А именно — рассчитывать нарастающий итог по текущей и предыдущей строке и вычислять округлённую разницу. Т. е. использовать не математическое округление, а округление, с учётом накопленного значения:

SELECT nvl(to_char(rnum), 'Итого') rnum,
       SUM(amount_usd) amount_usd,
       SUM(amount_rur) amount_rur,
       SUM(amount_rur_math_rounded) amount_rur_math_rounded,
       SUM(amount_rur_rounded) amount_rur_rounded
FROM (SELECT rnum,
             amount amount_usd,
             amount * curr_rate amount_rur,
             ROUND(amount * curr_rate, 2) amount_rur_math_rounded,
             ROUND(SUM(amount * curr_rate) OVER(ORDER BY rnum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) -
             ROUND(NVL(SUM(amount * curr_rate) OVER(ORDER BY rnum ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0), 2) amount_rur_rounded
      FROM (SELECT t.*,
                   32.7041 curr_rate
            FROM tmp_amounts t
           )
     )
GROUP BY GROUPING SETS((rnum), ())

Результат выполнения:



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