sql - 获取具有 max(timestamp) 的行

Question

我需要选择最近评论的文章，​​每篇文章的最后评论，即包含 max(c.created) 的行的其他列:

SELECT a.id, a.title, a.text, max(c.created) AS cid, c.text?
FROM subscriptions s
JOIN articles a ON a.id=s.article_id
JOIN comments c ON c.article_id=a.id
WHERE s.user_id=%d
GROUP BY a.id, a.title, a.text
ORDER BY max(c.created) DESC LIMIT 10;

Postgres 告诉我必须将 c.text 放入 GROUP BY 中。显然，我不想这样做。最小/最大也不适合。我不知道如何选择它。

请指教。

Answer 1

在 PostgreSQL 中，DISTINCT ON可能是这种查询的最佳解决方案:

SELECT DISTINCT ON (a.id)
       a.id, a.title, a.text, c.created, c.text
FROM   subscriptions s
JOIN   articles      a ON a.id = s.article_id
JOIN   comments      c ON c.article_id = a.id
WHERE  s.user_id = %d
ORDER  BY a.id, c.created DESC

这会检索带有最新评论和相关附加列的文章。
此 closely related answer 中的说明、链接和基准.

要获取最新的 10 个，请将其包装在子查询中:

SELECT *
FROM  (
    SELECT DISTINCT ON (a.id)
             a.id, a.title, a.text, c.created, c.text
    FROM   subscriptions s
    JOIN   articles      a ON a.id = s.article_id
    JOIN   comments      c ON c.article_id = a.id
    WHERE  s.user_id = 12
    ORDER  BY a.id, c.created DESC
    ) x
ORDER  BY created DESC
LIMIT  10;

或者，您可以使用 window functions结合标准 DISTINCT:

SELECT DISTINCT
       a.id, a.title, a.text, c.created, c.text
      ,first_value(c.created) OVER w AS c_created
      ,first_value(c.text)    OVER w AS c_text
FROM   subscriptions s
JOIN   articles      a ON a.id = s.article_id
JOIN   comments      c ON c.article_id = a.id
WHERE  s.user_id = 12
WINDOW w AS (PARTITION BY c.article_id ORDER BY c.created DESC)
ORDER  BY c_created DESC
LIMIT  10;

这是可行的，因为 DISTINCT(与聚合函数不同)在 窗口函数之后应用。
你必须测试哪个更快。我猜最后一个比较慢。