PostgreSQL-获取具有列的最大值的行

Question

我正在处理一个Postgres表（称为“lives”），该表包含带有time_stamp，usr_id，transaction_id和live_remaining列的记录。我需要一个查询，该查询将为我提供每个usr_id的最新live_remaining总数

1. 有多个用户（与usr_id不同）
2. time_stamp不是唯一的标识符：有时，用户事件（表中的每一行）将使用相同的time_stamp发生。
3. trans_id仅在很小的时间范围内是唯一的：随着时间的流逝，它会重复
4. （对于给定的用户）剩余生命可以随着时间的过去而增加和减少
   例子：
   

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    

因为我将需要使用给定usr_id的每个给定数据访问该行的其他列，因此我需要一个查询，其给出的结果如下：

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

如前所述，每个usr_id可能会失去生命，有时，这些带有时间戳记的事件发生得非常紧密，以至于它们具有相同的时间戳！因此，此查询将不起作用：

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的新手，所以我知道我需要有效地使用适当的索引。我对如何优化有些迷茫。

我在这里找到了类似的讨论。我可以执行某种与Oracle分析功能等效的Postgres吗？

任何有关访问由聚合函数（如MAX）使用的相关列信息，创建索引以及创建更好的查询的建议都将不胜感激！

PS您可以使用以下内容创建我的示例案例：

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

在具有158k个伪随机行的表上（usr_id在0和10ktrans_id之间均匀分布，在0和30之间均匀分布），

下面，通过查询成本，我指的是基于Postgres的基于成本的优化器的成本估算（带有Postgres的默认xxx_cost值），它是对所需I / O和CPU资源的加权函数估算；您可以通过启动PgAdminIII并在查询上运行“查询/解释（F7）”并将“查询/解释选项”设置为“分析”来获取此信息。

Quassnoy的查询有745k成本估算（！），并完成了130秒（给出一个复合索引（usr_id，trans_id，time_stamp））
Bill的查询的费用估算为93k，并在2.9秒内完成（鉴于（usr_id，trans_id）上的复合索引）
查询＃1的下方具有16K成本估算，和在800ms的结束（在给定的化合物指数（usr_id，trans_id，time_stamp））
查询＃2的下方具有14K成本估算，和在800ms的结束（在给定的化合物功能指数（usr_id，EXTRACT(EPOCH FROM time_stamp)，trans_id））
这是Postgres特有的
下面的查询＃3（Postgres的8.4+）具有成本估算和完成时间相当（或更好）的查询＃2（在给定（一个复合索引usr_id，time_stamp，trans_id））; 它具有lives只扫描一次表的优点，并且，如果您临时增加（如果需要）work_mem以容纳内存中的排序，那么它将是所有查询中最快的。
上面所有时间都包括检索全部1万行结果集。

您的目标是最小的成本估算和最短的查询执行时间，重点是估算成本。查询执行可能在很大程度上取决于运行时条件（例如，相关行是否已经完全缓存在内存中），而成本估算却没有。另一方面，请记住，成本估算正是估算值。

当在没有负载的专用数据库上运行时（例如，在开发PC上使用pgAdminIII），可以获得最佳的查询执行时间。查询时间将根据实际的机器负载/数据访问范围而在生产环境中有所不同。当一个查询稍快出现（<20％）比其它但是具有多更高的成本，这将通常是明智的选择具有较高的执行时间，但成本更低。

如果您希望在运行查询时生产机器上的内存没有竞争（例如，并发查询和/或文件系统活动不会破坏RDBMS缓存和文件系统缓存），那么您获得的查询时间在独立模式下（例如，开发PC上的pgAdminIII）将具有代表性。如果生产系统存在争用，查询时间将与估计的成本比率成比例地降低，因为成本较低的查询对缓存的依赖程度不高，而成本较高的查询将反复访问相同的数据（触发在没有稳定缓存的情况下添加其他I / O），例如：

              cost | time (dedicated machine) |     time (under load) |
-------------------+--------------------------+-----------------------+
some query A:   5k | (all data cached)  900ms | (less i/o)     1000ms |
some query B:  50k | (all data cached)  900ms | (lots of i/o) 10000ms |

ANALYZE lives创建必要的索引后，请不要忘记运行一次。

查询＃1

-- incrementally narrow down the result set via inner joins
--  the CBO may elect to perform one full index scan combined
--  with cascading index lookups, or as hash aggregates terminated
--  by one nested index lookup into lives - on my machine
--  the latter query plan was selected given my memory settings and
--  histogram
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
    SELECT
      usr_id,
      MAX(time_stamp) AS time_stamp_max
     FROM
      lives
     GROUP BY
      usr_id
  ) AS l2
 ON
  l1.usr_id     = l2.usr_id AND
  l1.time_stamp = l2.time_stamp_max
 INNER JOIN (
    SELECT
      usr_id,
      time_stamp,
      MAX(trans_id) AS trans_max
     FROM
      lives
     GROUP BY
      usr_id, time_stamp
  ) AS l3
 ON
  l1.usr_id     = l3.usr_id AND
  l1.time_stamp = l3.time_stamp AND
  l1.trans_id   = l3.trans_max

查询＃2

-- cheat to obtain a max of the (time_stamp, trans_id) tuple in one pass
-- this results in a single table scan and one nested index lookup into lives,
--  by far the least I/O intensive operation even in case of great scarcity
--  of memory (least reliant on cache for the best performance)
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
   SELECT
     usr_id,
     MAX(ARRAY[EXTRACT(EPOCH FROM time_stamp),trans_id])
       AS compound_time_stamp
    FROM
     lives
    GROUP BY
     usr_id
  ) AS l2
ON
  l1.usr_id = l2.usr_id AND
  EXTRACT(EPOCH FROM l1.time_stamp) = l2.compound_time_stamp[1] AND
  l1.trans_id = l2.compound_time_stamp[2]

2013/01/29更新

最后，从8.4版开始，Postgres支持Window Function，这意味着您可以编写简单而有效的内容，例如：

查询3

-- use Window Functions -- performs a SINGLE scan of the table SELECT DISTINCT ON (usr_id) last_value(time_stamp) OVER wnd, last_value(lives_remaining) OVER wnd, usr_id, last_value(trans_id) OVER wnd FROM lives WINDOW wnd AS ( PARTITION BY usr_id ORDER BY time_stamp, trans_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING );