sql - 如何获得百分比值？PostgreSQL

Question

这是我的代码：

select 
    referido,
    count(*) filter (where extract(month from fec_alta) = 3) ::float  as Marzo
from usuarios
where fec_alta between '2020-03-01' and '2020-04-30'
group by referido;

现在我需要将第二行除以 76（它是列的总和）

所以我有这个输出

         march  
true     60    
false    16    

我需要这个输出

         march  
true     78,9 % 
false    21,1 % 

标签： sqlpostgresql

Answer 1

只需使用窗口函数：

select referido,
       (count(*) filter (where extract(month from fec_alta) = 3)::float  /
        sum(count(*) filter (where extract(month from fec_alta) = 3)) over ()
       ) as Marzo
from usuarios
where fec_alta between '2020-03-01' and '2020-04-30'
group by referido;