sql - 使用 Total 获取 PostgreSQL 中组百分比的最佳方法是什么

Question

在 excel 和其他类似软件中，您可以使用总计来获取百分比。谁能说出什么是复制全部功能的最有效方法。

我使用了嵌套查询，但我没有得到正确的结果

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
    ;

我在百分比列中的结果为零。谁能帮忙？

Answer 1

这是一个类型问题。表达式

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
;