返回每组最多一列的行 [英] Return row with the max value of one column per group

问题描述

我很难做到这一点，至少要搜索同一张表两次才能获取最大行，然后获取该行的值.有问题的桌子很大，所以这是不能接受的.

I am having a hard time doing this without searching the same table at least twice in order to grab the max row, and then grab the value for that row. The table in question is quite big so this is unacceptable.

这是我的桌子的样子:

SCORES
ID    ROUND    SCORE
1     1        3
1     2        6
1     3        2
2     1        10
2     2        12
3     1        6

我需要返回每个ID在最近一轮中获得的分数.也就是说，具有最高(整数)但没有最高分数的行.

I need to return the score that each ID got in the most recent round. That is, the row with the max (round), but not the max score.

OUTPUT:
ID   ROUND   SCORE
1    3       2
2    2       12
3    1       6

现在我有:

SELECT * FROM 
(SELECT id, round,
CASE WHEN (MAX(round) OVER (PARTITION BY id)) = round THEN score ELSE NULL END score
 FROM
 SCORES
 where id in (1,2,3)
) scorevals
WHERE
scorevals.round is not null;

这行得通，但效率很低(当我一开始就不能抢那些行时，我必须手动过滤掉所有这些行.)

This works, but is pretty inefficient (I have to manually filter out all of these rows, when I should just be able to not grab those rows in the first place.)

我该怎么做才能获得正确的值?

What can I do to get the right values?

推荐答案

如果没有子查询，这也是可能的:

This is also possible without subquery:

SELECT DISTINCT
       id
      ,max(round) OVER (PARTITION BY id) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

完全返回您的要求.
关键点是DISTINCT在 窗口函数之后应用.

Returns exactly what you asked for.
The crucial point is that DISTINCT is applied after window functions.

SQL提琴.

也许更快，因为它两次使用相同的窗口:

Maybe faster because it uses the same window twice:

SELECT DISTINCT
       id
      ,first_value(round) OVER (PARTITION BY id ORDER BY round DESC) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

否则做同样的事情.

这篇关于返回每组最多一列的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！