Выбор первичного из строки сопоставления SQL

Question

У меня есть таблица, которая ссылается на кучу статей, таблица содержит теги для этих статей. Как это:

tag text
article_id bigint

Я хочу выбрать все article_ids с набором тегов, скажем, tag1, tag2, tag3, но к статье также могут быть прикреплены tag4, tag5.

Я знаю, что это будет работать:

SELECT article_id 
FROM tag WHERE tag='tag1' 
INTERSECT 
SELECT article_id 
FROM tag 
WHERE tag='tag2' 
INTERSECT 
SELECT article_id 
FROM tag 
WHERE tag='tag3'

И так будет это:

SELECT article_id 
FROM tag 
WHERE tag IN ('tag1','tag2','tag3') 
GROUP BY article_id 
HAVING count(*) = 3

Но я не уверен, что это самый эффективный способ сделать это. Я также играл с нижеуказанным, но не могу заставить его работать в настоящее время.

SELECT array_agg(tag) as arr,
       article_id 
FROM tag 
GROUP BY article_id 
HAVING arr = {tag1,tag2,tag3}

Это просто казалось общей проблемой, с которой столкнутся другие, мне было интересно, является ли INTERSECT наиболее эффективным запросом в этом случае. Это для PostgreSQL.

Answer 1

Я полагаю, вы хотите больше столбцов из article чем только article_id, Хотя стиль запроса не сильно меняется.

То, с чем вы имеете дело, называется реляционным делением. И есть много способов снять шкуру с этой кошки, что можно увидеть в этом связанном вопросе, включая рекомендации по индексированию и тестам производительности.

Мои личные фавориты (и, скорее всего, одни из самых быстрых):

SELECT a.*
FROM   article a
JOIN   tag x USING (article_id)
JOIN   tag y USING (article_id)
JOIN   tag z USING (article_id)
WHERE  x.tag = 'tag1'
AND    y.tag = 'tag1'
AND    z.tag = 'tag3';

Или же:

SELECT a.*
FROM   article a
WHERE  EXISTS (
   SELECT *
   FROM   tag x
   JOIN   tag y USING (article_id)
   JOIN   tag z USING (article_id)
   WHERE  x.article_id = a.article_id
   AND    x.tag = 'tag1'
   AND    y.tag = 'tag2'
   AND    z.tag = 'tag3'
   );

@ Давид уже объяснил, почему ваша третья версия не может функционировать. Но вы никогда не должны использовать его, так как он не может использовать индексы и будет работать на порядок медленнее, чем любой другой метод здесь.

Answer 2

Лично мне нравится второй вариант. Но вы должны использовать инструменты PostgreSQL вместе с некоторыми тестовыми запросами, чтобы увидеть, какой из них наиболее эффективен.

Причина, по которой третий не работает так, как написано, состоит в том, что вам нужно указать порядок сортировки для array_agg(): как заставить array_agg () работать как group_concat() из mySQL

Answer 3

В вашем последнем варианте вас не интересует равенство массивов. Вы заинтересованы в содержании массива, HAVINGarr содержать все три тега.

И массив1 содержит массив2 array1 @> array2 в PostgreSQL.

Тем не менее, я ожидаю, что ответ Эрвина выиграет в производительности благодаря индексации.