如何在Postgres中执行此群组ActiveRecord查询

Question

我试图在一个超过3M的表格中查找所有用户名相同的用户。我读到了一些类似这样的东西，可能会起作用。

User.find(:all, :group => [:username], :having => "count(*) > 1" )

然而，由于我正在使用Postgres，这会返回错误信息ActiveRecord::StatementInvalid: PG::Error: ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function。

我尝试着像下面这样做：

User.select('users.id, users.username').having("count(*) > 1").group('users.username')

但仍然收到相同的错误。你有什么想法我做错了什么吗？

更新：我使用User.select('users.*').group('users.id').having('count(users.username) > 1')以某种方式使其工作，但是这个查询返回的是一个看起来像空数组的东西，即使它找到了5条记录。

 GroupAggregate  (cost=9781143.40..9843673.60 rows=3126510 width=1365)
   Filter: (count(username) > 1)
   ->  Sort  (cost=9781143.40..9788959.68 rows=3126510 width=1365)
         Sort Key: id
         ->  Seq Scan on users  (cost=0.00..146751.10 rows=3126510 width=1365)
(5 rows)

 => [] 

有任何想法为什么会发生这种情况，以及如何获得那5行的数据呢？

- Martin

Answer 1

我认为，对于重复记录，你最好的解决方法是获取用户名。这可以通过以下方式实现:

User.select(:username).group(:username).having('COUNT(username) > 1')

- Mike Szyndel

---

顺便提一下，(5行)并不意味着找到了5行 - 我使用explain时只找到了一行，但结果是相同的。 - Mike Szyndel

请注意相同的内容，那么这 5 行代表的意思是什么呢？ - Martin

我从来没有真正理解过Postgres的explain结构，但我认为这是一些内部消息（模式中的行？）。 - Mike Szyndel

5行表示解释命令返回了5行，您可以在输出中看到。 - alexius

Answer 2

数据库中的"group by"将每个组合并为一个输出行。您可能想要的结果可以通过以下查询获得：

    User.where("name in (select name from users group by name having count(*)>1)").order(:name)

上面的内部查询找到了所有出现超过一次的名称。然后我们找到所有具有这些名称的行。按名称排序将使进一步处理更容易。为了加速，可以在用户表中的名称列上添加索引。

有其他特定于Postgres的方法来解决这个问题，但是上述方法适用于所有数据库。

- Deepak Kumar