主页 / coding / 问题 / 78842924
Accepted
peti446
Asked: 2024-08-07 17:39:39 +0800 CST

检查同一列上使用 AND 运算的两个不同列表

  • 772

我有以下客户的示例数据结构，它可以使用连接表和数据成为多个组的一部分：

CREATE TABLE customer(id) AS VALUES (0),(1),(2),(3);
CREATE TABLE groups(id)   AS VALUES (1),(3),(5),(6);
CREATE TABLE customers_to_groups(customer_id, group_id) AS
  VALUES (0, 1)--customer 0 is in group (5 OR 6) AND (1 OR 3)
        ,(0, 5)--customer 0 is in group (5 OR 6) AND (1 OR 3)
        ,(1, 1)
        ,(1, 90)
        ,(2, 1)
        ,(3, 3)--customer 3 is in group (5 OR 6) AND (1 OR 3)
        ,(3, 5)--customer 3 is in group (5 OR 6) AND (1 OR 3)
        ,(3, 90);

我需要获取属于特定组的客户，并且需要获取属于多个组列表中至少 1 个组的所有客户列表。例如，我想获取组中所有客户(5 OR 6) AND (1 OR 3)，因此例如组 5 和 1 中的客户将被返回，但组 1 和 90 或仅组 1 中的客户则不会被返回。使用提供的样本数据，我们将仅获取 ID 为 0 和 3 的客户，因为它们符合上述给定的规则。

仅仅这样做WHERE group_id IN (5,6) AND group_id IN (1,3)似乎不起作用，所以我正在寻找替代方案。

到目前为止我得到了这个有效的方法：

SELECT DISTINCT c.id
FROM customer c
INNER JOIN customers_to_groups at1 ON c.id = at1.customer_id
INNER JOIN customers_to_groups at2 ON c.id = at2.customer_id
WHERE at1.group_id IN (5, 6)
  AND at2.group_id IN (1, 3);

预期成绩：

ID
0
3

有没有更有效的方法来实现这一点？

sql

6 个回答

  1. 您可以使用 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;

    索引注意事项：为了进一步提高性能，请确保在 customers_to_groups 表中的 customer_id 和 group_id 列上有一个索引：

    CREATE INDEX idx_customer_group ON customers_to_groups (customer_id, group_id);
    • 2
  2. Best Answer

    我们可以使用GROUP BY客户的 ID 并使用子句。如果您的 RDBMS 支持它，HAVING我们可以使用CASE或。Postgres 应该。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在表 customers_to_groups 上花费一秒钟的时间）。确切的性能差异取决于表中的实际数据以及您使用的索引。

    • 2

  3. 这是关系除法的问题，不同之处在于您有多个除数。从效率的角度来看，您得到的结果是不错的。

    但是如果您希望能够动态地执行此操作，并传递不同数量的组和组块，那么您需要一个不同的解决方案。

    您可以将其作为 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<>小提琴

    • 1

  4. 执行GROUP BY, useHAVING子句以确保 (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)
    • 1

  5. 如果这个任务是重复性的，你可以把它放在一个函数中

    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;
    • 0

  6. 由于customer_id很可能是一个外键，customer.id意味着两个表都具有相同的值，所以我完全没有理由去追求它customer。

    一种非常 SQL 标准的方法是使用@jarlhINTERSECT已经建议的：

    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}');

    ALL关键字跳过重复数据删除，这是INTERSECT( DISTINCT) 的默认行为。

    您还可以运行自连接：

    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}');

    如果你喜欢@Rahul jangid和Jonas Metzler的having想法，那么你正在寻找的条件可以简化为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}'));

    或者您可以使用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}') );

    在这个db<>fiddle 测试中，19 000客户被1 900 000随机分配到一些19 000组，该变体通过Hash Semi JoinEXISTS赢得了最快的计划。

    所有这些都最好通过覆盖索引来实现，从而实现仅索引扫描：

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

    如果您打算动态地重新使用它，请将其包装在参数化的准备好的语句中：

    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) );

    然后

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

相关问题