选择PostgreSQL中另一列的百分比

由玩火自焚发布于 2020-04-03 20:35:29

我想按avfamily分组，选择具有非居住地价值等于true的记录数量，并将其返回为perc值。

本质上，第3栏除以第2栏乘以100。

select 

    avclassfamily, 
    count(distinct(malware_id)) as cc, 
    sum(case when livingofftheland = 'true' then 1 else 0 end),  
    (100.0 *  (sum(case when livingofftheland = 'true' then 1 else 0 end)  / (count(*)) ) )  as perc 
from malwarehashesandstrings 
group by avclassfamily  having count(*) > 5000  
order by perc desc;

可能很简单，但是我的大脑在这里空白。

牵绊情愫

2020-04-03 20:35:30

select, grouped by avfamily, the amount of records that have livingofftheland value equalling true and return it as the perc value.

You could simply use avg() for this:

select 
    avclassfamily, 
    count(distinct(malware_id)) as cc, 
    avg(livingofftheland::int) * 100 as perc 
from malwarehashesandstrings 
group by avclassfamily
having count(*) > 5000
order by perc desc

livingofftheland::int turns the boolean value to 0 (false) or 1 (true). The average of this value gives you the ratio of records that satisfy the condition in the group, as a decimal number between 0 and 1, thatn you can then multiply by 100.

det

2020-04-03 20:35:30

我将其表示为：

select avclassfamily, 
       count(distinct malware_id) as cc, 
       count(*) filter (where livingofftheland = 'true'),
       ( count(*) filter (where livingofftheland = 'true') * 100.0 /
         count(distinct malware_id)
       ) as perc
from malwarehashesandstrings 
group by avclassfamily 
having count(*) > 5000  
order by perc desc;

Note that this replaces the conditional aggregation with filter, a SQL standard construct that Postgres supports. It also puts the 100.0 right next to the /, just to be sure Postgres doesn't decide to do integer division.

玩火自焚

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3185
注册排名
2424