如何在PostgreSQL中将平均值四舍五入到小数点后两位? [英] How to round an average to 2 decimal places in PostgreSQL?

问题描述

我正在通过Ruby gem'sequel'使用PostgreSQL.

I am using PostgreSQL via the Ruby gem 'sequel'.

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

I'm trying to round to two decimal places.

这是我的代码:

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

我收到以下错误:

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

我运行以下代码时没有错误:

I get no error when I run the following code:

SELECT ROUND(AVG(some_column))
FROM table

有人知道我在做什么错吗?

Does anyone know what I am doing wrong?

推荐答案

PostgreSQL未定义round(double precision, integer).由于某些原因， @Mike Sherrill的猫召回"(Cat Recall)在评论中进行了解释，只有精度numeric的回合版本才可用.

PostgreSQL does not define round(double precision, integer). For reasons @Mike Sherrill 'Cat Recall' explains in the comments, the version of round that takes a precision is only available for 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在输出中对其进行了扩展.)

(In the above, note that float8 is just a shorthand alias for double precision. You can see that PostgreSQL is expanding it in the output).

必须将值舍入为numeric，才能使用round的两个参数形式.只需附加::numeric即可进行速记转换，例如round(val::numeric,2).

You must cast the value to be rounded to numeric to use the two-argument form of round. Just append ::numeric for the shorthand cast, like round(val::numeric,2).

如果要格式化以显示给用户，请不要使用round.使用to_char(请参阅:数据类型格式化功能手册)，它允许您指定格式并为您提供text结果，该结果不受客户端语言使用numeric值可能产生的怪异影响.例如:

If you're formatting for display to the user, don't use round. Use to_char (see: data type formatting functions in the manual), which lets you specify a format and gives you a text result that isn't affected by whatever weirdness your client language might do with numeric values. For example:

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

to_char将在格式化时为您舍入数字. FM前缀告诉to_char您不希望任何前导空格填充.

to_char will round numbers for you as part of formatting. The FM prefix tells to_char that you don't want any padding with leading spaces.

这篇关于如何在PostgreSQL中将平均值四舍五入到小数点后两位?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！