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

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

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

I'm trying to round to two decimal places.

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

Here's my code:

这是我的代码:

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

I get the following error:

我得到了以下错误:

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?

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

5 个解决方案

#1

PostgreSQL does not define round(double precision, integer). For reasons @Catcall explains in the comments, the version of round that takes a precision is only available for numeric.

PostgreSQL没有定义round(双精度、整数)。出于某些原因，@Catcall在评论中解释说，只有数字才能使用具有精度的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)

(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).

(在上面，请注意float8只是双精度的一种简化别名。您可以看到PostgreSQL正在输出中扩展它)。

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(val::numeric,2)。

---

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:

如果要将格式设置为显示给用户，不要使用round。使用to_char(参见手册中的数据类型格式化函数)，它允许您指定一种格式，并为您提供一个文本结果，不会受到客户机语言对数值的奇怪程度的影响。例如:

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

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.

作为格式化的一部分，to_char将为您提供整数。FM前缀告诉to_char，不需要任何带前导空格的填充。

#2

Try also the old syntax for casting,

尝试使用旧的语法来进行强制转换，

SELECT ROUND(AVG(some_column)::numeric,2)    
FROM table;

works with any version of PostgreSQL.

适用于任何版本的PostgreSQL。

There are a lack of overloads in some PostgreSQL functions, why (???): I think "it is a lack" (!), but @CraigRinger, @Catcall and the PostgreSQL team agree about "pg's historic rationale".

在一些PostgreSQL函数中缺少重载，为什么(???):我认为“这是一个缺失”(!)，但是@CraigRinger、@Catcall和PostgreSQL团队都同意“pg的历史基本原理”。

PS: another point about rounding is accuracy, check @IanKenney's answer.

关于舍入的另一点是准确性，请查看@IanKenney的答案。

---

Overloading as casting strategy

You can overload the ROUND function with,

你可以用，

 CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $$
    SELECT ROUND($1::numeric,$2);
 $$ 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 NUMERIC type... To preserve the first commom-usage overload, we can return a FLOAT type when a TEXT parameter is offered,

但是它返回一个数值类型…为了保持第一个逗号使用重载，我们可以在提供文本参数时返回浮点类型，

 CREATE FUNCTION ROUND(float, text, int DEFAULT 0) 
 RETURNS FLOAT AS $$
    SELECT CASE WHEN $2='dec'
                THEN ROUND($1::numeric,$3)::float
                -- ... WHEN $2='hex' THEN ... WHEN $2='bin' THEN... complete!
                ELSE 'NaN'::float  -- like an 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: checking \df round after overloadings, will show something like,

加荷后检查\df轮，会显示如下内容:

 Schema     |  Name | Result data type | Argument data types 
------------+-------+------------------+----------------------------
 myschema   | round | double precision | double precision, text, int
 myschema   | round | numeric          | double precision, int
 pg_catalog | round | double precision | double precision            
 pg_catalog | round | numeric          | numeric   
 pg_catalog | round | numeric          | numeric, int          

The pg_catalog functions are the default ones, see manual of build-in math functions.

pg_catalog函数是默认函数，请参阅内置数学函数手册。

#3

Try with this:

试试这个:

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

Or simply:

或者仅仅是:

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

#4

Error:function round(double precision, integer) does not exist

错误:不存在函数圆(双精度、整数)

Solution: You need to addtype cast then it will work

解决方案:您需要添加类型转换，然后它将工作

Ex: round(extract(second from job_end_time_t)::integer,0)

例:圆形(提取(job_end_time_t二)::整数,0)

#5

According to Bryan's response you can do this to limit decimals in a query. I convert from km/h to m/s and display it in dygraphs but when I did it in dygraphs it looked weird. Looks fine when doing the calculation in the query instead. This is on postgresql 9.5.1.

根据Bryan的回答，您可以这样做来限制查询中的小数。我把km/h转换成m/s并在dygraphs中显示它但是当我在dygraphs中做的时候它看起来很奇怪。在查询中执行计算时看起来很好。这是在postgresql 9.5.1中。

select date,(wind_speed/3.6)::numeric(7,1) from readings;