为什么Firebird在除法时会截断小数位? [英] Why does Firebird truncate decimal places when dividing?

问题描述

Firebird在除法而不是舍入时会舍去小数位.此外，它以分子和分母的小数位数为基础，将返回值的小数点位数作为基础.

为什么Firebird会被截断而不是四舍五入?为何将返回值基于查询中的小数位数?

Firebird 2.5:

 从rdb $ database中选择187/60.00;-结果:3.11从rdb $ database中选择187.000/60;-结果:3.116从rdb $ database中选择187.000/60.00-结果:3.11666 

SQL Server 2012:

 选择187/60.00;-结果:3.116666 

Oracle 11gR2:

 从double中选择187/60.00;-结果:3.116666666667 

MySQL 5.5.32:

 从double中选择187/60.00;-结果:3.1167 

PostgreSQL 9.3.1:

 选择187/60.00;-结果:3.116666666667 

SQLite:

 选择187/60.00;-结果:3.1166666666666667 

解决方案

在带小数点的Firebird文字中，其类型为 NUMERIC ，而不是 DOUBLE PRECISION (或其他浮动类型)点类型).这意味着它将应用其确切的数值计算规则.

因此，通过从rdb $ database 中选择187/60.00，这意味着187是 INTEGER ，而60.00是 NUMERIC(18,2).

精确数字计算的规则可以在精确数字-功能规范"中找到:

如果两个操作数OP1和OP2分别是标度为S1和S2的精确数值，则OP1 + OP2和OP1-OP2是精确度为18的精确数值，并且标定S1和S2中的较大者，而OP1 * OP2和OP1/OP2是精度为18，标度为S1 + S2的精确数值.(除除法运算外，这些操作的标度由SQL标准指定.该标准确定了所有这些运算的精度，除法的标度由实现定义:我们定义精度为18，除法的标度为S1 +S2，与乘法时标准所要求的相同.)

当操作数之一是整数类型时，它被视为小数位数为0的数字.因此，在这种情况下，您具有 NUMERIC(18,0)/NUMERIC(18,2)和根据上述规则，结果为 NUMERIC(18，0 + 2)= NUM​​ERIC(18,2).

数字似乎被截断的事实是应用精确数字计算的结果:一旦计算出最后一位数字，计算就会停止.存在余数的事实与计算结果无关:

  60.00/187 \ 3.11180---7060--10060-  (停止)40 

看看SQL:2011 Foundation规范，Firebird认为 60.00 是一个确切的数字是正确的，因为它在5.3< literal>节中具有以下用于文字的生产规则:

 < literal>:: =<带符号数字文字>|<一般文字><无符号文字>:: =<无符号数字文字>|<一般文字><带符号数字文字>:: =[< sign>]<无符号数字文字><无符号数字文字>:: =<确切的数字文字>|<近似数字文字><确切的数字文字>:: =<无符号整数>[< period>[< unsigned integer>]]|<期间><无符号整数>< sign>:: =<加号>|<减号><近似数字文字>:: =<尾数>E<指数><尾数>:: =<确切的数字文字><指数>:: =<有符号整数><有符号整数>:: =[< sign>]< unsigned integer><无符号整数>:: =< digit> ... 

和语法规则:

21)没有< period> 的<精确数字文字> 在最后一个<代码><数字> .
22)<精确数字文字> ENL的声明类型是实现定义的精确数字类型，其小数位数是右边的< digit> 的数量< period> 的值.应该有一个能够精确表示ENL值的精确数字类型.

第6.27节<数值表达式>指定以下语法规则:

1)如果二进位算术运算符的两个操作数的声明类型均为精确数值，则结果的声明类型为实现定义的精确数值类型，其精度和小数位数确定如下:
a)令S1和S2分别为第一和第二操作数的小数位数.
b)加法和减法结果的精度是实现定义的，并且小数位数是S1和S2的最大值.
c)乘法结果的精度是实现定义的，小数位数是S1 + S2.
d)除法结果的精度和小数位数是由实现定义的.

换句话说，Firebird的行为符合SQL标准.从外观上看，您尝试的其他大多数数据库(SQL Server可能例外)要么在执行除法时使用相对较大的比例值，要么似乎使用近似数值(又称双精度)行为./p>

一种解决方法是使用近似数字文字.使用指数零或 E0 将使数字成为双精度，而没有额外的10的幂.例如:

 从rdb $ database中选择187E0/60.00;-结果:3.116666666666667-  或者从rdb $ database中选择187/60.00E0;-结果:3.116666666666667 

Firebird truncates decimal places when dividing, rather than rounding. Furthermore, it bases the number of decimal points in the returned value on the number of decimal places in the numerator and denominator.

