Verificando duas listas distintas com operação AND na mesma coluna

Você pode obter o resultado desejado com uma consulta mais otimizada usando uma cláusula GROUP BY e HAVING. Essa abordagem evita a necessidade de múltiplas autojunções

SELECT customer_id
FROM customers_to_groups
WHERE group_id IN (5, 6, 1, 3)
GROUP BY customer_id
HAVING COUNT(DISTINCT CASE WHEN group_id IN (5, 6) THEN 1 END) > 0
   AND COUNT(DISTINCT CASE WHEN group_id IN (1, 3) THEN 1 END) > 0;

Consideração sobre índice: para melhorar ainda mais o desempenho, certifique-se de ter um índice nas colunas customer_id e group_id na tabela clients_to_groups:

CREATE INDEX idx_customer_group ON customers_to_groups (customer_id, group_id);

Podemos GROUP BYidentificar o ID do cliente e usar uma HAVINGcláusula. Lá podemos usar CASEou FILTERse o seu RDBMS suportar. Postgres deveria.

Lá suas condições serão definidas.

A consulta será:

SELECT c.id 
FROM customer c
INNER JOIN customers_to_groups ctg
  ON c.id = ctg.customer_id
  AND ctg.group_id IN (1,3,5,6)
GROUP BY c.id
HAVING 
  COUNT(CASE WHEN ctg.group_id IN (1,3) THEN 1 END) > 0
  AND COUNT(CASE WHEN ctg.group_id IN (5,6) THEN 1 END) > 0;

ou

SELECT c.id 
FROM customer c
INNER JOIN customers_to_groups ctg
  ON c.id = ctg.customer_id
  AND ctg.group_id IN (1,3,5,6)
GROUP BY c.id
HAVING 
  COUNT(*) FILTER(WHERE ctg.group_id IN (1,3)) > 0
  AND COUNT(*) FILTER(WHERE ctg.group_id IN (5,6)) > 0;

Nota: As consultas acima pressupõem que você realmente precisa incluir ambas customersas customers_to_groupstabelas e juntá-las. Caso não precise incluir a customerstabela, basta removê-la e selecionar apenas na tabela customers_to_groupspara melhorar o desempenho:

SELECT customer_id
FROM customers_to_groups ctg
WHERE ctg.group_id IN (1,3,5,6)
GROUP BY customer_id
HAVING 
  COUNT(*) FILTER(WHERE ctg.group_id IN (1,3)) > 0
  AND COUNT(*) FILTER(WHERE ctg.group_id IN (5,6)) > 0;

Esta demonstração com seus dados de amostra e muitas linhas adicionais mostra as diferenças de desempenho.

Usar FILTERor CASEé muito mais rápido (pois evita um segundo JOINna tabela clients_to_groups). A diferença exata de desempenho depende dos dados reais em suas tabelas e de quais índices você usa.

Este é um problema de Divisão Relacional com a diferença de que você tem múltiplos divisores. O que você tem não é ruim em termos de eficiência.

Mas se você quiser fazer isso dinamicamente e passar diferentes números de grupos e blocos de grupos, precisará de uma solução diferente.

Você pode passá-lo como um parâmetro de array JSONB

SELECT
  cg.customer_id
FROM customers_to_groups cg
JOIN jsonb_array_elements('[[5,6],[1,3]]'::jsonb) sections(value)
    ON cg.group_id IN (SELECT value::int FROM jsonb_array_elements(sections.value))
GROUP BY
  cg.customer_id
HAVING COUNT(*) = MIN(jsonb_array_length('[[5,6],[1,3]]'::jsonb));

Alternativamente, se você tiver os dados de entrada em uma tabela, poderá usar a Divisão Relacional completa

SELECT
  c.id
FROM (
    SELECT
      c.id,
      COUNT(cg.group_id) AS count_matches
    FROM customer c
    CROSS JOIN input_groups ig
    LEFT JOIN customers_to_groups cg
        ON cg.group_id = ig.group_id
       AND cg.customer_id = c.id
    GROUP BY
      c.id,
      ig.section_id 
) c
GROUP BY
  c.id
