英文:
Show row-wise column value as well as the same column's aggregate using group by in Postgres
问题
我有一些数据，看起来像这样：
students:
id score section ...
--------------------------------------------
1 85 A ...
2 40 A ...
3 61 B ...
4 71 B ...
5 80 B ...
我想要显示每个学生的分数以及他们所在部分的最高分。
students:
id score section section_max_score ...
--------------------------------------------
1 85 A 85 ...
2 40 A 85 ...
3 61 B 80 ...
4 71 B 80 ...
5 80 B 80 ...
我尝试做这样的事情：
select id, score, section, max(score) as section_max_score from students group by section;
这会导致错误，要求我在group by中包括其他列。
select id, score, section, max(score) as section_max_score from students group by section, score, id;
这只显示每个学生的个别分数，而不是部分的最高分。
我漏掉了什么，如何修复它？
实际上，我需要能够在group by查询中使用特定列以及对该列进行聚合操作。
英文:
I have some data that looks like this:
students:
id score section ...
--------------------------------------------
1 85 A ...
2 40 A ...
3 61 B ...
4 71 B ...
5 80 B ...
I would like to show the score each student along with the maximum score of their section.
students:
id score section section_max_score ...
--------------------------------------------
1 85 A 85 ...
2 40 A 85 ...
3 61 B 80 ...
4 71 B 80 ...
5 80 B 80 ...
I am trying to do something like this:
select id, score, section, max(score) as section_max_score from students group by section;
This gives an error that I need to include other columns in the group by.
select id, score, section, max(score) as section_max_score from students group by section, score, id;
This just shows the individual score of each student instead of the section maximum.
What am I missing and how do I fix it?
Essentially, I need to be able to use a particular column as well an aggregate on that column in a group by query.
答案1
得分: 2
你可以使用窗口函数 max()：
select id, score, section, max(score) over ( partition by section) as section_max_score
from students
或者使用 inner join 将你的表与每个部分的最大分数数据集连接起来：
select s.*, t.section_max_score
from students s
inner join (
select section, max(score) as section_max_score
from students
group by section
) as t on s.section = t.section
英文:
You can use the window function max() :
select id, score, section, max(score) over ( partition by section) as section_max_score
from students
Or use an inner join to join your table to a dataset of maximum score per section:
select s.*, t.section_max_score
from students s
inner join (
select section, max(score) as section_max_score
from students
group by section
) as t on s.section = t.section
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论