sql - PostgreSQL - 获取每个 GROUP BY 组中列的最大值的行

Question

我正在处理一个 Postgres 表(称为“lives”)，该表包含带有 time_stamp、usr_id、transaction_id 和 lives_remaining 列的记录。我需要一个查询，为我提供每个 usr_id 的最近 lives_remaining 总数

1. 有多个用户(不同的 usr_id)
   
2. time_stamp 不是唯一标识符:有时用户事件(表中逐行)会使用相同的 time_stamp。
   
3. trans_id 仅在非常小的时间范围内是唯一的:随着时间的推移它会重复
   
4. remaining_lives(对于给定用户)可以随时间增加和减少

例子:

time_stamp|lives_remaining|usr_id|trans_id-----------------------------------------  07:00  |       1       |   1  |   1      09:00  |       4       |   2  |   2      10:00  |       2       |   3  |   3      10:00  |       1       |   2  |   4      11:00  |       4       |   1  |   5      11:00  |       3       |   1  |   6      13:00  |       3       |   3  |   1    

As I will need to access other columns of the row with the latest data for each given usr_id, I need a query that gives a result like this:

time_stamp|lives_remaining|usr_id|trans_id-----------------------------------------  11:00  |       3       |   1  |   6      10:00  |       1       |   2  |   4      13:00  |       3       |   3  |   1    

As mentioned, each usr_id can gain or lose lives, and sometimes these timestamped events occur so close together that they have the same timestamp! Therefore this query won't work:

SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM 
      (SELECT usr_id, max(time_stamp) AS max_timestamp 
       FROM lives GROUP BY usr_id ORDER BY usr_id) a 
JOIN lives b ON a.max_timestamp = b.time_stamp

相反，我需要同时使用 time_stamp(第一个)和 trans_id(第二个)来识别正确的行。然后我还需要将该信息从子查询传递到主查询，主查询将为相应行的其他列提供数据。这是我已经开始工作的被黑的查询:

SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM 
      (SELECT usr_id, max(time_stamp || '*' || trans_id) 
       AS max_timestamp_transid
       FROM lives GROUP BY usr_id ORDER BY usr_id) a 
JOIN lives b ON a.max_timestamp_transid = b.time_stamp || '*' || b.trans_id 
ORDER BY b.usr_id

好吧，这行得通，但我不喜欢它。它需要一个查询中的一个查询，一个自连接，在我看来，通过获取 MAX 发现的具有最大时间戳和 trans_id 的行可以简单得多。表“lives”有数千万行要解析，所以我希望这个查询尽可能快速和高效。我是 RDBM 和 Postgres 的新手，所以我知道我需要有效地使用适当的索引。我对如何优化有点迷茫。

我发现了类似的讨论here .我可以执行与 Oracle 分析函数等效的某种类型的 Postgres 吗？

任何关于访问聚合函数(如 MAX)使用的相关列信息、创建索引和创建更好的查询的建议都将不胜感激!

附言您可以使用以下内容创建我的示例案例:

create TABLE lives (time_stamp timestamp, lives_remaining integer, 
                    usr_id integer, trans_id integer);
insert into lives values ('2000-01-01 07:00', 1, 1, 1);
insert into lives values ('2000-01-01 09:00', 4, 2, 2);
insert into lives values ('2000-01-01 10:00', 2, 3, 3);
insert into lives values ('2000-01-01 10:00', 1, 2, 4);
insert into lives values ('2000-01-01 11:00', 4, 1, 5);
insert into lives values ('2000-01-01 11:00', 3, 1, 6);
insert into lives values ('2000-01-01 13:00', 3, 3, 1);

Answer 1

我会提出一个基于 DISTINCT ON 的干净版本(参见 docs ):

SELECT DISTINCT ON (usr_id)
    time_stamp,
    lives_remaining,
    usr_id,
    trans_id
FROM lives
ORDER BY usr_id, time_stamp DESC, trans_id DESC;