按组返回某一列最大值的行

21 浏览
0 Comments

按组返回某一列最大值的行

这个问题已经有了答案:

获取每个不同值的一列的最大值的行

Oracle SQL查询：按时间检索每个组的最新值[重复]

获取每个组的前几个结果（在Oracle中）

按MAX（DATE）进行分组 [重复]

我很难做到这一点，至少要搜索同一张表两次才能抓取最大行，然后抓取该行的值。所涉及的表很大，因此这是不可接受的。

这是我的表可能如何看起来：

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

我需要返回每个ID在最近一轮中获得的分数。也就是说，具有最大（round）的行，但不是最大分数。

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;

这个方法是有效的，但效率很低（我必须手动过滤所有这些行，当我应该首先不抓取那些行）。

我该怎么做才能得到正确的值呢？

admin 更改状态以发布 2023年5月20日
0

2 答案

0 Comments

你正在使用分析函数的正确方法。但你可能需要像下面这样使用rank函数

SELECT *
  FROM (SELECT a.*,
               rank() over (partition by id order by round desc) rnk
          FROM scores
         WHERE id IN (1,2,3))
 WHERE rnk = 1

如果存在并列（具有相同的id和round）的情况，您可能需要使用row_number分析函数而不是rank-这将任意选择两个并列行中的一个行拥有rnk为1，而不是像rank一样返回两个并列行。

如果你想使用MAX分析函数，你也可以像下面这样做 

SELECT *
  FROM (SELECT a.*,
               MAX(round) OVER (partition by id) max_round
          FROM scores
         WHERE id IN (1,2,3))
 WHERE round = max_round

0
0 Comments

这也可以不用子查询实现:

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是在窗口函数之后应用的。

SQL Fiddle。

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

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;

否则做同样的事。

0