“半决赛”在浮点值上 [英] "Round half up" on floating point values
问题描述
pre $
SELECT ROUND（6.925e0，2） - >返回6.92
ROUND does 围绕半圈，但自浮点数不能精确地表示十进制数，则显示错误的结果（从最终用户的角度来看）。 我知道为什么发生这种情况。
我已经提出了两种可能的解决方案（都返回一个浮点数， ）
$ b
- 在舍入前转换为十进制数据类型：
SELECT CONVERT（float ，ROUND（CONVERT（十进制（29,14），6.925e0），2）） - 相乘，直到第三个数字位于左边（6.925e0 * 1000，-1）/ 1000
- Convert to a decimal data type before rounding:
SELECT CONVERT(float, ROUND(CONVERT(decimal(29,14), 6.925e0), 2)) - Multiply until the third digit is on the left-hand side of the decimal point (i.e. accurately represented), and then do the rounding:
SELECT ROUND(6.925e0 * 1000, -1) / 1000
<小数点（即准确表示），然后进行四舍五入：
SELECT ROUND / ol>
我应该选择哪一个？有更好的解决方案吗？ （不幸的是，由于一些遗留应用程序访问相同的数据库，我们无法更改数据库中的字段类型。）
是否有一个成熟的最好的做法解决这个（共同？）的问题？
（很明显，常用的技巧舍入两次已经四舍五入到三位小数 - 只要这是可能的一个浮动。）
解决方案您的第一个解决方案似乎更安全，也似乎在概念上更接近适合的问题：尽可能从浮点数转换为十进制数，在十进制类型内进行所有相关的计算，然后在写入数据库之前做最后一分钟的转换返回浮点数。 p>
编辑：您可能还需要在获取浮点值并转换为十进制数之后立即执行额外的一轮操作（例如小数点后三位或适合您的应用程序的任何内容） ，以确保你最终用实际打算的小数值。 （code> 6.925e0
）转换为十进制数将会是可能的（假设十进制格式的精度大于16位）给出非常接近但不完全等于 6.925 ; 第二个解决方案对我来说看起来并不可靠：如果 6.925e0 碰巧是由于通常的二进制浮点问题，一小部分太小？然后乘以 1000 ，结果 仍然可以在 6925 之下触摸，所以四舍五入取代了四舍五入。如果你知道你的值始终最多有3位数字，你可以在乘以1000之后做一个额外的循环来修复这个问题，比如 ROUND（ROUND（x * 1000，0），-1）。
（免责声明：虽然在处理其他上下文中的浮点和小数问题方面有很多经验，我几乎不知道SQL。）
We are stuck with a database that (unfortunately) uses floats instead of decimal values. This makes rounding a bit difficult. Consider the following example (SQL Server T-SQL):
SELECT ROUND(6.925e0, 2) --> returns 6.92
ROUND does round half up, but since floating point numbers cannot accurately represent decimal numbers, the "wrong" result (from the point of view of the end-user) is displayed. I understand why this happens.
I already came up with two possible solutions (both returning a float, which is, unfortunately, also a requirement):
Which one should I choose? Is there some better solution? (Unfortunately, we cannot change the field types in the database due to some legacy applications accessing the same DB.)
Is there a well-established best practice solution for this (common?) problem?
(Obviously, the common technique "rounding twice" will not help here since 6.925 is already rounded to three decimal places -- as far as this is possible in a float.)
Your first solution seems safer, and also seems like a conceptually closer fit to the problem: convert as soon as possible from float to decimal, do all relevant calculations within the decimal type, and then do a last minute conversion back to float before writing to the DB.
Edit: You'll likely still need to do an extra round (e.g. to 3 decimal places, or whatever's appropriate for your application) immediately after retrieving the float value and converting to decimal, to make sure that you end up with the decimal value that was actually intended. 6.925e0 converted to decimal would again be likely (assuming that the decimal format has > 16 digits of precision) to give something that's very close to, but not exactly equal to, 6.925; an extra round would take care of this.
The second solution doesn't look reliable to me: what if the stored value for 6.925e0 happens to be, due to the usual binary floating-point issues, a tiny amount too small? Then after multiplication by 1000, the result may still be a touch under 6925, so that the rounding step rounds down instead of up. If you know your value always has at most 3 digits after the point, you could fix this by doing an extra round after multiplying by 1000, something like ROUND(ROUND(x * 1000, 0), -1).
(Disclaimer: while I have plenty of experience dealing with float and decimal issues in other contexts, I know next to nothing about SQL.)
这篇关于“半决赛”在浮点值上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
