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

Question

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

9

我正在尝试加载按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...

有什么想法吗？

- atmorell

4个回答

6

您需要选择需要的特定列

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分组时，查询无法处理组中的标题，因为该组包含多个标题。

因此，异常已经提到您需要在group by中出现

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

或者用于聚合函数

Art.select("user_id, array_agg(title) as titles").group(:user_id).limit(10)

- Ilake Chang

2

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

PostGres 在这里实际上遵循了 SQL 标准，而 SQLite 和 MySQL 违背了该标准。

- John Beynon

0

看一下这个问题 - 将MySQL select转换为PostgreSQL。Postgres不允许在select语句中列出未在group by子句中的列。

- ipr101

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- rubish · Accepted Answer

该表达式生成的 SQL 查询语句无效，因为您在按 user_id 进行分组并基于此选择了许多其他字段，但未告诉数据库应如何汇总其他字段。例如，如果您的数据如下：

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

现在当您要求对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],
 #    ....
 # }

编辑:选择最新的文章，但每个用户只能有一篇文章

这里只是为了向您介绍SQL的思路（我没有在系统上安装关系型数据库管理系统，因此未进行测试）。

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

这个解决方案基于一个实际的假设，即同一用户的两个艺术品不会有相同的最高创建时间，但是如果您正在导入或通过程序批量创建艺术品，则该假设可能是错误的。如果假设不成立，则 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])