Início / dba / Perguntas / 341073
Accepted
Александр
Asked: 2024-07-22 01:33:41 +0800 CST

Muitos para muitos: selecione uma entidade por relacionamento correspondente

  • 772

Eu tenho um relacionamento muitos para muitos implementado:

CREATE TABLE public.message (
    id BIGSERIAL PRIMARY KEY,
    name varchar(40) UNIQUE NOT NULL
);

CREATE TABLE public.package(
    id BIGSERIAL PRIMARY KEY,
    name varchar(40) UNIQUE NOT NULL
);

CREATE TABLE public.package_to_message (
    message_id BIGINT NOT NULL,
    package_id BIGINT NOT NULL,
    CONSTRAINT package_to_message_pk PRIMARY KEY (message_id, package_id)
);

Preciso selecionar um pacote que esteja associado a um conjunto de mensagens definido com precisão [message_name_1, message_name_2, message_name_3]. Com todos os itens acima e nada mais. É possível fazer isso usando uma consulta mais ou menos otimizada? Nada vem à mente.

postgresql

2 respostas

  1. Best Answer

    A palavra-chave aqui é “divisão relacional” .

    Para as especificações fornecidas, não será mais rápido que isso:

    SELECT package_id
FROM   package_to_message p1
JOIN   package_to_message p2 USING (package_id)
JOIN   package_to_message p3 USING (package_id)
WHERE  p1.message_id = (SELECT m.id FROM message m WHERE m.name = 'message_name_1')
AND    p2.message_id = (SELECT m.id FROM message m WHERE m.name = 'message_name_2')
AND    p3.message_id = (SELECT m.id FROM message m WHERE m.name = 'message_name_3')
AND    NOT EXISTS (  -- no other!
   SELECT FROM package_to_message p0
   WHERE  p0.package_id = p1.package_id
   AND    p0.message_id NOT IN (p1.message_id, p2.message_id, p3.message_id)
   );

    Mas talvez você precise de uma consulta mais genérica/dinâmica...

    PREPARE q1(text[]) AS
WITH m AS (                              -- translate names to IDs
   SELECT id AS message_id
   FROM   message
   WHERE  name = ANY($1)
   )
SELECT p.package_id
FROM   m
JOIN   package_to_message p USING (message_id)
GROUP  BY 1
HAVING count(*) = cardinality($1)        -- length of array
AND    NOT EXISTS (                      -- no other message
   SELECT FROM package_to_message p0
   WHERE  p0.package_id = p.package_id
   AND    p0.message_id <> ALL(TABLE m)  -- m holds IDs
   );

    Chamar:

    EXECUTE q1('{message_name_1, message_name_2, message_name_3}');

    Minhas consultas pressupõem que você passe nomes de mensagens distintos (sem duplicatas). Além disso, message.nameestá definido UNIQUE.

    Observe como ANYe ALLaceite um array ou um conjunto. Ver:

    Sobre a sintaxe curta TABLE m:

    Índices e desempenho

    Índice 1

    A UNIQUErestrição na tabela messagecobre bem a pesquisa.
    Se o desempenho for crucial (e você atender aos pré-requisitos para varreduras somente de índice), um índice de cobertura seria um pouco melhor:

    ALTER TABLE message
  DROP CONSTRAINT message_name_key  -- actual constraint name here!
, ADD  CONSTRAINT message_name_id_key UNIQUE (name) INCLUDE (id)
;

    Ver:

    Índice 2

    O PRIMARY KEYon package_to_message(message_id, package_id)fornece o índice perfeito para a próxima etapa.

    Índice 3

    Idealmente, você adiciona outro índice package_to_message(package_id, message_id)para a etapa final. Ver:

    Com esses três índices em vigor, e se suas tabelas forem limpas o suficiente, ambas as consultas poderão se contentar exclusivamente com varreduras somente de índice. Então leva no máximo alguns ms, mesmo para tabelas grandes, ou até menos de 1 ms de tempo de execução .

    --

    Relacionado:

    • 0

  2. Outra opção usa contagens para verificar se temos todas as correspondências. Observe que sua pergunta é especificamente um exemplo de divisão relacional multidirecional sem resto, não com.

    PREPARE q1(text[]) AS
SELECT
    pm.package_id
FROM   message m
JOIN   package_to_message pm ON pm.message_id = m.id
GROUP BY
    pm.package_id
HAVING count(*) = cardinality($1)
   AND count(*) = count(*) FILTER (WHERE m.name = ANY($1));

    Esta é uma variação de uma solução mais generalizada para qualquer Divisão Relacional multidirecional, que é mais eficiente usando um array

    SELECT
    pm.package_id
FROM   message m
JOIN   package_to_message pm ON pm.message_id = m.id
LEFT JOIN inputData i ON i.value = m.name
GROUP BY
    pm.package_id
HAVING count(*) = (SELECT count(*) FROM inputData)
   AND count(*) = count(i.value);

    Para fazer o mesmo com With Remainder, altere a HAVINGcláusula para HAVING count(*) >= (SELECT count(*) FROM inputData);e altere LEFT JOINpara a JOIN.

    Consulte também este artigo , entre outros, para obter mais opções de Divisão Relacional.

    • 0

relate perguntas