获取的行数集团经过数BY [英] Get the count of rows count after GROUP BY

问题描述

下面是code我在一个Ruby使用on Rails项目找到住宅具有设施与 IDS 48，49和50。它们通过连接的has_many连接。

Here is the code I use in a Ruby on Rails project to find residences which have amenities with the ids 48, 49 and 50. They are connected with a has_many through connection.

id_list = [48, 49, 50]
Residence.joins(:listed_amenities).
          where(listed_amenities: {amenity_id: id_list}).
          group('residences.id').
          having("count(listed_amenities.*) = ?", id_list.size)

产生的SQL：

The resulting SQL:

SELECT "residences".*
FROM "residences"
INNER JOIN "listed_amenities" ON "listed_amenities"."residence_id" = "residences"."id"
WHERE "listed_amenities"."amenity_id" IN (48, 49, 50)
GROUP BY residences.id
HAVING count(listed_amenities.*) = 3

我感兴趣的住宅数量而导致的此查询。有没有一种方法来添加一个计数或别的东西来让数据库做计算？我不想浪费这样做是在Ruby中的计算能力。添加 .Count之间方法是行不通的。这导致了 {528747 =＆GT; 3，529004 =＆GT; 3，529058 =＆GT; 3}

I'm interested in the number of residences that result from this query. Is there a way to add a count or something else to let the database do that calculation? I don't want to waste computing power by doing it in Ruby. Adding a .count method doesn't work. It results in {528747=>3, 529004=>3, 529058=>3}.

推荐答案

如果您的设计实施参照完整性，您不必加入到表住宅此目的可言。同样假设一个唯一或 PK 的约束（residence_id，amenity_id）（否则你需要不同的查询！）

If your design enforces referential integrity, you don't have to join to the table residences for this purpose at all. Also assuming a UNIQUE or PK constraint on (residence_id, amenity_id) (else you need different queries!)

最好的查询取决于你所需要的完全的。

The best query depends on what you need exactly.

使用窗口功能，您的可以的，即使这样做在一个单一的查询级别：

Using a window function, you can even do this in a single query level:

SELECT count(*) OVER () AS ct
FROM   listed_amenities
WHERE  amenity_id IN (48, 49, 50)
GROUP  BY residence_id
HAVING count(*) = 3
LIMIT  1;

这个窗口功能追加总数为每一行没有聚集行。考虑事件的 SELECT 查询序列：

This window function appends the total count to every row without aggregating rows. Consider the sequence of events in a SELECT query:

• <一个href="http://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied-in-php-postgresql/8242764#8242764">Best方式得到的结果计数之前限制PHP / PostgreSQL的应用于

因此​​，你可以使用一个类似的查询返回所有符合条件的标识（甚至整个行），并追加数到每一行（冗余）：

Accordingly, you could use a similar query to return all qualifying IDs (or even whole rows) and append the count to every row (redundantly):

SELECT residence_id, count(*) OVER () AS ct
FROM   listed_amenities
WHERE  amenity_id IN (48, 49, 50)
GROUP  BY residence_id
HAVING count(*) = 3;

不过还好使用子查询，这是 便宜通常要的

But better use a subquery, that's typically much cheaper:

SELECT count(*) AS ct
FROM  (
   SELECT 1
   FROM   listed_amenities
   WHERE  amenity_id IN (48, 49, 50)
   GROUP  BY residence_id 
   HAVING count(*) = 3
   ) sub;

您的可以的返回ID数组在同一时间（相对于的设置上述的），对于几乎没有任何更多的成本：

You could return an array of IDs (as opposed to the set above) at the same time, for hardly any more cost:

SELECT array_agg(residence_id ) AS ids, count(*) AS ct
FROM  (
   SELECT residence_id 
   FROM   listed_amenities
   WHERE  amenity_id IN (48, 49, 50)
   GROUP  BY residence_id
   HAVING count(*) = 3
   ) sub;

有许多其他的变体，你就必须弄清预期的结果。像这样的：

There are many other variants, you would have to clarify the expected result. Like this one:

SELECT count(*) AS ct
FROM   listed_amenities l1
JOIN   listed_amenities l2 USING (residence_id)
JOIN   listed_amenities l3 USING (residence_id)
WHERE  l1.amenity_id = 48
AND    l2.amenity_id = 49
AND    l2.amenity_id = 50;

基本上，它是关系分裂的情况。我们已经组建了技术阿森纳在这里：

Basically it's a case of relational division. We have assembled an arsenal of techniques here:

• <一个href="http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation/7774879#7774879">How过滤SQL导致了一对多，通过关系

这篇关于获取的行数集团经过数BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！