Início / dba / Perguntas / 87249
Accepted
user4150760
Asked: 2014-12-31 05:54:29 +0800 CST

Noções básicas sobre junção de várias tabelas com agregação

  • 772

Estou com uma dúvida básica de como JOINfunciona em várias tabelas. Eu quero contar ocorrências de chave estrangeira em link1&link2

CREATE TABLE main (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE link1 (
   id SERIAL PRIMARY KEY,
   main_id integer NOT NULL,
   CONSTRAINT main_id_fk FOREIGN KEY (main_id) REFERENCES main (id)
);

-- link2 is similar to link1

SQL Fiddle

Por que a consulta abaixo fornece um produto de contagens (em vez de soma) quando a contagem é diferente de zero em ambas as colunas.

SELECT main.id, COUNT(link1.main_id) + COUNT(link2.main_id)
FROM main
LEFT JOIN link1 ON main.id=link1.main_id
LEFT JOIN link2 ON main.id=link2.main_id
GROUP BY main.id
postgresql join

2 respostas

  1. O que você vê é uma "junção cruzada de proxy". Agregar primeiro, depois juntar:

    SELECT m.id, COALESCE(l1.ct, 0) + COALESCE(l2.ct, 0) AS total_ct
FROM   main m
LEFT   JOIN (
   SELECT main_id, count(*) AS ct
   FROM   link1
   GROUP  BY main_id
   ) l1 ON l1.main_id = m.id
LEFT   JOIN (
   SELECT main_id, count(*) AS ct
   FROM   link2
   GROUP  BY main_id
   ) l2 ON l2.main_id = m.id
ORDER BY m.id;

    SQL Fiddle.

    Não multiplique linhas com várias junções não qualificadas e depoiscount(DISTINCT ...) para corrigir o erro. Acontece que funciona neste caso, pois a contagem de DISTINCT link1.id/ link2.idcoincide com o resultado desejado, mas é desnecessariamente caro e sujeito a erros.

    Explicação detalhada e algumas variantes de sintaxe nessas respostas relacionadas no SO:

    • 7
  2. Best Answer

    Vou tentar responder eu mesmo. Considere um LEFT JOINentre main& link1. A saída seria

    main.id   link1.main_id
   1          1
   1          1
   2          2
   3         NULL  
   4         NULL

    Agora faça um LEFT JOINda tabela acima com link2, a saída seria:

    main.id    link1.main_id    link2.main_id
   1             1               NULL  
   1             1               NULL  
   2             2                2
   2             2                2     -- Error : double counting for link1
   3            NULL              3
   4            NULL

    Agora conte as ocorrências de main_id& some-as (agrupadas por main.id)

    main.id        Count
   1             2
   2             2 + 2  
   3             1
   4             0

    Portanto, duas sucessivas LEFT JOINestão acontecendo sequencialmente, e não em paralelo. A abordagem correta para obter a contagem seria realizar 2 consultas separadamente e adicionar os resultados

    Atualizar Outra maneira de acordo com @ a1ex07 é

    SELECT main.id, COUNT(DISTINCT link1.id) + COUNT(DISTINCT link2.id)
FROM main
LEFT JOIN link1 ON main.id=link1.main_id
LEFT JOIN link2 ON main.id=link2.main_id
GROUP BY main.id
    • 3

relate perguntas