在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
    ;

我在百分比栏的结果是零。有人能帮忙吗?
sql - 使用Total获取PostgreSQL中组百分比的最佳方法是什么-LMLPHP

最佳答案

这是类型问题。表达

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中组百分比的最佳方法是什么-LMLPHP

关于sql - 使用Total获取PostgreSQL中组百分比的最佳方法是什么,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/37242748/

10-14 12:32
查看更多