Postgres按组分组聚合函数

Question

我有一个消息表，看起来像这样：

+------------+-------------+----------+
| sender_id  |  created_at | message  |
+------------+-------------+----------+
|      1     | 2010-06-14  | the msg  |
|      1     | 2010-06-15  | the msg  |
|      2     | 2010-06-16  | the msg  |
|      3     | 2010-06-14  | the msg  |
+------------+-------------+----------|

我希望选择每个发送者的最新消息。

这似乎需要按sender_id分组并按created_at排序，但我无法选取最新的消息。

我正在使用postgres，因此如果我想按该字段排序，则需要在SELECT语句中对created_at字段进行聚合函数处理，因此我正在考虑像这样进行初始测试。

SELECT messages.sender_id, MAX(messages.created_at) as the_date 
FROM messages 
GROUP BY sender_id 
ORDER BY the_date DESC 
LIMIT 10;

这似乎可以工作，但当我想选择'message'时，我不知道要使用哪个聚合函数。基本上，我只想获得与最大created_at对应的消息。

有没有什么方法可以获取到它，或者我是错误的方式接近它？

- johnnymire

---

你使用的是哪个版本？ - Martin Smith

Answer 1

This:

SELECT  *
FROM    (
        SELECT  DISTINCT ON (sender_id) *
        FROM    messages 
        ORDER BY
                sender_id, created_at DESC 
        ) q
ORDER BY
        created_at DESC
LIMIT 5

或者这样：

SELECT  (mi).*
FROM    (
        SELECT  (
                SELECT  mi
                FROM    messages mi
                WHERE   mi.sender_id = m.sender_id
                ORDER BY
                        created_at DESC
                LIMIT 1
                ) AS mi
        FROM    messages m
        GROUP BY
                sender_id
        ) q
ORDER BY
        (mi).created_at  DESC
LIMIT 5

为了让它能够快速运行，请在 (sender_id, created_at) 上创建索引。

你可能会对这篇文章感兴趣：

• PostgreSQL：选择保持分组最大值的记录

- Quassnoi

---

这个会给出不同的结果，排序顺序也不同。 - Frank Heikens

@Frank：你可以将这个作为子查询，并使用上层查询重新排序结果。 - Kevin

@Frank：原始问题没有提到sender_id的顺序，但这里是已更正顺序的查询。 - Quassnoi

关于这篇文章，我不能使用任何8.4方法，比如窗口函数，因为我使用的是8.3版本。你提供的第二个查询会出现错误（在“mi”附近有语法错误）。我需要按日期排序，所以也不能使用DISTINCT ON的想法。 - johnnymire

太好了，完成了，谢谢！我会尝试使用索引来加快速度。 - johnnymire

Answer 2

使用相关子查询：

select * from messages m1 
where m1.created_at = (
    select max(m2.create_at) 
    from messages m2 
    where m1.sender_id = m2.sender_id
);

子查询会在上层查询处理每一行时重新计算。

- Kevin

Answer 3

使用 distinct on：

    SELECT DISTINCT ON (sender_id) 
           sender_id,created_at,message
      FROM messages
  ORDER BY sender_id,created_at DESC

- Gavin