postgresql - Postgresql 四舍五入到小数点后两位

Question

我试图在 Postgres SQL 中将除法和结果四舍五入到小数点后两位。我试过下面的方法，但它会将它们四舍五入为整数。

round((total_sales / total_customers)::numeric,2) as SPC,

round((total_sales / total_orders)::numeric,2) as AOV

请问如何将结果四舍五入到小数点后两位？

亲切的问候，JB78

Answer 1

假设 total_sales 和 total_customers 是 integer 列，表达式 total_sales/total_orders 产生一个 integer。

您需要在舍入它们之前至少转换其中一个，例如:total_sales/total_orders::numeric 以从除法中获得小数结果:

round(total_sales / total_orders::numeric, 2) as SPC,
round(total_sales / total_orders::numeric, 2) as AOV

例子:

create table data
(
   total_sales integer,
   total_customers integer, 
   total_orders integer
);

insert into data values (97, 12, 7), (5000, 20, 30);

select total_sales / total_orders as int_result,
       total_sales / total_orders::numeric as numeric_result,
       round(total_sales / total_orders::numeric, 2) as SPC,
       round(total_sales / total_orders::numeric, 2) as AOV
from data;

返回:

int_result | numeric_result       | spc    | aov   
-----------+----------------------+--------+-------
        13 |  13.8571428571428571 |  13.86 |  13.86
       166 | 166.6666666666666667 | 166.67 | 166.67