Why is Firebird truncating instead of rounding? And why does it base the returned value on the number of decimal places in the query?

Firebird 2.5:

select 187/60.00 from rdb$database; --result: 3.11
select 187.000/60 from rdb$database; --result: 3.116
select 187.000/60.00 from rdb$database --result: 3.11666

SQL Server 2012:

select 187/60.00; --result: 3.116666

Oracle 11gR2:

select 187/60.00 from dual; --result: 3.116666666667

MySQL 5.5.32:

select 187/60.00 from dual; --result: 3.1167

PostgreSQL 9.3.1:

select 187/60.00; --result: 3.116666666667

SQLite:

select 187/60.00; --result: 3.1166666666666667

解决方案

In Firebird literals with a decimal point are of type NUMERIC, not DOUBLE PRECISION (or another floating point type). This means it will apply its exact numeric calculation rules.

So with select 187/60.00 from rdb$database this means that 187 is an INTEGER and 60.00 is a NUMERIC(18,2).

The rules for exact numeric calculation can be found in "Exact Numerics - Functional Specification":

If two operands OP1 and OP2 are exact numeric with scale S1 and S2 respectively, then OP1+OP2 and OP1-OP2 are exact numeric with precision 18 and scale the larger of S1 and S2, while OP1*OP2 and OP1/OP2 are exact numeric with precision 18 and scale S1+S2. (The scales of these operation except division are specified by the SQL standard. The standard makes the precision of all these operations, and the scale of divison, implementation-defined: we define the precision as 18, and the scale of division as S1+S2, the same as is required by the standard in the case of multiplication.)

When one of the operands is an integral type, it is considered as a numeric with scale 0. So in this case you have NUMERIC(18,0)/NUMERIC(18,2) and based on the above rules, the result is NUMERIC(18, 0+2) = NUMERIC(18,2).

The fact that the number appears to be truncated is a result of the application of exact numeric calculation: the calculation stops once the last digit has been calculated. The fact that there is a remainder has no bearing on the result of the calculation:

60.00 / 187 \ 3.11
        180
        ---
          70
          60
          --
          100
           60
           -- (stop)
           40 

Looking at the SQL:2011 Foundation specification the fact Firebird considers 60.00 to be a exact numeric is correct, as it has the following production rules for literals in section 5.3 <literal>:

<literal> ::=
    <signed numeric literal>
  | <general literal>

<unsigned literal> ::=
    <unsigned numeric literal>
  | <general literal>

<signed numeric literal> ::=
    [ <sign> ] <unsigned numeric literal>

<unsigned numeric literal> ::=
    <exact numeric literal>
  | <approximate numeric literal>

<exact numeric literal> ::=
    <unsigned integer> [ <period> [ <unsigned integer> ] ]
  | <period> <unsigned integer>

<sign> ::=
    <plus sign>
  | <minus sign>

<approximate numeric literal> ::=
    <mantissa> E <exponent>

<mantissa> ::=
    <exact numeric literal>

<exponent> ::=
    <signed integer>

<signed integer> ::=
    [ <sign> ] <unsigned integer>

<unsigned integer> ::=
    <digit>...

And syntax rules:

21) An <exact numeric literal> without a <period> has an implied <period> following the last <digit>.
22) The declared type of an <exact numeric literal> ENL is an implementation-defined exact numeric type whose scale is the number of <digit>s to the right of the <period>. There shall be an exact numeric type capable of representing the value of ENL exactly.

Section 6.27 <numeric value expression> specifies the following syntax rules:

1) If the declared type of both operands of a dyadic arithmetic operator is exact numeric, then the declared type of the result is an implementation-defined exact numeric type, with precision and scale determined as follows:
a) Let S1 and S2 be the scale of the first and second operands respectively.
b) The precision of the result of addition and subtraction is implementation-defined, and the scale is the maximum of S1 and S2.
c) The precision of the result of multiplication is implementation-defined, and the scale is S1 + S2.
d) The precision and scale of the result of division are implementation-defined.

In other words the behavior of Firebird complies with the SQL standard. By the looks of it most of the other database you tried (with the possible exception of SQL Server), either use a relatively large value for the scale when performing division, or seem to use the approximate numeric (aka double precision) behavior.

A workaround would be to use an approximate numeric literal. Use of exponent zero or E0 will make the number a double precision without additional powers of ten. For example:

select 187E0/60.00 from rdb$database; -- result: 3.116666666666667
-- or
select 187/60.00E0 from rdb$database; -- result: 3.116666666666667

这篇关于为什么Firebird在除法时会截断小数位?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！