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

我通过Ruby宝石“续集”使用PostgreSQL。

我要四舍五入到小数点后两位。

这是我的代码:

SELECT ROUND(AVG(some_column),2)    
FROM table

我得到以下错误:

PG::Error: ERROR:  function round(double precision, integer) does 
not exist (Sequel::DatabaseError)

当我运行以下代码时，我没有得到错误:

SELECT ROUND(AVG(some_column))
FROM table

有人知道我哪里做错了吗?

当前回答

PostgreSQL没有定义圆(双精度，整数)。出于某些原因，“猫回忆”的@Mike Sherrill在评论中解释说，需要精度的round版本只适用于数字。

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只是双精度的缩写别名。你可以看到PostgreSQL在输出中扩展了它)。

要使用round的双参数形式，必须将要舍入为数值的值强制转换。只需将::numeric附加为简化强制转换，如round(val::numeric,2)。

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

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

To_char会将数字四舍五入作为格式化的一部分。FM前缀告诉to_char不需要任何前导空格的填充。

2012-10-28 22:25:30

其他回答

您可以使用下面的函数

 SELECT TRUNC(14.568,2);

结果将显示:

14.56

你也可以将变量转换为期望类型:

 SELECT TRUNC(YOUR_VAR::numeric,2)

2019-06-10 09:51:36

试试这个:

SELECT to_char (2/3::float, 'FM999999990.00');
-- RESULT: 0.67

或者仅仅是:

SELECT round (2/3::DECIMAL, 2)::TEXT
-- RESULT: 0.67

2016-05-25 21:33:29

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

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

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
0.666	0.667	0.667	numeric	double precision	numeric

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

在另一个应用中，我们也需要一个浮点数作为结果。另一种方法是使用round(f,3)::float或创建round_tofloat()函数。另一种选择，再次重载ROUND函数，并使用浮点数的所有精度-精度范围，是在定义精度时返回一个浮点数(参见IanKenney的答案)，

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;

Try

  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

float是双精度的同义词，而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)可以重载而不会造成性能损失。在实现用户定义的强制转换函数以获得高性能时，必须注意两点:

IMMUTABLE子句对于这样的代码段非常重要，因为正如指南中所说:“允许优化器在查询使用常量参数调用函数时预估函数”。 PLpgSQL是首选语言，除了“纯SQL”。对于JIT优化(有时用于并行)，SQL语言可以获得更好的优化。类似于复制/粘贴一小段代码，而不是使用函数调用。

结论:经过优化后，上面的ROUND(float,int)函数比@CraigRinger的答案快得多;它将编译为(完全)相同的内部表示。所以，虽然它不是PostgreSQL的标准，但它可以成为你项目的标准，通过一个集中的、可重用的“代码片段库”，比如pg_pubLib。

四舍五入到第n位或其他数字表示

有些人认为，对PostgreSQL来说，四舍五入浮点数据类型的数量是没有意义的，因为浮点是二进制表示，它需要四舍五入位数或其十六进制表示。

好吧，让我们来解决这个问题，加入一个奇异的建议……这里的目的是在另一个重载函数中返回一个float类型，ROUND(float, text, int

“dec”表示“十进制表示”， 'bin'表示“二进制”表示 'hex'表示十六进制表示。

所以，在不同的表示中，我们对四舍五入的位数有不同的解释。当d计算二进制数字而不是十进制或十六进制时，用一个近似更短的值舍入一个数字x，用更少的“小数位数”(比它原来的d位)将更短。

没有c++，使用“纯SQL”是不容易的，但这段代码片段将说明并可以用作变通方案:

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

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

而\df轮也有:

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

2014-01-05 13:28:45

试着把你的列转换成一个数字:

SELECT ROUND(cast(some_column as numeric),2) FROM table

2019-12-10 21:48:52