总和除数值问题(处理舍入误差) [英] sum divided values problem (dealing with rounding error)

问题描述

我的产品价格为4欧元，我需要将这笔钱分配给3个部门. 在第二列上，我需要获取此产品的行数，然后除以部门数.

I've a product that costs 4€ and i need to divide this money for 3 departments. On the second column, i need to get the number of rows for this product and divide for the number of departments.

我的查询:

select
   department, totalvalue,
   (totalvalue / (select count(*) from departments d2 where d2.department = p.product))
       dividedvalue
from products p, departments d
where d.department = p.department

Department  Total Value  Divided Value
----------  -----------  -------------
A           4            1.3333333
B           4            1.3333333
C           4            1.3333333

但是，当我对这些值求和时，我得到3,999999.当然，在数百行中，我有很大的不同... 有没有机会定义2个十进制数字并舍入最后一个值? (我的结果是1.33 1.33 1.34) 我的意思是，可以通过某种方式来调整最后一行吗?

But when I sum the values, I get 3,999999. Of course with hundreds of rows i get big differences... Is there any chance to define 2 decimal numbers and round last value? (my results would be 1.33 1.33 1.34) I mean, some way to adjust the last row?

推荐答案

如果将精度四舍五入到小数点后两位，则需要大约5,000个事务来注意相差1美分.将小数位数增加到可接受的水平将消除大多数问题，即使用9个小数，您需要大约5,000,000笔交易才能注意到一分钱的差异.

With six decimals of precision, you would need about 5,000 transactions to notice a difference of one cent, if you round the final number to two decimals. Increasing the number of decimals to an acceptable level would eliminate most issues, i.e. using 9 decimals you would need about 5,000,000 transactions to notice a difference of a cent.

这篇关于总和除数值问题(处理舍入误差)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！