主页 / dba / 问题 / 227194
Accepted
ldrg
Asked: 2019-01-16 07:02:23 +0800 CST

选择匹配的行子集的更好方法？

  • 772

数据库小提琴链接。我有很多这样的表：

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

我需要一个查询，该查询采用任意猫列表并返回匹配的房子（如果存在）。我想出了这个：

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

有一个更好的方法吗？

我的查询背后的想法：在 中filter，获取house_id其中至少有一只我们的猫。在中，为所有这些agg创建数组。并在最外层的查询中过滤掉与我们的集合不匹配的组。cat_id_agghouse_ids

postgresql optimization

1 个回答

  1. Best Answer

    如果我理解正确，您的查询可以简化为：

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

    请注意调用order by中的- 数组不等于数组。为避免因以不同顺序进行聚合而导致的错误结果，聚合数组必须包含与比较值顺序相同的值。array_agg()[3,2,1][1,2,3]

    在线示例：https ://rextester.com/IKGHWL59301

    • 3

相关问题