Início / dba / Perguntas / 227194
Accepted
ldrg
Asked: 2019-01-16 07:02:23 +0800 CST

Melhor maneira de selecionar um subconjunto de linhas correspondente?

  • 772

Link do DB Fiddle. Eu tenho uma tabela de muitos para muitos assim:

CREATE TABLE house_to_cats (
  id SERIAL PRIMARY KEY,
  house_id INTEGER,
  cat_id INTEGER
);

-- house with cats 1 and 2: too small
INSERT INTO house_to_cats (house_id, cat_id) VALUES (1, 1), (1, 2);
-- house with cats 1 2 3 4: too big
INSERT INTO house_to_cats (house_id, cat_id) VALUES (2, 1), (2, 2), (2, 3), (2, 4);
-- house with cats 1 2 3: just right
INSERT INTO house_to_cats (house_id, cat_id) VALUES (3, 1), (3, 2), (3, 3);

Eu preciso de uma consulta que pegue uma lista arbitrária de gatos e retorne uma casa correspondente, se existir. Eu vim com isso:

SELECT
    house_id
FROM (
    SELECT
          house_id
        , ARRAY_AGG(cat_id) as cat_id_agg
    FROM house_to_cats
    JOIN (
        SELECT DISTINCT
              house_id
        FROM house_to_cats
        JOIN (SELECT * FROM UNNEST(ARRAY[1, 2, 3]) cat_id) inn USING (cat_id)
    ) filter USING (house_id)
    GROUP BY house_id
) agg
WHERE cat_id_agg <@ ARRAY[1, 2, 3]
  AND cat_id_agg @> ARRAY[1, 2, 3];

Existe uma maneira melhor de fazer isso?

A ideia por trás da minha consulta: em filter, obtenha o house_idque tem pelo menos um dos nossos gatos neles. Em agg, crie cat_id_aggarrays para todos eles house_ids. E na consulta mais externa, filtre os grupos que não correspondem ao nosso conjunto.

postgresql optimization

1 respostas

  1. Best Answer

    Se entendi corretamente, sua consulta pode ser simplificada para:

    select house_id
from house_to_cats
group by house_id
having array_agg(cat_id order by cat_id) = array[1,2,3]

    Observe o order byna array_agg()chamada - o array [3,2,1]não é igual ao array [1,2,3]. Para evitar resultados incorretos devido à agregação ser feita em uma ordem diferente, a matriz agregada deve conter os valores na mesma ordem do valor de comparação.

    Exemplo online: https://rextester.com/IKGHWL59301

    • 3

relate perguntas