我想从一列中找到负利润和零利润的比率。我试着使用聚合和子查询来实现,但似乎不起作用,因为这两个方法都返回0值。代码如下
SELECT
COUNT(CASE WHEN profit < 0 THEN 1
END) AS negative_profits,
COUNT(CASE WHEN profit < 0 THEN 1
END) / COUNT(profit),
COUNT(CASE WHEN profit = 0 THEN 1
END) AS zero_profits,
COUNT(CASE WHEN profit = 0 THEN 1
END) / COUNT(profit)
FROM sales;
SELECT (SELECT COUNT(*)
FROM sales
WHERE profit <= 0)/COUNT(profit) AS n_negative_profit
FROM sales;
两个查询都返回值为0在此处输入图像描述
避免整数除法,因为它会截断(正如Adrian所指出的(
此外,使用聚合FILTER
表达式进行简化:
SELECT count(*) FILTER (WHERE profit <= 0)::float8
/ count(profit) AS n_negative_profit
FROM sales;
如果profit
定义为NOT NULL
,或者用总计数除,则进一步优化:
SELECT count(*) FILTER (WHERE profit <= 0)::float8
/ count(*) AS n_negative_profit
FROM sales;
参见:
- 使用附加(不同(筛选器聚合列
因为您正在对每个文档进行整数除法数学运算符/函数。
numeric_type/numberic_type→numeric_type
除法(对于积分类型,除法将结果截断为零(
所以:
select 2/5;
0
您需要使其中一个数字float
或numeric
:
select 2/5::numeric;
0.40000000000000000000
为了让它更干净:
select round(2/5::numeric, 2);
0.40