sql - array_agg 包含另一个 array_agg

Question

t1
id|entity_type
9|3
9|4
9|5
2|3
2|5

t2  
id|entity_type
1|3
1|4
1|5     

---

SELECT t1.id, array_agg(t1.entity_type)
    FROM t1
GROUP BY
    t1.id
HAVING ARRAY_AGG(t1.entity_type by t1.entity_type) = 
    (SELECT ARRAY_AGG(t2.entity_type by t2.entity_type) 
        FROM t2
    WHERE t2.id = 1
    GROUP BY t2.id);

结果:

t1.id = 9|array_agg{3,4,5}      

我有两个表 t1 和 t2。我想获取 t1.id 的值，其中 t1.entity_type 数组等于 t2.entity_type 数组。

在这种情况下，一切正常。对于 t2.id = 1，我收到 t1.id = 9。两者具有相同的 entity_type 数组:{3,4,5}

现在我想获取 t1.id 不仅适用于相等的集合，还适用于较小的集合。如果我这样修改 t2:

t2  
id|entity_type
1|3
1|4

并以这种方式修改查询:

SELECT t1.id, array_agg(t1.entity_type)
    FROM t1
GROUP BY
    t1.id
HAVING ARRAY_AGG(t1.entity_type by t1.entity_type) >= /*MODIFICATION*/
    (SELECT ARRAY_AGG(t2.entity_type by t2.entity_type) 
        FROM t2
    WHERE t2.id = 1
    GROUP BY t2.id);

我没有收到预期的结果:

t1.id = 1 has {3, 4, 5}     
t2.id = 1 has {3, 4}

t1 中包含 t2 中的数组的数组应该符合条件。我希望收到与第一种情况一样的结果，但我没有得到任何行。
是否有类似的方法:ARRAY_AGG 包含另一个 ARRAY_AGG？

Answer 1

清理

首先，语法错误。我假设你的意思是:

ARRAY_AGG(t1.entity_type <b>ORDER BY</b> t1.entity_type)

Details in the manual.

接下来，使用两次不同的 array_agg() 调用会效率低下。使用相同的(SELECT 列表和 HAVING 子句中的 ORDER BY):

SELECT id, array_agg(entity_type ORDER BY entity_type) AS arr
FROM   t1
GROUP  BY 1
HAVING array_agg(entity_type ORDER BY entity_type) = (
   SELECT array_agg(entity_type ORDER BY entity_type)
   FROM   t2
   WHERE  id = 1
   -- GROUP  BY id   -- not needed
   );

“包含”运算符 @>

赞Nick commented ，您的第二个查询 将使用“包含”运算符 @>

SELECT id, array_agg(entity_type ORDER BY entity_type) AS arr
FROM   t1
GROUP  BY 1
HAVING array_agg(entity_type ORDER BY entity_type) @> (
   SELECT array_agg(entity_type ORDER BY entity_type)
   FROM   t2
   WHERE  id = 1
   );

但这对于大表来说效率很低。

更快的查询

这是一个关系 split 的案例。根据您的(缺失的)精确 表定义，有更有效的技术。我们在这个相关问题下收集了整个武器库:

• How to filter SQL results in a has-many-through relation

假设 (id, entity_type) 在两个表中都是唯一的，这对于大表来说应该明显更快，特别是因为它可以使用t1 上的索引(与您的原始查询相反):

SELECT t1.id
FROM   t2
JOIN   t1 USING (entity_type)
WHERE  t2.id = 1
GROUP  BY 1
HAVING count(*) = (SELECT count(*) FROM t2 WHERE id = 1);

你需要两个索引:

首先在 t2.id 上，通常由主键覆盖。
第二个关于 t1.entity_type:

CREATE INDEX t1_foo_idx ON t1 (entity_type, id);

添加的 id 列是可选的，以允许仅索引扫描。列的顺序是必不可少的:

• Is a composite index also good for queries on the first field?

SQL Fiddle.