PostgreSQL - 必须出现在GROUP BY子句中或用于聚合函数。

Question

我在pg生产模式下遇到了这个错误，但在sqlite3开发模式下却可以正常工作。

 ActiveRecord::StatementInvalid in ManagementController#index

PG::Error: ERROR:  column "estates.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "estates".* FROM "estates"  WHERE "estates"."Mgmt" = ...
               ^
: SELECT "estates".* FROM "estates"  WHERE "estates"."Mgmt" = 'Mazzey' GROUP BY user_id

@myestate = Estate.where(:Mgmt => current_user.Company).group(:user_id).all

- Hrishikesh Sardar

---

在PostgreSQL中使用GROUP BY时，如果你没有在group by中使用某个列，那么你就不能在选择列表中使用它。不知道如何在Rails中翻译它。 - Roman Pekar

相关链接：https://dev59.com/WWIk5IYBdhLWcg3wFKlK - Craig Ringer

可能是重复的问题: https://dev59.com/WWIk5IYBdhLWcg3wFKlK - That Brazilian Guy

Answer 1

如果user_id是主键，则需要升级PostgreSQL；较新的版本将正确处理按主键分组。

如果user_id既不是'estates'关系中要查询的唯一的也不是主键，那么这个查询就没有多大意义，因为PostgreSQL无法知道在多行共享相同user_id的情况下要为每个estates列返回哪个值。您必须使用表达您想要的聚合函数，例如min、max、avg、string_agg、array_agg等，或将所需列添加到GROUP BY中。

或者，如果您真的想选择一个任意行，请重新构造查询以使用DISTINCT ON和ORDER BY。不过，我真的怀疑是否可以通过ActiveRecord来表达它。

一些数据库（包括SQLite和MySQL）将只选择任意一行。这被PostgreSQL团队认为是不正确和不安全的，因此PostgreSQL遵循SQL标准，并将这样的查询视为错误。

如果您有：

col1    col2
fred    42
bob     9
fred    44
fred    99

然后你需要做：

SELECT col1, col2 FROM mytable GROUP BY col1;

那么很明显你应该获取这一行：

bob     9

但是对于fred的结果呢？没有一个正确的答案可供选择，因此数据库将拒绝执行这些不安全的查询。如果您想要为任何col1使用最大值的col2，则可以使用max聚合函数：

SELECT col1, max(col2) AS max_col2 FROM mytable GROUP BY col1;

- Craig Ringer

---

user_id 是 User 表中的主键，但不是 Estate 表中的。 - Hrishikesh Sardar

在这种情况下，这个查询没有意义。对于任何给定的“user_id”，它返回哪个房地产？随机选择一个吗？它不会这样做。您必须使用聚合或重新考虑您正在做什么。 - Craig Ringer

我正在尝试弄清楚的是，假设我有一个具有值 {1,1,2,3,4,5,6,6} 的 @estate 变量，但现在我只想从中获取 {1,2,3,4,5,6}，我应该怎么做。在这个例子中，我给出了一个数组，但我想对哈希数组执行相同的操作。 - Hrishikesh Sardar

如何将此查询“SELECT col1，max（col2）AS max_col2 FROM mytable GROUP BY col1;”转换为Ruby？ - Hrishikesh Sardar

@HrishikeshSardar http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html 。有趣的是，在谷歌上搜索“activerecord max”的第一个结果。 - Craig Ringer

Answer 2

我最近从MySQL转移到PostgreSQL并遇到了同样的问题。仅供参考，我发现最佳方法是使用 DISTINCT ON，正如在这个SO答案中建议的那样：

Elegant PostgreSQL Group by for Ruby on Rails / ActiveRecord

这将允许您获得符合其他查询条件的所选列中每个唯一值的一条记录：

MyModel.where(:some_col => value).select("DISTINCT ON (unique_col) *") 

我更喜欢使用DISTINCT ON，因为它可以同时获取该行中所有其他列的值。而仅使用DISTINCT则只会返回特定列的值。

- Jin Hian Lee

---

“DISTINCT ON” 在适当的情况下非常有用。但是，与“DISTINCT”或“GROUP BY”相比，查询规划器对“DISTINCT ON”的处理不够智能化，因此除非您确实需要它，否则最好避免使用它。此外，“DISTINCT ON”也是非标准的。 - Craig Ringer

Answer 3

我发现在使用 Rails（我使用的是 Rails 4）时，会自动在分组查询的末尾添加 'order by id'。这通常会导致上述错误。所以请确保您在Rails查询的末尾添加自己的 .order(:group_by_column)，这样你的代码将会像这样：

@problems = Problem.select('problems.username, sum(problems.weight) as weight_sum').group('problems.username').order('problems.username')

- hec

---

我有与我的模型排序相关的相同(default_scope)。因此，我所做的是：Model.reorder(nil).group_by(...) 这样可以去除排序。 - Mosselman

这个注释…应该是答案。 - Dudo

Answer 4

@myestate1 = Estate.where(:Mgmt => current_user.Company)
@myestate = @myestate1.select("DISTINCT(user_id)")

这是我所做的。

- Hrishikesh Sardar