Валюта округления до итоговой суммы

Question

У меня есть сумма налога с продаж, которую мне нужно равномерно разделить на 3 элемента. Например: $153.88/3 = 51.29333333333333

При округлении до 2 десятичных знаков для обозначения валюты it = $51.29. тем не мение $51.29*3=$153.87 что составляет 1 цент от общей суммы налога.

Есть ли функция для устранения этих ошибок округления, чтобы отдельные значения всегда составляли общую сумму? Итак, этот дополнительный 1 цент случайным образом распределяется на 1/3 предметов?

      select '1' as item_number, 153.88 as sales_tax, round(153.88 /3,2) as rounded
union all
select '2' as item_number, 153.88 as sales_tax, round(153.88 /3,2) as rounded
union all
select '3' as item_number, 153.88 as sales_tax, round(153.88 /3,2) as rounded
     

Answer 1

Это сложная проблема. Достаточно просто использовать floor(), ceil(), или же round()чтобы получить примерное решение. Затем вы даже можете добавить «лишнее» в одну из строк.

Однако добавка может быть больше 0,01, и это начинает выглядеть неудобно. Этого не происходит, когда у вас есть три предмета, но может случиться с большим количеством. Я предпочитаю равномерно распределять их по рядам.

Итак, рекомендую:

      with t as (
      select '1' as item_number, 153.89 as sales_tax
      union all
      select '2' as item_number, 153.89 as sales_tax
      union all
      select '3' as item_number, 153.89 as sales_tax
      union all
      select '4' as item_number, 153.89 as sales_tax
      union all
      select '5' as item_number, 153.89 as sales_tax
      union all
      select '6' as item_number, 153.89 as sales_tax
      union all
      select '7' as item_number, 153.89 as sales_tax
)
select t.*,
       (floor(sales_tax * 100 / count(*) over ()) +
        (case when floor(sales_tax * 100 / count(*) over ()) * count(*) over () +
                   row_number() over ()  - 1 < sales_tax * 100
              then 1 else 0
         end)
       ) / 100.0
from t;

Я привел пример, в котором распределение имеет значение.

Answer 2

Рассмотрим ниже

      select * except(pre_split, pos), 
  round(if(pos > round(100 * (sales_tax - sum(pre_split) over()), 2), 
    pre_split, pre_split + .01)
  , 2) final_split
from (
  select *, 
    round(sales_tax / count(1) over(), 2) pre_split,
    row_number() over(order by rand()) pos
  from `project.dataset.table`
)   

если применить к образцу данных в вашем вопросе - вывод

вывод следующего запуска

пока что следующий пробег ...

Итак, как видите - лишний цент применяется случайным образом - как randompy, как работает функция rand ():o))

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