为什么舍入有时会产生额外的数字?

问题描述

有时，当从数据库中选择聚合数据(通常是 AVG())时，我会得到一个重复的小数，例如:

Occasionally, when selecting aggregate data (usually AVG()) from a database, I'll get a repeating decimal such as:

2.7777777777777777

当我将 ROUND(AVG(x), 2) 应用于该值时，有时会得到如下结果:

When I apply ROUND(AVG(x), 2) to the value, I sometimes get a result like:

2.7800000000000002

我碰巧知道实际样本有 18 行，SUM(x) = 50.所以这个命令应该是等价的:

I happen to know that the actual sample has 18 rows with SUM(x) = 50. So this command should be equivalent:

SELECT ROUND(50.0/18, 2)

但是，它产生了预期的结果(2.78).为什么舍入有时会产生错误的聚合函数结果?

However, it produces the expected result (2.78). Why does rounding sometimes produce wrong results with aggregate functions?

为了验证上面的结果，我写了一个查询一个假人表:

In order to verify the above result, I wrote a query against a dummy table:

declare @temp table(
  x float
)

insert into @temp values (1.0);
insert into @temp values (2.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (1.0);
insert into @temp values (8.0);
insert into @temp values (9.0);

select round(avg(x), 2),
       sum(x),count(*)
from @temp

我知道 浮点表示的陷阱，但这个简单的案例似乎不受这些约束.

I'm aware of the gotchas of floating point representation, but this simple case seems not to be subject to those.

推荐答案

十进制数并不总是(甚至通常)1:1 映射到浮点数的精确表示.

Decimal numbers don't always (or even usually) map 1:1 to an exact representation in floating point.

在您的情况下，双精度浮点数可以表示的两个最接近的数字是；

In your case, the two closest numbers that double precision floating point can represent are;

• 40063D70A3D70A3D，大约为 2.77999999999690597766
• 40063D70A3D70A3E，大约为 2.780000000000896024786li1

• 40063D70A3D70A3D which is approximately 2.77999999999999980460074766597
• 40063D70A3D70A3E which is approximately 2.78000000000000024868995751604

在这两个数字之间不存在双精度数，在这种情况下，数据库选择了较高的值 - 如您所见 - 舍入为 2.7800000000000002.

There exists no double precision number between those two numbers, in this case the database chose the higher value which - as you see - rounds to 2.7800000000000002.

这篇关于为什么舍入有时会产生额外的数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！