英文:
Postgresql - efficient select distinct
问题
我有一个查询运行时间太长，我正在尝试简化它。我使用了https://explain.depesz.com/，显然我的“唯一”节点消耗了大量的计算时间。
我正在计算分区中的最大值，然后使用distinct on来保留最大值和其他列。
这是一个示例：
| class | student | grade |
|---|---|---|
| 1 | A | 0 |
| 1 | B | 10 |
| 1 | C | 11 |
| 1 | D | 7 |
| 2 | E | 20 |
| 2 | F | 18 |
| 2 | G | 19 |
基本上我想要每个教室的最高成绩：
| class | max_grade |
|---|---|
| 1 | 11 |
| 2 | 20 |
我目前的做法是：
select distinct on (class)
class, max_grade
from (select max(grade) over (partition by class) as max_grade,
student,
class)
除了使用distinct on，是否有更好的方法来获得这个结果？
英文:
I have a query that is taking too much time to run and I am trying to simplify it. I used https://explain.depesz.com/, and apparently my "unique" node is consuming a lot of computation time.
I am calculating the maximum over a partition, and then using distinct on to keep the maximal value and the other column.
Here is an example:
| class | student | grade |
|---|---|---|
| 1 | A | 0 |
| 1 | B | 10 |
| 1 | C | 11 |
| 1 | D | 7 |
| 2 | E | 20 |
| 2 | F | 18 |
| 2 | G | 19 |
Basically I want the maximal grade per classroom:
| class | max_grade |
|---|---|
| 1 | 11 |
| 2 | 20 |
what I currently do is:
select distinct on (class)
class, max_grade
from (select max(grade) over (partition by class) as max_grade,
student,
class)
Is there a better way to obtain this result than using distinct on?
答案1
得分: 1
这是最简单的聚合查询。
选择 班级, 最大(成绩) 从 表 根据 班级 分组
英文:
This is the simplest of aggregate queries.
SELECT class, MAX(grade) FROM tbl GROUP BY class
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论