postgresql round half down function

PostgreSQL中的round(数字，整数)函数只向上取整：

round(cast (41.0255 as numeric),3) ==> 41.026

由于我们需要一个返回41.025的round函数，并且(非常令人惊讶)PostgreSQL中没有这样的函数(我们使用的是9.1.5)，我们编写了一个"包装器"函数，在第一个版本中它非常天真和粗糙。。。但是，由于缺乏对plpgsql中这类问题的本地支持，我们没有发现更好的方法。

代码如下所示。问题是对于我们的目的来说太慢了。你能提出一个更好的方法来处理这项任务吗？

这是代码：

CREATE OR REPLACE FUNCTION round_half_down(numeric,integer) RETURNS numeric 
AS $$
DECLARE
arg ALIAS FOR $1;
rnd ALIAS FOR $2;
tmp1 numeric;
res numeric;
BEGIN
tmp1:=arg;
IF cast(tmp1 as varchar) ~ '5$'  THEN res:=trunc(arg,rnd);
ELSE res:=round(arg,rnd);
END IF;
RETURN res;
END;
$$ LANGUAGE plpgsql;

我需要强制转换数值并使用regexp。。。这就是(我想)扼杀表演的原因。

正如你所知：我们需要这样做，因为我们必须比较存储在两个不同列(两个不同表上)但具有不同数字数据类型的数字：一个是双精度的，一个是实数。问题是，当插入到真实的数据类型列中时，PostgreSQL执行ROUND HALF DOWN，而它没有通过数学函数提供这样的选项！

编辑：
该函数实际上已被窃听。是第一次快速重写，试图提高工作函数的性能，但速度非常慢。

行为必须与以下内容匹配：
IF从四舍五入中推迟的十进制数字为<=5 => trunc
ELSE四舍五进。

一些例子：

select round_half_down(cast (41.002555 as numeric),3) -- 41.002 
select round_half_down(cast (41.002555 as numeric),4) -- 41.0025 
select round_half_down(cast (41.002555 as numeric),5) -- 41.00255

而PostgreSQL中的round函数给出：

select round(cast (41.002555 as numeric),3) -- 41.003

一个方法非常快速，不需要创建一个新的FUNCTION，它可以向下取整一半，如下所示：

--向上取整

round($n, 3)

--四舍五入

round($n-0.5, 3)

我们必须比较存储在两个不同列中的数字(在两个不同的表上)，但具有不同的数字数据类型：一个是双和一是真实的。

这应该非常快速和简单：

SELECT dp_col, real_col
FROM   tbl
WHERE  dp_col::real = real_col

基本上，只需将double precision数字转换为real即可进行比较。

如果这对你不起作用，这个SQL函数应该做正确的工作，并更快地工作：

CREATE OR REPLACE FUNCTION round_half_down1(numeric, int)
RETURNS numeric LANGUAGE sql AS
$func$
SELECT CASE WHEN abs($1%0.1^$2) < .6 * 0.1^$2 THEN
trunc($1, $2)
ELSE round($1, $2) END;
$func$

现在修复了负数，在注释中输入@sufleR
您也可以只使用包含的CASE表达式。

%。。模运算符
^。。幂

这里有一个可以用于基准测试的快速测试：

SELECT n                                   -- Total runtime: 36.524 ms
,round_half_down1(n,3)               -- Total runtime: 70.493 ms
,round_down_to_decimal_places(n,3)   -- Total runtime: 74.690 ms
,round_half_down(n,3)                -- Total runtime: 82.191 ms
FROM  (SELECT random()::numeric AS n FROM generate_series(1,10000)) x
WHERE  round_down_to_decimal_places(n,3)
<> round_half_down1(n,3)

它还展示了@Parveen的函数和您编辑的版本是如何计算错误的——它们在不应该的地方trunc()。

它很简单：

尝试使用此功能

CREATE OR REPLACE FUNCTION ROUND_HALF_DOWN(NUMERIC)
RETURNS NUMERIC LANGUAGE SQL AS
$FUNC$
SELECT CASE WHEN ($1%1) < 0.6 THEN FLOOR($1) ELSE CEIL($1) END;
$FUNC$

这里有一个更简单的方法

CREATE OR REPLACE FUNCTION roundHalfDown(value NUMERIC, prec INTEGER)
RETURNS NUMERIC AS $$
BEGIN
RETURN trunc(value * 10^prec + 0.5 - 0.000000001) / 10^prec;
END
$$ LANGUAGE 'plpgsql';

create or replace function round_down_to_decimal_places(numeric,integer)
returns numeric stable language sql as $$ 
select
case
when $1 >= 0 then
case when $1 - round($1, 3) < 0 then round($1, 3) - 0.001 else 
round($1, 3) end
else
case when $1 - round($1, 3) > 0 then round($1, 3) + 0.001 else 
round($1, 3) end
end
$$;

您可以通过更改(0.001表示3位小数，0.0001表示4位小数等)来使用泛型

由OP编辑：@帕维尔：我修改了你的函数，使它能以一种通用的方式工作。

create or replace function round_half_down(numeric,integer)
returns numeric stable language sql as $$ 
select
case
when $1 >= 0 then
case 
when ($1 - round($1, $2)) < 0 then cast((round($1, $2) - (1.0/(10^$2))) as numeric) 
else round($1, $2) end
else
case 
when ($1 - round($1, $2)) > 0 then cast((round($1, $2) + (1.0/(10^$2))) as numeric)
else round($1, $2) end
end
$$;

CREATE OR REPLACE FUNCTION public.round_half_down (numeric,integer)
RETURNS numeric AS
$body$
DECLARE
arg ALIAS FOR $1;
rnd ALIAS FOR $2;
tmp1 numeric;
res numeric;
vra varchar;
inta integer;
ifa boolean;
BEGIN
tmp1:= arg;
vra := substr(cast((arg - floor(arg)) as varchar),3);
ifa := null;
FOR i IN 1 .. length(vra) LOOP
inta := CAST((substr(vra,i,1)) as integer);
IF (i > rnd) THEN
IF ((ifa is null) AND inta >= 6)THEN
ifa := true;
ELSE
ifa := false;
END IF;
END IF;
END LOOP;
IF ifa THEN 
res:=trunc(arg,rnd);
ELSE 
res:=round(arg,rnd);
END IF;
RETURN res;
END;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

试试这个希望可以帮助你

相关内容

最新更新

热门标签：