Предложение PostgreSQL - GROUP BY

Я хочу выполнить поиск по тегам, а затем перечислить все статьи с этим тегом, а также количество соответствующих тегов. Так, например, я мог бы:

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

Когда я только ставил COUNT(t.tag), запрос работает, и я получаю хорошие результаты. Но если я добавлю, например, ID моей статьи я получаю следующую ошибку:

ОШИБКА: столбец "a.title" должен появиться в предложении GROUP BY или использоваться в агрегатной функции    LINE 1: SELECT COUNT (t.tag), a.title FROM a_tags t

Как добавить указанные столбцы в этот запрос?

2 ответа

Во-первых, для уточнения, Postgres 9.1 или новее (цитирование примечаний к выпуску 9.1)...

Разрешить столбцы не GROUP BY в списке целей запроса, когда первичный ключ указан в предложении GROUP BY (Peter Eisentraut)

Подробнее в этом ответном ответе: Вернуть сгруппированный список с вхождениями с использованием Rails и PostgreSQL

Затем запросы в вопросе и в @Michael answer имеют логику назад. Мы хотим подсчитать, сколько тегов соответствует каждой статье, а не сколько статей имеют определенный тег. Поэтому нам нужно GROUP BY w_article.id, а не a_tags.id.

перечислите все статьи с этим тегом, а также количество соответствующих тегов, которые они соответствуют

В исправить:

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

Предполагая, что 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 a.article 
 LIMIT 9 -- LIMIT early - cheaper
 ) sub
JOIN w_article a USING (id); -- attached alias to article in the sub

Больше в этом близком ответе от вчера: Почему следующее соединение значительно увеличивает время запроса?

В стороне: это анти-шаблон для использования общего, не описательного id в качестве имени столбца. Назовите его article_id и т.д. В обеих таблицах. Легче присоединиться, и вам не нужно постоянно использовать псевдонимы в запросах.


Когда вы используете предложение 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

licensed under cc by-sa 3.0 with attribution.