SQL - 查找0到人口平均值之间的百分比

我试图找到0和该组平均值之间的每个分组行的人口百分比。例如,在下面的查询中,假设我有一行,其中num_problems为100,平均值为70.总体中有58个值(对于该行),其值低于70.我想将值(58)提取为结果元组的一部分。

select
    tm.subject_name,
    tm.topic_name,
    pm.problem_type,
    count( pa.id ) as num_problems,
    avg( pa.duration ) as average ,
    stddev( pa.duration )
from
    problem_attempt pa,
    problem_master pm,
    topic_master tm
where
    pa.problem_id = pm.id and
    pm.topic_id = tm.id and
    pa.outcome = 'Solved' and
    pa.duration > 10 and 
    pa.duration < 1000 and
    pm.problem_type = 'SCA'
group by
    tm.subject_name,
    tm.topic_name,
    pm.problem_type ;
0
投票

切勿在FROM条款中使用逗号。始终使用正确,明确,标准的JOIN语法。

您需要聚合两次或使用窗口函数。

我建议:

select subject_name, topic_name, problem_type,
       count(*) as num_problems,
       average ,
       stddev( pa.duration ),
       sum(case when pa_duration < average then 1 else 0 end) as num_less_than_average
from (select tm.subject_name, tm.topic_name, pm.problem_type,
             avg( pa.duration ) over (partition by tm.subject_name, tm.topic_name, pm.problem_type) as average
      from problem_attempt pa join
           problem_master pm
           on pa.problem_id = pm.id
           topic_master tm
           on pm.topic_id = tm.id
      where pa.outcome = 'Solved' and
            pa.duration > 10 and 
            pa.duration < 1000 and
            pm.problem_type = 'SCA'
     ) x
group by subject_name, topic_name, problem_type, average ;
热门标签: msyql | oracle | mongodb | postgresql |