在PostgreSQL中的GROUP BY和COUNT

Question

查询：

SELECT COUNT(*) as count_all, 
       posts.id as post_id 
FROM posts 
  INNER JOIN votes ON votes.post_id = posts.id 
GROUP BY posts.id;

返回 Postgresql 中的 n 条记录：

 count_all | post_id
-----------+---------
 1         | 6
 3         | 4
 3         | 5
 3         | 1
 1         | 9
 1         | 10
(6 rows)

我只想获取返回的记录数：6。

我使用了一个子查询来实现我想要的结果，但这似乎不是最优解：

SELECT COUNT(*) FROM (
    SELECT COUNT(*) as count_all, posts.id as post_id 
    FROM posts 
    INNER JOIN votes ON votes.post_id = posts.id 
    GROUP BY posts.id
) as x;

在PostgreSQL中，我如何获取此上下文中记录的数量？

- skinkelynet

---

你为什么认为这不是最优的选择？ - Samson

这似乎是一个非常普遍的操作，应该有更简单的方法。 - skinkelynet

在你的情况下，SELECT COUNT(*) from POSTS 是否有效？ - rogerdpack

Answer 1

我认为你只需要使用COUNT(DISTINCT post_id) FROM votes。

请参见http://www.postgresql.org/docs/current/static/sql-expressions.html中的“4.2.7. 聚合表达式”部分。

编辑：根据Erwin的评论，我已经更正了我的粗心错误。

- Steve Jorgensen

---

PG::Error: 错误：列“posts.id”必须出现在GROUP BY子句中或在聚合函数中使用。 - skinkelynet

@skinkelynet：那是因为答案微妙地错误了——它必须是FROM votes。我在我的答案中添加了正确的形式。 - Erwin Brandstetter

@LostCrotchet，原来在PostgreSQL中也可以这样做。你需要将字段列表放在括号中，例如... SELECT COUNT(DISTINCT (firstname, lastname)) FROM people。 - Steve Jorgensen

Answer 2

还有EXISTS函数：

SELECT count(*) AS post_ct
FROM   posts p
WHERE  EXISTS (SELECT FROM votes v WHERE v.post_id = p.id);

在Postgres中，如果你的n端有多个条目，通常比count(DISTINCT post_id)更快：

SELECT count(DISTINCT p.id) AS post_ct
FROM   posts p
JOIN   votes v ON v.post_id = p.id;

votes中每个帖子的行数越多，性能差异就越大。使用EXPLAIN ANALYZE进行测试。 count(DISTINCT post_id) 必须读取 所有 的行，对它们进行排序或哈希，然后仅考虑相同集合中的第一个。而 EXISTS 只会扫描 votes（或最好是在 post_id 上建立的索引），直到找到第一个匹配项。 如果 在表 posts 中保证存在每个 votes 中的 post_id（通过外键约束执行参照完整性），则这个 简短形式 等效于较长的形式：

SELECT count(DISTINCT post_id) AS post_ct
FROM   votes;

对于每个帖子不存在或仅有少量条目的情况，此方法可能实际上比没有EXISTS查询更快。

你提供的查询也可以以更简单的形式工作：

SELECT count(*) AS post_ct
FROM  (
    SELECT FROM posts 
    JOIN   votes ON votes.post_id = posts.id 
    GROUP  BY posts.id
    ) sub;

基准测试

为了验证我的说法，我在资源有限的测试服务器上运行了一个基准测试。所有测试都在单独的模式下进行：

测试设置

模拟典型的帖子/投票情况：

CREATE SCHEMA y;
SET search_path = y;

CREATE TABLE posts (
  id   int PRIMARY KEY
, post text
);

INSERT INTO posts
SELECT g, repeat(chr(g%100 + 32), (random()* 500)::int)  -- random text
FROM   generate_series(1,10000) g;

DELETE FROM posts WHERE random() > 0.9;  -- create ~ 10 % dead tuples

CREATE TABLE votes (
  vote_id serial PRIMARY KEY
, post_id int REFERENCES posts(id)
, up_down bool
);

INSERT INTO votes (post_id, up_down)
SELECT g.* 
FROM  (
   SELECT ((random()* 21)^3)::int + 1111 AS post_id  -- uneven distribution
        , random()::int::bool AS up_down
   FROM   generate_series(1,70000)
   ) g
