PostgreSQL - GROUP BY子句

Question

我希望能够通过标签搜索，并列出所有包含该标签的文章，同时显示它们与给定标签的匹配数。例如，我可能有以下内容：

 Page1 - 2 (has css and php tag)
 Page2 - 1 (has only css tag)

查询：

SELECT COUNT(t.tag)
FROM a_tags t
JOIN w_articles2tag a2t ON a2t.tag = t.id 
JOIN w_article a ON a.id = a2t.article 
WHERE t.tag = 'css' OR t.tag = 'php'
GROUP BY t.tag
LIMIT 9

当我只输入COUNT(t.tag)时，查询可以正常工作，并且我可以得到正确的结果。但是如果我添加例如我的文章的ID，我会收到以下错误信息：

错误：列“a.title”必须出现在GROUP BY子句中或用于聚合函数 LINE 1: SELECT COUNT(t.tag), a.title FROM a_tags t

如何将这些列添加到此查询中？

- Adrian

Answer 1

当使用“GROUP BY”子句时，需要将所有未进行分组的列都包含在聚合函数中。尝试将标题添加到GROUP BY列表中，或选择“min(a.title)”。

SELECT COUNT(t.tag), a.title FROM a_tags t
JOIN w_articles2tag a2t ON a2t.tag = t.id 
JOIN w_article a ON a.id = a2t.article 
WHERE t.tag = 'css' OR t.tag = 'php' GROUP BY t.tag, a.title LIMIT 9

- Michael Krebs

---

我尝试添加MAX(a.title)，然后在最后按tags_count DESC排序解决了这个问题，但我不确定为什么你需要在你的情况下加上eg. MIN(a.title)...因为我是从MySQL转移过来的，我认为那里不是这种情况，所以有点奇怪 :O - Adrian

MySQL的group by语句不符合标准且模糊不清。 - Falmarri

具体来说，MySQL在select列表中不要求对未出现在group by中的列使用聚合函数（如MIN或MAX）。这种行为可能会导致某些情况下出现意外结果，因此PostgreSQL确实有这个要求，你已经注意到这与MySQL不同。 - Michael Krebs

@Adrian：你可能想要阅读这篇文章：http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html 和这篇文章：http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/，以了解MySQL“宽松”（不准确地说是：错误的）`group by`处理可能存在的陷阱。 - user330315

Answer 2

Postgres 9.1或更高版本，引用9.1版本的发布说明 ...

当在GROUP BY子句中指定主键时，允许在查询目标列表中使用非GROUP BY列（Peter Eisentraut）。

由于主键的存在，SQL标准允许这种行为，结果是明确的。

相关：

• 使用Rails和PostgreSQL返回带有出现次数的分组列表

问题和@Michael's答案中的查询逻辑相反。我们想要计算每篇文章匹配的标签数量，而不是具有某个标签的文章数量。因此，我们需要通过GROUP BY w_article.id而不是a_tags.id进行分组。

列出所有带有该标签的文章，并显示它们匹配给定标签的数量。

要修复此问题：

SELECT count(t.tag) AS ct, a.*  -- any column from table a allowed ...
FROM   a_tags         t
JOIN   w_articles2tag a2t ON a2t.tag = t.id
JOIN   w_article      a   ON a.id = a2t.article
WHERE  t.tag IN ('css', 'php')
GROUP  BY a.id                  -- ... since PK is in GROUP BY
LIMIT  9;

假设id是w_article的主键。
然而，这种形式在执行相同操作时会更快速：

SELECT a.*, ct
FROM  (
   SELECT a2t.article AS id, count(*) AS ct
   FROM   a_tags         t
   JOIN   w_articles2tag a2t ON a2t.tag = t.id 
   GROUP  BY 1
   LIMIT  9      -- LIMIT early - cheaper
   ) sub
JOIN   w_article a USING (id);  -- attached alias to article in the sub

昨天的回答与此密切相关：

• 为什么以下连接会显著增加查询时间？

- Erwin Brandstetter

---

Erwin，我邀请你在提到其他贡献者时更加鼓励。问题和答案在原则上并不是错误的，它们仅仅基于不同的原则而正确。所问的问题是语法问题，而不是语义问题。Adrian提供了支持语法问题的代码。答案提供了与问题同样详细的信息，并且仅限于所问的问题。作为回复，Adrian评论道：“问题解决了。”你选择用更多关于GROUP BY的细节来回答，并改进了语义。我完全赞成这一点。另一个原则也可能很有用。 - Michael Krebs

@MichaelKrebs：没有任何冒犯或人身攻击的意思，也不是有意为之。如果我有得罪你，对此我很抱歉。我已经去掉了加粗和一些噪音。只是指出了问题，这正是我们在SO上所做的。你的回答虽然只对了一半，但还是得到了两个赞，算不错了。欢迎来到Stackoverflow。 - Erwin Brandstetter

你好，Erwin。抱歉让这个古老（优秀）的答案复活了。我在使用此功能时遇到了问题，想知道你是否恰好知道答案。最近我不得不重新安装postgres，并发现一个使用这个"分组功能"的SQL查询在我的旧postgres中可以工作，但在我的新postgres中不能工作。它们都是相同的postgres版本！你知道这个“功能”是否可能是某种必须启用的postgres服务器ini配置吗？谢谢。 - d-ph

没错，这是一个经典的“stackoverflow”时刻：在提问后5分钟就找到了答案（尽管之前已经花费了一个小时来解决这个问题）。解决方案：我的数据库转储导入某种方式没有在相关的数据库表上重新创建主键索引，因此这个“分组功能”默默地没有“启动”。我需要找出为什么我的数据库导出没有重新实例化主键，但除此之外：创建缺失的主键解决了问题。谢谢。 - d-ph