在excel和其他类似的软件中,您可以使用total来获得百分比。有谁能告诉我复制整个函数的最有效方法是什么。
我使用了嵌套查询,但没有得到正确的结果
select retpre04recency,
count(*) as CustomerCount,
(select count(*) from extractsummary) as Total,
round(count(*)/(select count(*) from extractsummary),2) as CustomerCount
from extractsummary
group by retpre04recency
order by retpre04recency asc
;
我在百分比栏的结果是零。有人能帮忙吗?
最佳答案
这是类型问题。表达
count(*)
导致类型
bigint
。表达(select count(*) from extractsummary)
也会导致类型
bigint
。与某些编程语言(如R)不同,PostgreSQL中的除法运算符不会自动将整数操作数提升为小数类型。所以你必须自己投。select
retpre04recency,
count(*) as CustomerCount,
(select count(*) from extractsummary) as Total,
round(count(*)::numeric/(select count(*) from extractsummary),2) as CustomerCount
from
extractsummary
group by
retpre04recency
order by
retpre04recency asc
;
例子:
drop table if exists extractsummary;
create table extractsummary (retpre04recency int);
insert into extractsummary (retpre04recency) values (1), (1), (2), (2), (2), (3), (3), (3), (3), (4), (4), (4), (5), (5), (5), (5), (5), (6), (6), (6), (99);
select
retpre04recency,
count(*) as CustomerCount,
(select count(*) from extractsummary) as Total,
round(count(*)::numeric/(select count(*) from extractsummary),2) as CustomerCount
from
extractsummary
group by
retpre04recency
order by
retpre04recency asc
;
关于sql - 使用Total获取PostgreSQL中组百分比的最佳方法是什么,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/37242748/