SQL 如何在PostgreSQL中将平均值四舍五入到小数点后两位？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13113096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to round an average to 2 decimal places in PostgreSQL?
提问by user1626730
I am using PostgreSQL via the Ruby gem 'sequel'.
我正在通过 Ruby gem 'sequel' 使用 PostgreSQL。
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?
有谁知道我做错了什么？
回答by Craig Ringer
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.
PostgreSQL 没有定义round(double precision, integer). 由于@Mike Sherrill 'Cat Recall'在评论中解释的原因，需要精度的 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)
(In the above, note that float8is just a shorthand alias for double precision. You can see that PostgreSQL is expanding it in the output).
（在上面，请注意这float8只是 的简写别名double precision。您可以看到 PostgreSQL 在输出中扩展了它）。
You must cast the value to be rounded to numericto use the two-argument form of round. Just append ::numericfor the shorthand cast, like round(val::numeric,2).
您必须转换要四舍五入的值才能numeric使用 的两个参数形式round。只需附加::numeric为速记演员，如round(val::numeric,2).
If you're formatting for display to the user, don't use round. Use to_char(see: data type formatting functionsin the manual), which lets you specify a format and gives you a textresult that isn't affected by whatever weirdness your client language might do with numericvalues. For example:
如果您要格式化以显示给用户，请不要使用round. 使用to_char（请参阅：手册中的数据类型格式化函数），它允许您指定格式并为您提供text不受客户端语言可能对numeric值所做的任何奇怪影响的结果。例如：
regress=> SELECT to_char(float8 '3.1415927', 'FM999999999.00');
to_char
---------------
3.14
(1 row)
to_charwill round numbers for you as part of formatting. The FMprefix tells to_charthat you don't want any padding with leading spaces.
to_char作为格式化的一部分，将为您舍入数字。该FM前缀告诉to_char你不想与前导空格任何填充。
回答by Peter Krauss
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 overloadsin 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.
PS：关于四舍五入的另一点是准确性，请查看@IanKenney 的回答。
Overloading as casting strategy
重载作为铸造策略
You can overloadthe ROUND function with,
您可以重载ROUND 函数，
CREATE FUNCTION ROUND(float,int) RETURNS NUMERIC AS $$
SELECT ROUND(::numeric,);
$$ 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,
但它返回一个 NUMERIC 类型...为了保留第一个通用使用重载，我们可以在提供 TEXT 参数时返回一个 FLOAT 类型，
CREATE FUNCTION ROUND(float, text, int DEFAULT 0)
RETURNS FLOAT AS $$
SELECT CASE WHEN ='dec'
THEN ROUND(::numeric,)::float
-- ... WHEN ='hex' THEN ... WHEN ='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 roundafter overloadings, will show something like,
PS：\df round重载后检查，会显示类似的东西，
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_catalogfunctions are the default ones, see manual of build-in math functions.
这些pg_catalog函数是默认的，请参阅内置数学函数手册。
回答by atiruz
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
回答by AdagioDev
you can use the function below
你可以使用下面的功能
SELECT TRUNC(14.568,2);
the result will show :
结果将显示：
14.56
you can also cast your variable to the desire type :
您还可以将变量强制转换为期望类型：
SELECT TRUNC(YOUR_VAR::numeric,2)
回答by kometen
According to Bryan'sresponse 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;
回答by Gabriel Jaime Sierra Rua
Try casting your column to a numeric like:
尝试将您的列转换为数字，例如：
SELECT ROUND(cast(some_column as numeric),2) FROM table
回答by user5702982
Error:function round(double precision, integer) does not exist
错误：函数round（双精度，整数）不存在
Solution: You need to addtype cast then it will work
解决方案：您需要添加类型转换然后它会起作用
Ex: round(extract(second from job_end_time_t)::integer,0)
前任： round(extract(second from job_end_time_t)::integer,0)
回答by vlatko606
select ROUND(SUM(amount)::numeric, 2) as total_amount FROM transactions
选择 ROUND(SUM(amount)::numeric, 2) 作为 total_amount FROM transactions
gives: 200234.08
给出：200234.08
