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

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

tag text
article_id bigint

Я хочу выбрать все article_id с набором тегов, скажем, 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.


postgresql sql relational-division sql-match-all
person ehiller    schedule 11.03.2012    source источник
comment
Если вы хотите найти наиболее эффективный запрос, просто запустите EXPLAIN ANALZYE   -  person a_horse_with_no_name    schedule 12.03.2012

Ответы (3)


arrow_upward
1
arrow_downward

Я предполагаю, что вам нужно больше столбцов из 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';

Or:

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'
   );

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

person Erwin Brandstetter    schedule 12.03.2012

arrow_upward
0
arrow_downward

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

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

person David Faber    schedule 11.03.2012
comment
Когда он переписан, он работает как SELECT article_id FROM tag GROUP BY article_id HAVING array_agg(tag::text ORDER BY tag)=ARRAY['tag1','tag2','tag3']; но если у статьи есть тег 4, то он ее не найдет. Есть ли способ, которым мне не хватает, чтобы убедиться, что все элементы находятся в массиве, но массивы не совпадают идеально? - person ehiller; 12.03.2012

arrow_upward
0
arrow_downward

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

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

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

person Michał Politowski    schedule 13.03.2012