Início / dba / Perguntas / 168460
Accepted
eComEvo
Asked: 2017-03-29 17:50:50 +0800 CST

Postgres join onde a tabela estrangeira tem TODOS os registros

  • 772

Eu tenho isso peoplee tagsmesa, assim,

CREATE TABLE people
AS
  SELECT *
  FROM ( VALUES
    (1,'Joe'),
    (2,'Jane')
  ) AS t(id,name);

CREATE TABLE tags
AS
  SELECT * FROM ( VALUES
    (1, 1, 'np'),
    (2, 1, 'yw'),
    (3, 2, 'np')
  ) AS t(id, people_id, tag);

Se eu quiser encontrar tudo o peopleque contém as tags npe na tabela usando uma junção, como eu faria isso de forma eficiente no Postgres 9.6?ywtags

Nesse cenário, devo apenas obter o registro de Joe da peopletabela.

postgresql postgresql-9.6

5 respostas

  1. Aqui estão algumas abordagens alternativas que não envolvem o uso do array_agg.

    Use o INTERSECToperador contra os conjuntos de people_idretornados para cada tag:

    WITH both_tags AS (
    SELECT people_id FROM tags WHERE tag = 'np'
    INTERSECT 
    SELECT people_id FROM tags WHERE tag = 'yw')
SELECT *
  FROM people 
  WHERE id IN (SELECT people_id FROM both_tags);

    Ou você pode usar a COUNT(DISTINCT tag) = 2para encontrar pessoas com ambas as tags. (Observe que o DISTINCTfoi adicionado para lidar com o caso de uma pessoa ter a mesma tag duas vezes. Se isso for impossível, é seguro removê-la.)

    WITH both_tags AS (
    SELECT people_id
      FROM tags
      WHERE tag IN ('np', 'yw')
      GROUP BY people_id
      HAVING COUNT(DISTINCT tag) = 2)
SELECT *
  FROM people 
  WHERE id IN (SELECT people_id FROM both_tags);

    Essa segunda abordagem seria mais fácil de estender para aceitar um número arbitrário de tags, embora a primeira abordagem não fosse impossível.

    • 3

  2. Mais duas maneiras - que usam junções ou subconsultas correlacionadas - e não GROUP BY:

    A primeira usa EXISTSsubconsultas:

    select p.id, p.name
from people as p 
where exists (select from tags as t where t.people_id = p.id and t.tag = 'np')
  and exists (select from tags as t where t.people_id = p.id and t.tag = 'yw')
;

    O segundo assume uma UNIQUErestrição em (tag, people_id):

    select p.id, p.name
from people as p 
  join tags as t1 on t1.people_id = p.id and t1.tag = 'np'
  join tags as t2 on t1.people_id = p.id and t2.tag = 'yw'
;

    Testes em dbfiddle.uk .

    Confira também esta questão no SO, com mais de 10 maneiras de resolver esse tipo de problema - e análise de desempenho: Como filtrar resultados SQL em uma relação tem-muitos-através .

    Existe até uma tag para eles:

    Pode ser surpreendente, mas na maioria das vezes o método many join, o many existe e os semelhantes (como o que usa INTERSECT) são mais eficientes que os group by / countmétodos. Mas é claro que há muitos detalhes que importam para o desempenho. Parâmetros de consulta, tamanhos de tabela, índices, distribuições de dados e muito mais podem afetar o desempenho dos vários métodos.

    • 3

  3. Aqui selecionamos todas as pessoas e agregamos em array as tags. Fazemos isso em uma única passagem. Em seguida, agrupamos isso em uma subseleção e encontramos todas as correspondências em que elas têm np, e yw.

    SELECT people_id, name, array_agg(tag) AS tags
FROM people
JOIN tags ON (people_id = people.id)
GROUP BY people_id, name
HAVING array_agg(tag) @> ARRAY['np', 'yw'];

 id | name |  tags   
----+------+---------
  1 | Joe  | {np,yw}
(1 row)

    Às vezes você pode fazer isso mais rápido, empurrando para baixo a condição

    SELECT people_id, name, array_agg(tag) AS tags
FROM people
JOIN tags ON (people_id = people.id)

-- push down
WHERE tag IN ('np', 'yw')

GROUP BY people_id, name
HAVING array_agg(tag) @> ARRAY['np', 'yw'];

    Você também pode simplesmente colocar a matriz de tags peoplediretamente. Em seguida, consultá-lo torna-se muito simples.

    • 2
  4. Best Answer

    Uma pequena variante da resposta de mendosi , que evita WITH:

    SELECT *
FROM people 
WHERE id IN 
(    
      SELECT people_id
      FROM tags
      WHERE tag IN ('np', 'yw')
      GROUP BY people_id
      HAVING COUNT(DISTINCT tag) = 2
);

    identificação | nome
 -: | :---
  1 | João

    Esta abordagem tem algumas pequenas diferenças em relação à sua abordagem:

    • Se você usa um banco de dados não lida com WITHinstruções (não é o caso do PostgreSQL há muito tempo)
    • Você não se sente confortável comWITH
    • Você quer evitar o fato de que, no PostgreSQL, WITHsão cercas de otimização; e (a partir de hoje) impedir que , eventualmente, o banco de dados execute algumas otimizações.
    • Isso deve estar muito próximo do padrão SQL completo e funciona em todos os bancos de dados disponíveis no DBFiddle (a partir de hoje).

    dbfiddle aqui

    Se você está procurando a solução mais rápida possível , eu verificaria diferentes abordagens em condições práticas e decidiria com base nos tempos que você realmente obtém. Minha consulta proposta é muito padrão e não deve ser mais lenta que aquela com um WITH, mas se é mais lenta ou mais rápida que outras abordagens, eu realmente não sei de antemão.

    • 2

  5. Outra maneira com um simples equi-join:

    select p.id, name
from people p join tags on tags.people_id=p.id
where tag in ('np','yw')
group by p.id, name
having count(distinct tag)=2;

    identificação | nome
-: | :---
 1 | João

    dbfiddle aqui

    • 2

relate perguntas