Rails 3.1 与 PostgreSQL：GROUP BY 必须在聚合函数中使用

Question

我正在尝试加载按 user_id 分组并按created_at 排序的最新 10 个艺术。这适用于 SqlLite 和 MySQL，但在我的新 PostgreSQL 数据库上出现错误。

Art.all(:order => "created_at desc", :limit => 10, :group => "user_id")

ActiveRecord 错误：

Art Load (18.4ms)  SELECT "arts".* FROM "arts" GROUP BY user_id ORDER BY created_at desc LIMIT 10
ActiveRecord::StatementInvalid: PGError: ERROR:  column "arts.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT  "arts".* FROM "arts"  GROUP BY user_id ORDER BY crea...

有什么想法吗？

Answer 1

表达式生成的 sql 不是有效的查询，您按 user_id 进行分组并基于此选择许多其他字段，但不告诉数据库应如何聚合其他字段。例如，如果您的数据如下所示：

a  | b
---|---
1  | 1
1  | 2
2  | 3

现在，当您要求 db 按 a 分组并返回 b 时，它不知道如何聚合值 1,2 。你需要告诉它是否需要选择最小值、最大值、平均值、总和或其他。就在我写答案时，有两个答案可以更好地解释这一切。

但在您的用例中，我认为您不希望在数据库级别进行分组。由于只有 10 种艺术，您可以将它们分组到您的申请中。不过，不要对数千种艺术使用此方法：

 arts = Art.all(:order => "created_at desc", :limit => 10)
 grouped_arts = arts.group_by {|art| art.user_id}
 # now you have a hash with following structure in grouped_arts
 # { 
 #    user_id1 => [art1, art4],
 #    user_id2 => [art3],
 #    user_id3 => [art5],
 #    ....
 # }

编辑：选择latest_arts，但每个用户只能使用一种艺术

只是为了让您了解sql的想法（尚未测试它，因为我没有RDBMS安装在我的系统上）

SELECT arts.* FROM arts
WHERE (arts.user_id, arts.created_at) IN 
  (SELECT user_id, MAX(created_at) FROM arts
     GROUP BY user_id
     ORDER BY MAX(created_at) DESC
     LIMIT 10)
ORDER BY created_at DESC
LIMIT 10

此解决方案基于实际假设，即同一用户的两种艺术不能具有相同的最高created_at，但如果您导入或以编程方式创建大量艺术，则很可能是错误的。如果假设不成立，sql 可能会变得更加不自然。

编辑：尝试将查询更改为 Arel：

Art.where("(arts.user_id, arts.created_at) IN 
             (SELECT user_id, MAX(created_at) FROM arts
                GROUP BY user_id
                ORDER BY MAX(created_at) DESC
                LIMIT 10)").
    order("created_at DESC").
    page(params[:page]).
    per(params[:per])

Answer 2

您需要选择您需要的特定列

Art.select(:user_id).group(:user_id).limit(10)

当您尝试在查询中选择标题时，它会引发错误，例如

Art.select(:user_id, :title).group(:user_id).limit(10)

列“arts.title”必须出现在 GROUP BY 子句中或在聚合函数中使用

这是因为当您尝试分组时user_id，查询不知道如何处理组中的标题，因为该组包含多个标题。

所以例外已经提到你需要出现在分组依据中

Art.select(:user_id, :title).group(:user_id, :title).limit(10)

或在聚合函数中使用

Art.select("user_id, array_agg(title) 作为标题").group(:user_id).limit(10)

Answer 3

看看这篇文章 SQLite 到 Postgres (Heroku) GROUP BY

PostGres 是实际上这里遵循 SQL 标准，而 sqlite 和 mysql 则违反了标准。

Answer 4

看看这个问题 - Converting MySQL select to PostgreSQL。 Postgres 不允许在 select 语句中列出不在 group by 子句中的列。