JOIN   posts p ON p.id = g.post_id;

以下所有查询返回的结果相同（9107个帖子中有8093个帖子被投票）。
我在Postgres 9.1.4上对每个查询都运行了4次EXPLAIN ANALYZE，并从中选择了最好的一次，并附加了结果的总运行时间。

1. 原样。

2. 在 ...后

   ANALYZE posts;
   ANALYZE votes;
   

3. 之后，您可以通过调用submitForm()函数来提交表单。

CREATE INDEX foo on votes(post_id);

4. 在...

VACUUM FULL ANALYZE posts;
CLUSTER votes using foo;

count(*) ... WHERE EXISTS

1. 253 ms
2. 220 ms
3. 85 ms -- winner (seq scan on posts, index scan on votes, nested loop)
4. 85 ms

count(DISTINCT x) - 带连接的长格式

1. 354 ms
2. 358 ms
3. 373 ms -- (posts 上的索引扫描，votes 上的索引扫描，合并连接)
4. 330 ms

count(DISTINCT x) - 不带连接的短格式

1. 164 ms
2. 164 ms
3. 164 ms -- (总是顺序扫描)
4. 142 ms

原始查询的最佳时间:

• 353 ms

简化版本的最佳时间:

• 348 ms

@wildplasser的CTE查询使用与长格式相同的计划（在post上进行索引扫描，在votes上进行索引扫描，合并连接），并额外增加了一些开销。最佳时间:

• 366 ms

即将推出的PostgreSQL 9.2中的只索引扫描可以改善每个查询的结果，尤其是EXISTS。

与Postgres 9.5相关的更详细的基准测试（实际检索不同行，而不仅仅是计数）:

• 选择每个GROUP BY组中的第一行？

- Erwin Brandstetter

---

你说的“更可移植”，是什么意思？ - user330315

@a_horse_with_no_name: "更易移植"实际上是无意义的。我已经删除了这部分内容，感谢您的指出。我错误地认为SQLite不支持聚合函数中的DISTINCT。事实证明它确实支持，就像所有其他主要的RDBMS一样。作为补偿（也因为我想澄清一下），我用基准测试详细说明了性能问题。 - Erwin Brandstetter

如果我没看错的话，你错过了我的CTE版本。虽然它应该等同于一个子查询。 - wildplasser

@wildplasser: 抱歉，我重新创建了场景（虽然不完全相同，但从设置中可以看出非常接近），并添加了CTE版本的结果。正如预期的那样，CTE在这里并没有帮助性能。 - Erwin Brandstetter

Answer 3

使用 OVER() 和 LIMIT 1：

SELECT COUNT(1) OVER()
FROM posts 
   INNER JOIN votes ON votes.post_id = posts.id 
GROUP BY posts.id
LIMIT 1;

- Nick

---

这是适用于我的情况的解决方案，因为我想要过滤掉带有 HAVING SUM(..) > 5 子句的跨行求和值。 - stefansundin

Answer 4

WITH uniq AS (
        SELECT DISTINCT posts.id as post_id
        FROM posts
        JOIN votes ON votes.post_id = posts.id
        -- GROUP BY not needed anymore
        -- GROUP BY posts.id
        )
SELECT COUNT(*)
FROM uniq;

- wildplasser

Answer 5

对于读者来说，我喜欢原帖中的内部查询方法：

SELECT COUNT(*) FROM (
    SELECT COUNT(*) as count_all, posts.id as post_id 
    FROM posts 
    INNER JOIN votes ON votes.post_id = posts.id 
    GROUP BY posts.id
) as x;

自此之后，您也可以在其中使用HAVING：

SELECT COUNT(*) FROM (
    SELECT COUNT(*) as count_all, posts.id as post_id 
    FROM posts 
    INNER JOIN votes ON votes.post_id = posts.id 
    GROUP BY posts.id HAVING count(*) > 1
) as x;

或者等价的 CTE

with posts_coalesced as (
     SELECT COUNT(*) as count_all, posts.id as post_id 
        FROM posts 
        INNER JOIN votes ON votes.post_id = posts.id 
        GROUP BY posts.id )

select count(*) from posts_coalesced;

- rogerdpack