sql - 特定列的postgresql(redshift)最大值

Question

我正在研究 redshift - 我有一张类似的 table

userid  oid version number_of_objects
1       ab  1       10
1       ab  2       20
1       ab  3       17
1       ab  4       16
1       ab  5       14
1       cd  1       5
1       cd  2       6
1       cd  3       9
1       cd  4       12
2       ef  1       4
2       ef  2       3
2       gh  1       16
2       gh  2       12
2       gh  3       21

我想从这个表中选择每个 oid 的最大版本号并获取 userid 和行号。

当我尝试这个时，不幸的是我把整张 table 都拿回来了:

SELECT MAX(version), oid, userid, number_of_objects
FROM table
GROUP BY oid, userid, number_of_objects
LIMIT 10;

但真正的结果，我正在寻找的是:

userid  oid MAX(version)    number_of_objects
1       ab  5               14
1       cd  4               12
2       ef  2               3
2       gh  3               21

它说:

SELECT DISTINCT ON is not supported

你有什么想法吗？

---

更新:与此同时，我想出了这个解决方法，但我觉得这不是最聪明的解决方案。它也很慢。但它至少有效。以防万一:

SELECT * FROM table,
   (SELECT MAX(version) as maxversion, oid, userid
    FROM table
    GROUP BY oid, userid
    ) as maxtable
    WHERE  table.oid = maxtable.oid
   AND table.userid = maxtable.userid
   AND table.version = maxtable.version
LIMIT 100;

你有更好的解决方案吗？

Answer 1

如果redshift确实有窗口函数，你可以试试这个:

SELECT * 
FROM (
  select oid, 
         userid, 
         version,
         max(version) over (partition by oid, userid) as max_version, 
  from the_table
) t
where version = max_version;

我希望这比使用 group by 的自加入更快。

另一种选择是使用 row_number() 函数:

SELECT * 
FROM (
  select oid, 
         userid, 
         version,
         row_number() over (partition by oid, userid order by version desc) as rn, 
  from the_table
) t
where rn = 1;

使用哪一个更多的是个人喜好问题。性能方面，我预计不会有什么不同。