HAVING COUNT(*) FILTER (WHERE count_matches = 0) = 0;   -- all sections have a match

banco de dados<> violino

Faça uma cláusula GROUP BY, use HAVINGpara garantir que pelo menos um de (5, 6) e pelo menos um de (1, 3) estejam lá.

SELECT customer_id
FROM customers_to_groups
WHERE group_id IN (5, 6, 1, 3)
GROUP BY customer_id
HAVING MAX(group_id) >= 5
   AND MIN(group_id) <= 3;

Você também pode usar INTERSECT:

SELECT customer_id FROM customers_to_groups WHERE group_id IN (5, 6)
INTERSECT
SELECT customer_id FROM customers_to_groups WHERE group_id IN (1, 3)

se esta tarefa for repetitiva, você pode colocá-la em uma função

CREATE OR REPLACE FUNCTION get_customers_by_groups(group_set1 int[], group_set2 int[])
RETURNS TABLE(customer_id int) AS $f$
    SELECT c.id
    FROM customer c
    INNER JOIN customers_to_groups ctg ON c.id = ctg.customer_id
    GROUP BY c.id
    HAVING COUNT(DISTINCT CASE WHEN ctg.group_id = ANY(group_set1) THEN 1 END) > 0
       AND COUNT(DISTINCT CASE WHEN ctg.group_id = ANY(group_set2) THEN 1 END) > 0;
$f$ LANGUAGE sql;

Como customer_idé provável que seja uma chave estrangeira para customer.idsignificar que ambas as tabelas possuem o mesmo valor, não vejo motivo para alcançá-la customer.

Uma forma bem padrão SQL de fazer isso é usando INTERSECTjá sugerido pelo @jarlh :

SELECT customer_id FROM customers_to_groups WHERE group_id=any('{1,3}')
INTERSECT ALL
SELECT customer_id FROM customers_to_groups WHERE group_id=any('{5,6}');

ALLa palavra-chave ignora a desduplicação, que é o comportamento padrão de INTERSECT( DISTINCT).

Você também pode executar uma auto-junção :

SELECT customer_id 
FROM customers_to_groups a
JOIN customers_to_groups b
USING (customer_id)
WHERE a.group_id=any('{1,3}')
 AND  b.group_id=any('{5,6}');

E se você gosta da ideia de @Rahul Jangid e Jonas Metzlerhaving , a condição que você procura se simplifica para bool_or():

SELECT customer_id 
FROM customers_to_groups
WHERE group_id=any('{1,3,5,6}')
GROUP BY customer_id
HAVING bool_or(group_id=any('{1,3}'))
   AND bool_or(group_id=any('{5,6}'));

Ou você pode usar um EXISTS:

SELECT customer_id 
FROM customers_to_groups a
WHERE group_id=any('{1,3}'
  AND EXISTS (SELECT FROM customers_to_groups b
              WHERE a.customer_id=b.customer_id
              AND group_id=any('{5,6}') );

Neste teste db<>fiddle com 19 000clientes com 1 900 000atribuições aleatórias a alguns 19 000grupos, a EXISTSvariante ganha o plano mais rápido com Hash Semi Join .

Todos estes são melhor atendidos com um índice de cobertura que permite varreduras somente de índice :

CREATE INDEX ON customers_to_groups (group_id)
  INCLUDE(customer_id);

Se você pretende reutilizá-lo dinamicamente, envolva-o em uma instrução preparada com parâmetros :

PREPARE customers_in_groups(int[],int[]) AS
SELECT customer_id 
FROM customers_to_groups a
WHERE group_id=any($1)
  AND EXISTS (SELECT FROM customers_to_groups b
              WHERE a.customer_id=b.customer_id
              AND group_id=any($2) );

Então

EXECUTE customers_in_groups('{1,3}','{5,6}');