((这是一个维基百科!请编辑以改进!))
也可以尝试旧的类型转换语法,
SELECT ROUND( AVG(some_column)::numeric, 2 ) FROM table;
它可以与任何版本的PostgreSQL一起使用。
...但作为确定的解决方案,您可以重载ROUND函数。
以重载为转换策略
CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $f$
SELECT ROUND( CAST($1 AS numeric), $2 )
$f$ language SQL IMMUTABLE;
现在您的指令将正常工作,请尝试进行完整比较:
SELECT trunc(n,3), round(n,3) n_round, round(f,3) f_round,
pg_typeof(n) n_type, pg_typeof(f) f_type, pg_typeof(round(f,3)) f_round_type
FROM (SELECT 2.0/3.0, 2/3::float) t(n,f);
trunc |
n_round |
f_round |
n_type |
f_type |
f_round_type |
截断 |
四舍五入(保留整数部分) |
四舍五入(保留小数点后第三位) |
数字类型 |
双精度浮点型 |
数字类型 |
ROUND(float,int)函数的f_round
返回一个(十进制)NUMERIC数据类型,对于某些应用程序来说这是可以接受的:问题解决了!
在其他应用程序中,我们需要将结果作为浮点数返回。一种替代方法是使用round(f,3)::float
或创建一个round_tofloat()
函数。
另一种替代方法是再次重载ROUND
函数,并在定义准确性时使用浮点数的所有范围accuracy-precision(请参见IanKenney的回答),以返回浮点数。
CREATE FUNCTION ROUND(
input float,
accuracy float
) RETURNS float AS $f$
SELECT ROUND($1/accuracy)*accuracy
$f$ language SQL IMMUTABLE;
尝试
SELECT round(21.04, 0.05);
SELECT round(21.04, 5::float);
SELECT round(1/3., 0.0001);
SELECT round(2.8+1/3., 0.5);
SELECT round(pi(), 0.0001);
PS:在重载后,psql
命令中的\df round
将显示如下表格:
Schema | Name | Result | Argument
------------+-------+---------+------------------
myschema | round | numeric | float, int
myschema | round | float | float, float
pg_catalog | round | float | float
pg_catalog | round | numeric | numeric
pg_catalog | round | numeric | numeric, int
其中float是double precision
的同义词,而myschema是当您不使用模式时的public
。默认情况下使用pg_catalog
函数,请参阅内置数学函数指南。
四舍五入和格式化
to_char
函数在内部应用round过程,因此,当您的目的仅是在终端上显示最终结果时,您可以使用FM
修饰符作为数字格式模式的前缀:
SELECT round(x::numeric,2), trunc(x::numeric,2), to_char(x, 'FM99.99')
FROM (SELECT 2.0/3) t(x);
round |
trunc |
to_char |
0.67 |
0.66 |
.67 |
注意事项
问题原因
某些PostgreSQL函数缺乏重载,为什么(?):我认为“这是一种缺陷”,但@CraigRinger、@Catcall和PostgreSQL团队对“pg的历史原理”达成了共识。
关于性能和重用的说明
内置函数(如pg_catalog中的ROUND)可以进行重载而不会有性能损失,与直接强制类型转换相比。当实现用户定义的高性能转换函数时,必须采取两个预防措施:
结论:进行优化后,上述ROUND(float,int)
函数比@CraigRinger的答案快得多;它将编译为(完全相同的)相同的内部表示形式。因此,尽管这对于PostgreSQL来说不是标准的,但它可以成为您的项目的标准,通过一个集中和可重用的“片段库”,例如pg_pubLib。
舍入到第n位或其他数字表示法
有人认为,在PostgreSQL中对浮点数据类型进行舍入并没有意义,因为浮点是二进制表示法,需要将位数或其十六进制表示舍入。
好吧,我们来添加一个奇特的建议来解决这个问题...这里的目的是返回另一个重载的float类型函数,
ROUND(float, text, int) RETURNS float
其中text
是为了提供选择:
'dec'
表示“十进制表示法”
'bin'
表示“二进制表示法”
'hex'
表示十六进制表示。
因此,在不同的表示法中,我们对需要四舍五入的数字位数有不同的解释。使用一个近似较短值(比其原始的d个数字少"小数位")来四舍五入一个数字x,当d计算二进制而非十进制或十六进制时,将会更短。
在没有C++的情况下,仅使用“纯SQL”并不容易,但是以下代码片段可以说明并且可以用作解决方法:
CREATE FUNCTION trunc_bin(x bigint, t int) RETURNS bigint AS $f$
SELECT ((x::bit(64) >> t) << t)::bigint;
$f$ language SQL IMMUTABLE;
CREATE FUNCTION ROUND(
x float,
xtype text,
xdigits int DEFAULT 0
)
RETURNS FLOAT AS $f$
SELECT CASE
WHEN xtype NOT IN ('dec','bin','hex') THEN 'NaN'::float
WHEN xdigits=0 THEN ROUND(x)
WHEN xtype='dec' THEN ROUND(x::numeric,xdigits)
ELSE (s1 ||'.'|| s2)::float
END
FROM (
SELECT s1,
lpad(
trunc_bin( s2::bigint, CASE WHEN xd<bin_bits THEN bin_bits - xd ELSE 0 END )::text,
l2,
'0'
) AS s2
FROM (
SELECT *,
(floor( log(2,s2::numeric) ) +1)::int AS bin_bits,
CASE WHEN xtype='hex' THEN xdigits*4 ELSE xdigits END AS xd
FROM (
SELECT s[1] AS s1, s[2] AS s2, length(s[2]) AS l2
FROM (SELECT regexp_split_to_array(x::text,'\.')) t1a(s)
) t1b
) t1c
) t2
$f$ language SQL IMMUTABLE;
尝试
SELECT round(1/3.,'dec',4);
SELECT round(2.8+1/3.,'dec',1);
SELECT round(2.8+1/3.,'dec');
SELECT round(2.8+1/3.,'dec'::text);
SELECT round(2.8+1/3.,'dec',0);
SELECT round(2.8+1/3.,'hex',0);
SELECT round(2.8+1/3.,'hex',1);
SELECT round(2.8+1/3.,'hex',3);
SELECT round(2.8+1/3.,'bin',1);
SELECT round(2.8+1/3.,'bin',6);
SELECT round(2.8+1/3.,'bin',12);
而且\df round
还有以下功能:
Schema | Name | Result | Argument
myschema | round | float | x float, xtype text, xdigits int DEFAULT 0
提示:没有与给定名称和参数类型匹配的函数。您可能需要添加显式类型转换。
- Vzzarr