如何在PostgreSQL中四舍五入平均到小数点后2位?

PostgreSQL没有定义round(double precision, integer)。由于@Mike Sherrill“回忆猫”在评论中解释的原因，采用精度的round版本仅适用于numeric。

regress=> SELECT round( float8 '3.1415927', 2 );
ERROR:  function round(double precision, integer) does not exist


regress=> \df *round*
List of functions
Schema   |  Name  | Result data type | Argument data types |  Type
------------+--------+------------------+---------------------+--------
pg_catalog | dround | double precision | double precision    | normal
pg_catalog | round  | double precision | double precision    | normal
pg_catalog | round  | numeric          | numeric             | normal
pg_catalog | round  | numeric          | numeric, integer    | normal
(4 rows)


regress=> SELECT round( CAST(float8 '3.1415927' as numeric), 2);
round
-------
3.14
(1 row)

(在上面，请注意float8只是double precision的缩写别名。你可以看到PostgreSQL在输出中扩展了它)。

必须将该值舍入为numeric以使用round的双参数形式。只需将::numeric作为简略转换，如round(val::numeric,2)。

如果你正在格式化显示给用户，不要使用round。使用to_char(参见手册中的数据类型格式化函数)，它允许你指定一种格式，并给你一个text结果，它不受你的客户端语言可能对numeric值所做的任何奇怪操作的影响。例如:

regress=> SELECT to_char(float8 '3.1415927', 'FM999999999.00');
to_char
---------------
3.14
(1 row)

to_char将为您舍入数字作为格式化的一部分。FM前缀告诉to_char你不需要任何前导空格。

(这是维基!请编辑以增强!))

尝试旧式的类型转换语法，

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);

轮(浮动,int)函数是f_round，它返回一个(十进制)NUMERIC数据类型，这对一些应用程序来说很好:问题解决了!

CREATE FUNCTION ROUND(
input float,     -- the input number
accuracy float   -- accuracy, the "counting unit"
) RETURNS float AS $f$
SELECT ROUND($1/accuracy)*accuracy
$f$ language SQL IMMUTABLE;

试一试

  SELECT round(21.04, 0.05);     -- 21.05 float!
SELECT round(21.04, 5::float); -- 20
SELECT round(1/3., 0.0001);    -- 0.3333
SELECT round(2.8+1/3., 0.5);   -- 3.15
SELECT round(pi(), 0.0001);    -- 3.1416

PS: \df round命令，在psql重载后，将显示类似于此表的内容

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

其中，当你不使用模式时，浮动是double precision的同义词，而myschema是public。pg_catalog函数是默认函数，请参见引导内置数学函数。

舍入和格式化

to_char函数在内部应用轮过程，因此，当你的目标只是在终端中显示最终结果时，你可以使用FM修饰符作为数字格式模式的前缀:

SELECT round(x::numeric,2), trunc(x::numeric,2), to_char(x, 'FM99.99')
FROM (SELECT 2.0/3) t(x);

-- Looking for a round_bin() function! this is only a workaround:
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,  -- 'bin', 'dec' or 'hex'
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, -- most significant bit position
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);     -- 0.3333 float!
SELECT round(2.8+1/3.,'dec',1); -- 3.1 float!
SELECT round(2.8+1/3.,'dec');   -- ERROR, need to cast string
SELECT round(2.8+1/3.,'dec'::text); -- 3 float
SELECT round(2.8+1/3.,'dec',0); -- 3 float


SELECT round(2.8+1/3.,'hex',0); -- 3 float (no change)
SELECT round(2.8+1/3.,'hex',1); -- 3.1266
SELECT round(2.8+1/3.,'hex',3); -- 3.13331578486784


SELECT round(2.8+1/3.,'bin',1);  -- 3.1125899906842625
SELECT round(2.8+1/3.,'bin',6);  -- 3.1301821767286784
SELECT round(2.8+1/3.,'bin',12); -- 3.13331578486784

 Schema     |  Name | Result  | Argument
------------+-------+---------+---------------
myschema   | round | float   | x float, xtype text, xdigits int DEFAULT 0