ruby-on-rails - 获取按唯一字段值分组的最新行

Question

我有一个包含 author_id 字段的 Books 表。

我想获取一个 Books 数组，其中每个作者只包含一本书。具有最新 updated_at 字段的那个。

像 Postgres 上的 Books.all.group('author_id') 这样直接的方法的问题是它需要在其 GROUP BY block 中请求所有字段。 (参见 https://stackoverflow.com/a/6106195/1245302)

但是我需要为每个作者获取所有 Book 对象，最近的一个，忽略所有其他字段。在我看来，DBMS 有足够的数据来准确找到我想要的行，至少我可以在 GROUP BY block 中没有任何其他字段的情况下自己做到这一点。 :)

是否有任何简单的 Rails 3 + Postgres(版本 < 9)或 SQL 实现独立的方式来获得它？

更新Postgres 的不错的解决方案:

books.unscoped.select('DISTINCT ON(author_id) *').order('author_id').order('updated_at DESC') 

但是!仍然存在问题——结果首先按 author_id 排序，但我需要在相同的 author_id-s 中按 updated_at 排序(找到，比如前 10 位最近的图书作者)。

并且 Postgres 不允许您更改 DISTINCT 查询中的 ORDER BY 参数的顺序 :(

Answer 1

我不了解 Rails，但希望向您展示您想要的 SQL 将帮助您找到生成正确 SQL 的方法。

SELECT DISTINCT ON (author_id) *
  FROM Books
  ORDER BY author_id, updated_at DESC;

DISTINCT ON (author_id) 部分不应与结果列列表的一部分混淆——它只是表示每个 author_id 各占一行。 DISTINCT ON 子句中的列表必须是此类查询中 ORDER BY 子句的前导部分，保留的行是根据以下条件首先排序的行ORDER BY 子句的 rest。

对于大量行，这种编写查询的方式通常比任何基于 GROUP BY 或窗口函数的解决方案快得多，通常快一个数量级或更多。不过，它是一个 PostgreSQL 扩展；所以它不应该用在可移植的代码中。

如果您想在另一个查询中使用此结果集(例如，查找最近更新的 10 位作者)，有两种方法可以实现。您可以使用子查询，如下所示:

SELECT *
  FROM (SELECT DISTINCT ON (author_id) *
          FROM Books
          ORDER BY author_id, updated_at DESC) w
  ORDER BY updated_at DESC
  LIMIT 10;

您还可以使用 CTE，如下所示:

WITH w AS (
  SELECT DISTINCT ON (author_id) *
    FROM Books
    ORDER BY author_id, updated_at DESC)
SELECT * FROM w
  ORDER BY updated_at DESC
  LIMIT 10;

关于 CTE 的通常建议在这里:仅在没有其他方法编写查询或需要通过引入优化障碍来强制规划器的情况下使用它们。这些计划非常相似，但通过 CTE 扫描传递中间结果会增加一些开销。在我的小型测试集上，CTE 表格慢了 17%。