PostgreSQL - 四舍五入浮点数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16246886/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - rounding floating point numbers
提问by Dinesh
I have a newbie question about floating point numbers in PostgreSQL 9.2.
我有一个关于 PostgreSQL 9.2 中浮点数的新手问题。
Is there a function to round a floating point number directly, i.e. without having to convert the number to a numeric type first?
是否有直接舍入浮点数的函数，即不必先将数字转换为数字类型？
Also, I would like to know whether there is a function to round by an arbitrary unit of measure, such as to nearest 0.05?
另外，我想知道是否有一个函数可以按任意度量单位舍入，例如最接近 0.05？
When casting the number into a decimal form first, the following query works perfectly:
首先将数字转换为十进制形式时，以下查询完美运行：
SELECT round(1/3.::numeric,4);
round
--------
0.3333
(1 row)
Time: 0.917 ms
However, what really I'd like to achieve is something like the following:
但是，我真正想要实现的是以下内容：
SELECT round(1/3.::float,4);
which currently gives me the following error:
目前给我以下错误：
ERROR: function round(double precision, integer) does not exist at character 8
Time: 0.949 ms
Thanks
谢谢
回答by Peter Krauss
Try
尝试
SELECT round((1/3.)::numeric,4);
works with any version of PostgreSQL.
适用于任何版本的 PostgreSQL。
There are a lack of overloads in some PostgreSQL functions, why (???): I think "it is a lack", and below show my workaround, but see this discussion for more explanations.
某些 PostgreSQL 函数中缺少重载，为什么 (???)：我认为“这是一个缺少”，下面显示了我的解决方法，但请参阅此讨论以获取更多解释。
Overloading as casting strategy
重载作为铸造策略
You can overloadthe ROUND function with,
您可以重载ROUND 函数，
CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $$
SELECT ROUND(::numeric,);
$$ language SQL IMMUTABLE;
Now your instruction will works fine, try (after function creation)
现在您的指令将正常工作，请尝试（创建函数后）
SELECT round(1/3.,4); -- 0.3333 numeric
but it returns a type NUMERIC... To preserve the first commom-usage overload, we can return a float when a text parameter is offered,
但它返回一个 NUMERIC 类型...为了保留第一个通用用法重载，我们可以在提供文本参数时返回一个浮点数，
CREATE FUNCTION ROUND(float, text, int DEFAULT 0)
RETURNS FLOAT AS $$
SELECT CASE WHEN ='dec'
THEN ROUND(::numeric,)::float
-- ... WHEN ='hex' THEN ... WHEN ='bin' THEN...
ELSE 'NaN'::float -- is like a error message
END;
$$ language SQL IMMUTABLE;
Try
尝试
SELECT round(1/3.,'dec',4); -- 0.3333 float!
SELECT round(2.8+1/3.,'dec',1); -- 3.1 float!
SELECT round(2.8+1/3.,'dec'::text); -- need to cast string? pg bug
PS: You can check the overloading by \df,
PS：您可以通过\df检查重载，
\df round
Schema | Name | Datatype of result | Datatype of parameters
-----------+-------+---------------------------+--------------------------------
myschema | round | numeric | double precision, integer
myschema | round | double precision | double precision, text, integer
pg_catalog | round | double precision | double precision
pg_catalog | round | numeric | numeric
pg_catalog | round | numeric | numeric, integer
The pg_catalog functions are the default ones, see manual of build-in math functions.
pg_catalog 函数是默认函数，请参阅内置数学函数手册。
回答by Ian Kenney
You can accomplish this by doing something along the lines of
你可以通过做一些事情来实现这一点
select round( (21.04 /0.05 ),0)*0.05
where 21.04is the number to round and 0.05is the accuracy.
其中21.04是要舍入的数字，0.05是准确度。
