返回每组一列最大值的行

2021-12-01 00:00:00 max sql oracle greatest-n-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.

这是我的桌子可能的样子:

Here is what my table might look like:

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;

否则做同样的事情.

相关文章