>有多个用户(不同的usr_id的)
> time_stamp不是唯一标识符：有时用户事件(表中的一行)将使用相同的time_stamp发生。
> trans_id仅对于非常小的时间范围是唯一的：随着时间的推移重复
> 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
由于我需要访问每个给定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(first)和trans_id(second)来标识正确的行。然后，我还需要将该信息从子查询传递到主查询，该主查询将为适当行的其他列提供数据。这是我已经工作的被黑客查询：
SELECT b.time_stamp,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.我可以执行一些类型的Postgres等价于Oracle分析函数吗？
非常感谢任何建议访问集合函数(如MAX)使用的相关列信息，创建索引和创建更好的查询。
P.S。您可以使用以下代码创建我的示例案例：
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);
insert into lives values ('2000-01-01 09:00',4,2,2);
insert into lives values ('2000-01-01 10:00',3,3);
insert into lives values ('2000-01-01 10:00',4);
insert into lives values ('2000-01-01 11:00',5);
insert into lives values ('2000-01-01 11:00',6);
insert into lives values ('2000-01-01 13:00',1);
按照查询成本，我指的是Postgres的基于成本的优化器的成本估计(Postgres的默认xxx_cost值)，这是一个需要的I / O和cpu资源的权重函数估计;您可以通过启动PgAdminIII并对“查询/解释选项”设置为“分析”的查询运行“查询/说明(F7)”，
> Quassnoy的查询具有745k(！)的成本估计，并在1.3秒内完成(给定复合索引(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))相比的成本估计和完成时间;它有扫描生命表只有一次的优点，如果你暂时增加(如果需要)work_mem以适应内存中的排序，它将是迄今为止所有查询中最快的。
上面的所有时间包括检索完整的10k行结果集。
您的目标是最小的成本估算和最少的查询执行时间，并强调估计成本。查询执行可以显着依赖于运行时条件(例如，相关行是否已经完全缓存在存储器中)，而成本估计不是。另一方面，请记住，成本估计正是这个，估计。
当在无负载的专用数据库上运行时(例如，在开发PC上使用pgAdminIII)，获得最佳查询执行时间。查询时间将根据实际机器负载/数据访问扩展而变化。当一个查询出现比另一个查询稍快(<20％)但具有高得多的成本时，通常选择具有较高执行时间但较低成本的查询通常是更明智的。 当您期望在运行查询时，您的生产机器上的内存没有竞争(例如，RDBMS缓存和文件系统缓存不会被并发查询和/或文件系统活动损坏)，那么您获得的查询时间在独立(例如pgAdminIII在开发PC上)模式将是代表性的。如果在生产系统上存在争用，查询时间将与估计的成本比例成比例地降低，因为具有较低成本的查询不依赖于高速缓存，而具有较高成本的查询将重复访问相同的数据(触发在没有稳定高速缓存的情况下的附加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生活一次。
查询#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 );
