PostgreSQL-获取每个 GROUPBY 组中列的 Max 值的行

我正在处理一个 Postgres 表(称为“ live”) ，其中包含的记录的列包括 time _ 戳、 usr _ id、 transaction _ id 和 live _ rest。我需要一个查询，它将为我提供每个 usr _ id 的最新剩余生命总数

  1. 有多个用户(不同的 usr _ id)
  2. Time _ 戳不是一个唯一标识符，有时用户事件(表中的一行一行)会与 time _ 戳同时发生。
  3. Trans _ id 仅在非常小的时间范围内是唯一的: 随着时间的推移它会重复
  4. Rest _ life (对于给定的用户)可以随着时间的推移而增加或减少

例如:

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 _ 戳(first)和 trans _ id (second)来标识正确的行。然后，我还需要将该信息从子查询传递到主查询，主查询将为适当行的其他列提供数据。这就是我要解决的问题:

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

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

我发现了一个类似的讨论 给你。我可以执行一些相当于 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);
151522 次浏览
小开 
SELECT  l.*
FROM    (
SELECT DISTINCT usr_id
FROM   lives
) lo, lives l
WHERE   l.ctid = (
SELECT ctid
FROM   lives li
WHERE  li.usr_id = lo.usr_id
ORDER BY
time_stamp DESC, trans_id DESC
LIMIT 1
)

在 (usr_id, time_stamp, trans_id)上创建索引将极大地改进此查询。

你应该总是，总是有一些 PRIMARY KEY在你的表。

小开

下面是另一种方法，它碰巧不使用相关的子查询或 GROUPBY。我不是 PostgreSQL 性能调优的专家，所以我建议你同时尝试这个和其他人提供的解决方案，看看哪个更适合你。

SELECT l1.*
FROM lives l1 LEFT OUTER JOIN lives l2
ON (l1.usr_id = l2.usr_id AND (l1.time_stamp < l2.time_stamp
OR (l1.time_stamp = l2.time_stamp AND l1.trans_id < l2.trans_id)))
WHERE l2.usr_id IS NULL
ORDER BY l1.usr_id;

我假设 trans_id至少在任何给定的 time_stamp值上是唯一的。

小开
最佳答案

对于具有158k 伪随机行的表(usr _ id 均匀分布在0到10k 之间，trans_id均匀分布在0到30之间) ,

根据查询成本，下面我指的是 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 专用的
  • 在 (Postgres 8.4 +)下面的查询 # 3的成本估算和完成时间与查询 # 2相当(或者更好)(给定了(usr_id，time_stamp，trans_id)的复合索引) ; 它的优点是只扫描 lives表一次，如果你暂时增加(如果需要的话) 工作以适应内存中的排序，它将是所有查询中速度最快的。

以上所有时间都包括检索完整的10k 行结果集。

您的目标是最小成本估算 还有最小查询执行时间，重点是估算成本。查询执行在很大程度上依赖于运行时条件(例如，相关行是否已经完全缓存在内存中) ，而成本估计则不然。另一方面，请记住，成本估计正是这样，一个估计。

最佳查询执行时间是在专用数据库上运行而不加载时获得的(例如，在开发 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。

问题一

-- 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 支持 窗口功能，这意味着你可以写一些简单有效的东西，比如:

问题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
);
小开

我认为这里有一个主要问题: 没有单调增加的“计数器”来保证某一行比另一行发生得晚。举个例子:

timestamp   lives_remaining   user_id   trans_id
10:00       4                 3         5
10:00       5                 3         6
10:00       3                 3         1
10:00       2                 3         2

您无法从这些数据中确定哪个是最新的条目。是第二个还是最后一个？没有 sort 或 max ()函数可以应用于这些数据中的任何一个来给出正确答案。

提高时间戳的分辨率将是一个巨大的帮助。由于数据库引擎序列化请求，因此可以通过足够的分辨率保证不会有两个时间戳是相同的。

或者，使用 trans _ id，它不会滚动很长很长时间。有一个翻转的 trans _ id 意味着你不能告诉(对于相同的时间戳) trans _ id 6是否比 trans _ id 1更新，除非你做一些复杂的数学运算。

小开

我喜欢你提到的另一页 Mike Woodhouse 的回答的风格。如果最大化的只是一列，那么子查询可以只使用 MAX(some_col)和 GROUP BY其他列，但是在你的情况下，你有两部分的数量需要最大化，你仍然可以使用 ORDER BY和 LIMIT 1来代替(就像 Quassnoi 做的那样) :

SELECT *
FROM lives outer
WHERE (usr_id, time_stamp, trans_id) IN (
SELECT usr_id, time_stamp, trans_id
FROM lives sq
WHERE sq.usr_id = outer.usr_id
ORDER BY trans_id, time_stamp
LIMIT 1
)

我发现使用行构造函数语法 WHERE (a, b, c) IN (subquery)非常好，因为它减少了所需的冗长信息。

小开

实际上这个问题有一个蹩脚的解决方案。假设您希望选择区域中每个森林中最大的树。

SELECT (array_agg(tree.id ORDER BY tree_size.size)))[1]
FROM tree JOIN forest ON (tree.forest = forest.id)
GROUP BY forest.id

当您按森林对树木进行分组时，会出现一个未排序的树木列表，您需要找到最大的一个。首先，您应该按照行的大小对它们进行排序，并选择列表中的第一个行。它可能看起来效率低下，但是如果您有数百万行，它将比包含 JOIN和 WHERE条件的解决方案快得多。

顺便说一句，请注意 array_agg的 ORDER_BY是在 Postgreql 9.0中引入的

小开

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

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;
小开

Postpressql 9.5中有一个名为 DISTINCT ON 的新选项

SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;

它消除了重复的行，只留下定义为 ORDERBY 子句的第一行。

看看官方的 文件

小开

可以使用窗口函数来完成

SELECT t.*
FROM
(SELECT
*,
ROW_NUMBER() OVER(PARTITION BY usr_id ORDER BY time_stamp DESC) as r
FROM lives) as t
WHERE t.r = 1

提交答案