Postgresql -优化sql以根据每个学生的表现选择前3门学科
Postgresql -优化sql以根据每个学生的表现选择前3门学科
提问于 2021-06-08 14:49:09
我有一张表格，上面有学生和他们在不同科目上的成绩(同一科目的多重评价)。我想编写一个sql，以便按照以下要求输出结果。
每名学生都应选择subjects.
- There前3名科目，每门科目各选2行，
- 选出3名成绩最高的科目，第2排必须来自同一科目，不同科目的成绩相同，科目相同。如果分数相同的话，选择任何一个。学生的visualization.
- 主题可能不在一起，可能分散在桌子上，为了方便学习，我已经把它们放在一起了。
表：
student_id | subject | marks
------------|---------------|--------------
1 | sub-1 | 10
1 | sub-1 | 50
1 | sub-1 | 25
1 | sub-1 | 50
1 | sub-10 | 2
1 | sub-10 | 85
1 | sub-10 | 40
1 | sub-3 | 10
1 | sub-3 | 5
1 | sub-3 | 55
1 | sub-3 | 65
1 | sub-3 | 70
1 | sub-4 | 90
1 | sub-4 | 50
1 | sub-4 | 25
1 | sub-6 | 20
1 | sub-6 | 70
1 | sub-6 | 35
...所需结果：
student_id | subject | marks
------------|---------------|--------------
1 | sub-4 | 90
1 | sub-4 | 50
1 | sub-10 | 85
1 | sub-10 | 40
1 | sub-6 | 70
1 | sub-6 | 35我能够通过使用下面提到的sql来解决这个问题：
with cte as
(
select * from (
select
dense_rank() over(partition by s.id order by s.marks desc) dense_rank_number,
row_number() over (partition by s.id, s.subject order by marks desc) row_num,
s.*
from
(
select d.id, d.subject, count(*)
from student d
group by d.id, d.subject
having count(*) >= 2
) t join student s on t.id = s.id and t.subject = s.subject
order by 1, 2
) t5
where t5.row_num <= 2
),
cte1 as
(select e.dense_rank_number, e.row_num,
e.id, min(e.subject) as subject, e.marks from cte e
where e.row_num = 1 and e.dense_rank_number <= 3
group by e.id, e.row_num, e.marks, e.dense_rank_number
),
cte2 as
(
select cte.*
from cte, cte1
where
cte.id = cte1.id
and cte.subject = cte1.subject
and cte.row_num != cte1.row_num
)
select * from cte1
union
select * from cte2
;有更好的方法来编写这个sql吗？在这里可以找到演示：https://dbfiddle.uk/?rdbms=postgres_12&fiddle=4d9192b995884d5742977d13e4bbe68d
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-06-08 14:55:49
如果我的理解是正确的，你希望每名学生都能得到最高分的科目。然后你想要那门学科的两个最高分。如果是这样，我建议：
select s.*
from (select s.*,
dense_rank() over (partition by id order by max_marks desc, subject) as seqnum_s
from (select s.*,
row_number() over (partition by id, subject order by marks desc) as seqnum,
max(marks) over (partition by id, subject) as max_marks
from student s
) s
where seqnum <= 2
) s
where seqnum_s <= 3
order by s.id, max_marks, subject, marks desc;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67889412
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号