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

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

本文介绍了为什么舍入有时会产生额外的数字?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

有时,当从数据库中选择聚合数据(通常是 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 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.

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

08-11 12:20