PostgreSQL - GROUP BY子句 [英] PostgreSQL - GROUP BY clause

问题描述

我想通过标签进行搜索，然后列出具有该标签的所有文章，以及它们匹配的给定标签的数量。例如，我可能有：

pre $ Page1 - 2（有css和php标签）
Page2 - 1（has只有css标签）


查询：

 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 
限制9 
  

当我只放置 COUNT（t.tag）时，查询就可以正常工作，而且我的结果也不错。但如果我追加例如 ID 我的文章出现以下错误：

错误：列a。 title必须出现在GROUP BY子句中或用于聚合函数中
LINE 1：SELECT COUNT（t.tag），a.title FROM a_tags t

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

解决方案

首先，澄清一下，Postgres 9.1或更高版本（引用版本说明9.1 ）...

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

更多相关答案：

返回一个分组列表与事件usin g Rails和PostgreSQL

接下来，查询问题和已经把逻辑倒退了。我们希望计算每篇文章的多少个标签匹配，而不是多少篇文章具有特定的标签。所以我们需要 GROUP BY w_article.id ，而不是 a_tags.id 。

列出所有包含该标记的文章，以及它们匹配的给定标记的数量

>修复：

  SELECT COUNT（t.tag）AS ct，a。 *  - 允许的任何列... 
 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  -  ...自
的pk列分组后LIMIT 9 
  id 是的主键w_article  

/ code>。

但是，这种形式在执行相同操作时会更快：



  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 
 GRO UP BY a.article 
 LIMIT 9  -  LIMIT early  - 便宜的
）sub 
 JOIN w_article a USING（id）; - 附加的别名到sub 
中的文章 

更多与昨日紧密相关的答案： >
为什么下面的连接会显着增加查询时间吗？

顺便说一句：这是一种反模式，使用通用的非描述性的 id 作为列名称。在两个表中调用 article_id 等。更容易加入，您不必在查询中始终使用别名。

I want to search by tags, and then list all articles with that tag, and also how many of given tags they match. So for example I might have:

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

Query:

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

When I only put COUNT(t.tag) the query works, and I get okay results. But if I append e.g. ID of my article I get following error:

ERROR: column "a.title" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT COUNT(t.tag), a.title FROM a_tags t

How to add said columns to this query?

解决方案

First, to clarify, Postgres 9.1 or later (quoting release notes of 9.1) ...

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

More in this related answer:
Return a grouped list with occurrences using Rails and PostgreSQL

Next, the queries in the question and in @Michael's answer have got the logic backwards. We want to count how many tags match per article, not how many articles have a certain tag. So we need to GROUP BY w_article.id, not by a_tags.id.

list all articles with that tag, and also how many of given tags they match

To fix this:

SELECT COUNT(t.tag) AS ct, a.* -- any column from 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 grouped by pk column of a
LIMIT  9

Assuming id is the primary key of w_article.
However, this form will be faster while doing the same:

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 a.article 
   LIMIT  9      -- LIMIT early - cheaper
   ) sub
JOIN   w_article a USING (id);  -- attached alias to article in the sub

More in this closely related answer from just yesterday:
Why does the following join increase the query time significantly?

As an aside: It is an anti-pattern to use the generic, non-descriptive id as column name. Call it article_id etc. in both tables. Easier to join and you don't have to use aliases in queries all the time.

这篇关于PostgreSQL - GROUP BY子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！