postgresql 对于一个在线平台，统计满分学生的数量

4bbkushb 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(3)|浏览(117)

我最近在codility平台上做了一个测试，被下面的SQL问题卡住了。

问题：对于在线平台，对3个主题进行了评估，并提供了分数。

表：评估
输入：

|Id|experience|sql|algo|bug_fixing|
|1 |5         |100|null|100       |
|2 |1         |100|100 |null      |
|3 |3         |100|null|50        |
|4 |5         |100|null|50        |
|5 |5         |100|100 |100       |

字符串
我们需要打印experience，count of students having a perfect score（null被认为是满分）作为max，count of students with that year of experience作为counts。结果将按经验年数降序打印。
输出量：

|experience|max|counts|
|5         |2  |3     |
|3         |0  |1     |
|1         |1  |1     |

型
我的解决方案：

With t1 as
(select experience, 
 count(experience) as max 
 from assessments 
 where (sql=100 or sql=null) 
 and (algo=100 or algo=null) 
 and (bug_fixing=100 or bug_fixing=null) 
group by experience) 

select a.experience,
t1.max, 
count(a.experience) as counts 
from assessments a join t1 on a.experience=t1.experience 
group by a.experience

型
但是，我在第二列（max）的输出中得到了不正确的计数。
有人能告诉我代码中的错误或需要更正的地方吗？TIA。

postgresql

来源：https://stackoverflow.com/questions/73354080/for-an-online-platform-count-the-number-of-students-with-perfect-score

3条答案

按热度按时间

agxfikkp1#

您不需要子查询或with语句。使用filter选项的聚合，例如：

select 
    experience,
    count(*) filter (where 
        coalesce(sql, 100)+ 
        coalesce(algo, 100)+ 
        coalesce(bug_fixing, 100) = 300
    ) as max,
    count(*)
from assessments
group by experience
order by experience desc

字符串
在db<>fiddle.中测试
请参阅文档中的更多信息。

赞(0）回复(0）举报 2023-08-04

a0zr77ik2#

答：

With t1 as
(select experience, 
 count(experience) as max 
 from assessment 
 where (sql=100 or sql is null) 
 and (algo=100 or algo is null) 
 and (bug_fixing=100 or bug_fixing is null) 
group by experience) 

select a.experience,
coalesce(t1.max,0), 
count(a.experience) as counts 
from assessment a left join t1 on a.experience=t1.experience 
group by a.experience,t1.max;

字符串

赞(0）回复(0）举报 2023-08-04

zaq34kh63#

感谢@klin，因为在此之前我不知道filter子句。下面是我用CASE语句的解决方案：

select 
    experience,
     sum(case when
        coalesce(sql, 100)+ 
        coalesce(algo, 100)+ 
        coalesce(bug_fixing, 100) = 300 Then 1 Else 0 End
    ) as max,
    count(exp) as count
from assessments
group by experience
order by experience desc

字符串

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 对于一个在线平台，统计满分学生的数量

3条答案

相关问题

热门标签

最新问答