了解Oracle中restder()函数的行为 [英] Understanding behavior of remainder() function in Oracle

问题描述

根据Oracle文档中的rest(n2，n1)函数(

According to Oracle documentation for the remainder(n2,n1) function (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions133.htm), the remainder(n2,n1) function evaluates to = n2 - n1 * N where N = ROUND(n2/n1) and n1 != 0.

因此，我希望使用上述记录的计算余数的方法，使remainder(10,4)返回-2，如下所示:

So, I would expect remainder(10,4) to return -2 using the above documented method of calculating the remainder as shown below:

REMAINDER(10,4)
=10 - 4 * N where N = ROUND(10/4) 
=10 - 4 * 3 since ROUND(10/4) = 3 
=10 - 12
=-2

但是，在Oracle 10g，11g和12c中运行剩余(10,4)会返回2，而不是-2.

However, running remainder(10,4) in Oracle 10g, 11g, and 12c returns 2 instead of -2.

有人可以解释为什么余数(10,4)没有按照oracle如何评估余数的方法记录吗?

Can someone please explain why the remainder(10,4) is not evaluating as per the documented method of how oracle evaluates the remainder?

其他信息:此行为对于每隔n2个(n2是n1/2的倍数)都会发生.可以通过运行以下SQL进行验证，其中每个余数函数值应与其旁边的值匹配.但它仅与n2的每个其他值匹配. 运行SQL:

Additional information: This behavior happens for every other n2 where n2 is a multiple of n1 / 2. It can be verified by running the following SQL where each remainder function value should match the value next to it. But it only matches for every other value of n2. Running the SQL:

select
remainder(2,4), 2-4*round(2/4),--OUTPUT:2, -2
remainder(6,4),6-4*round(6/4), --OUTPUT: -2, -2
remainder(10,4),10-4*round(10/4),--OUTPUT: 2, -2
remainder(14,4),14-4*round(14/4)--OUTPUT: -2, -2
from dual 

推荐答案

编写文档的人(他们似乎不记得算术中的一些定义)自己似乎不确定自己写的是什么.一方面，在解释的早期，他们确实提到了ROUND-但后来，当他们给出更正式的定义时，他们说了

The people who wrote the documentation (who seem not to recall some definitions from arithmetic) themselves seem unsure about what they wrote. On the one hand, early in the explanation they do mention ROUND - yet later when they give a more formal definition they say

•如果n1！= 0，则余数为n2-(n1 * N)，其中N为整数 最近的n2/n1

•If n1 != 0, then the remainder is n2 - (n1*N) where N is the integer nearest n2/n1

整数最接近"在算术上没有正式定义，实际上，除x的小数部分正好为0.5时(在这种情况下，整数最接近"是模棱两可的并且一个不正确)，实际上允许一个名称用于round(x).可以选择使用向下舍入"作为自己对整数最近"的定义.

"Integer nearest" is not formally defined in arithmetic, and indeed one is permitted to use that name for round(x) except when the fractional part of x is exactly 0.5, in which case "integer nearest" is ambiguous and one may choose to use "round down" as their own definition of "integer nearest."

如果可以的话，请不要对文档中的此类不一致感到不满.您会看到更多.

Don't get too upset with such inconsistencies in the documentation, if you can. You will see many more.

但是:更糟糕的是行为不一致.我使用的是Oracle 12.1，在我的机器上，我尝试了一下，然后得到了

However: What is MUCH worse is that the behavior is inconsistent. I use Oracle 12.1, and on my machine I just tried and I get

remainder(10, 4) =  2
remainder( 6, 4) = -2

没有韵律或理由.最好使用FLOOR之类的东西来进行自己的划分.

No rhyme or reason. Much better to do your own division, using FLOOR and such.

编辑-也许有某些原因；也许他们使用最近整数"的定义来表示(如果是平局)最接近的偶数整数.仍然会产生意想不到的结果，最好不要使用Oracle的REMAINDER()函数.

Edit - Or maybe there is some reason; perhaps they use a definition of "nearest integer" to mean, in the case of a tie, the nearest even integer. Still producing unexpected results, best not to use Oracle's REMAINDER() function.

这篇关于了解Oracle中restder()函数的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！