Проверка двух разных списков с помощью операции AND в одном столбце

Желаемого результата можно добиться с помощью более оптимизированного запроса, используя предложения GROUP BY и HAVING. Этот подход позволяет избежать необходимости многократного самосоединения.

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;

Рассмотрение индекса: Чтобы еще больше повысить производительность, убедитесь, что у вас есть индекс для столбцов customer_id и group_id в таблице customer_to_groups:

CREATE INDEX idx_customer_group ON customers_to_groups (customer_id, group_id);

Мы можем GROUP BY идентификатор клиента и использовать предложение HAVING. Там мы можем использовать CASE или FILTER, если ваша СУБД поддерживает это. Постгрес должен.

Там вам будут поставлены условия.

Запрос будет:

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;

или

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;

Примечание. Приведенные выше запросы предполагают, что вам действительно нужно включить таблицы customers и customers_to_groups и соединить их. Если вам не нужно включать таблицу customers, просто удалите ее и выберите из таблицы customers_to_groups только для улучшения производительности:

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;

Эта демонстрация с примерами данных и множеством дополнительных строк показывает разницу в производительности.

Использование FILTER или CASE намного быстрее (поскольку позволяет избежать второго JOIN в таблице customer_to_groups). Точная разница в производительности зависит от реальных данных в ваших таблицах и от того, какие индексы вы используете.

Это проблема реляционного деления с той особенностью, что у вас есть несколько делителей. То, что у вас есть, неплохо с точки зрения эффективности.

Но если вы хотите иметь возможность делать это динамически и передавать разное количество групп и блоков групп, тогда вам нужно другое решение.

Вы можете передать его как параметр массива 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));

В качестве альтернативы, если у вас есть входные данные в таблице, вы можете использовать полное реляционное деление.

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

db<>рабочий пример

Сделайте GROUP BY, используйте предложение HAVING, чтобы убедиться, что присутствует хотя бы один из (5, 6) и хотя бы один из (1, 3).

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;

Вы также можете использовать 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)