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

Question

我试图找到每个分组行在 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 ;

Answer 1

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