Oracle SQL-四舍五入 [英] Oracle SQL - Round - Half

问题描述

Oracle ROUND函数默认将向上舍入":

Oracle ROUND function rounds "half up" by default :

select 3.674 my_number,
       round(3.674,2) round_on_number
from   dual
union
select 3.675 my_number,
       round(3.675,2) round_on_number
from   dual
union
select 3.676 my_number,
       round(3.676,2) round_on_number
from   dual
;

 MY_NUMBER ROUND_ON_NUMBER
---------- ---------------
     3,674            3,67
     3,675            3,68
     3,676            3,68

我需要四舍五入，这实际上意味着我应该得到以下结果:

I need to round "half down", which essentially means that I should get the following result instead :

 MY_NUMBER EXPECTED_ROUND_ON_NUMBER
---------- ------------------------
     3,674                     3,67
     3,675                     3,67
     3,676                     3,68

应该很快，因为我需要对数百万个项目执行此操作.

It should be fast as I need to do this on millions of items.

我可能可以检测到数字是否以"5"结尾，并在这种情况下截断最后一位数字，否则四舍五入，但是我感觉这效率低下(?)

I could probably detect if the number ends with a "5" and trunc that last digit in that case, round otherwise, but I have the feeling this will be inefficient (?)

谢谢！ 大卫

推荐答案

文档

1. 如果n为0，则ROUND始终返回0，而与整数无关.
2. 如果n为负，则ROUND(n，integer)返回-ROUND(-n，integer).
3. 如果n为正，则
   ROUND(n, integer) = FLOOR(n * POWER(10, integer) + 0.5) * POWER(10, -integer)

1. If n is 0, then ROUND always returns 0 regardless of integer.
2. If n is negative, then ROUND(n, integer) returns -ROUND(-n, integer).
3. If n is positive, then
   ROUND(n, integer) = FLOOR(n * POWER(10, integer) + 0.5) * POWER(10, -integer)

因此您可以修改正的非零版本:

So you could modify the positive, non-zero version:

FLOOR(n * POWER(10, integer) + 0.4) * POWER(10, -integer)
                                 ^

例如进行固定的舍入，现在暂时忽略零/负数:

e.g. for a fixed rounding, and ignoring zeros/negative for now:

with t (my_number) as (
  select 3.674 from dual
  union all select 3.675 from dual
  union all select 3.676 from dual
)
select my_number,
  floor(my_number * power(10, 2) + 0.4) * power(10, -2) as round_on_number
from  t;

 MY_NUMBER ROUND_ON_NUMBER
---------- ---------------
     3.674            3.67
     3.675            3.67
     3.676            3.68

您可以通过case表达式包含零/负数；或编写自己的函数来更整洁地处理它.

You could include zero/negative via a case expression; or write your own function to handle it more neatly.

这篇关于Oracle SQL-四舍五入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！