sql - 具有多个值的数组列上的 LEFT OUTER JOIN

Question

当一个表不是数组值，而另一个表的数组值可以包含多个值时，我似乎找不到通过数组列连接两个表的技巧。当存在单值数组时它确实有效。

这是我正在谈论的一个简单的最小示例。真实的表在数组列 FWIW 上有 GIN 索引。这些不是，但查询的行为是相同的。

DROP TABLE IF EXISTS eg_person;
CREATE TABLE eg_person (id INT PRIMARY KEY, name TEXT);
INSERT INTO eg_person (id, name) VALUES
  (1, 'alice')
, (2, 'bob')
, (3, 'charlie');

DROP TABLE IF EXISTS eg_assoc;
CREATE TABLE eg_assoc (aid INT PRIMARY KEY, actors INT[], benefactors INT[]);
INSERT INTO eg_assoc (aid, actors, benefactors) VALUES
  (1, '{1}'  , '{2}')
, (2, '{1,2}', '{3}')
, (3, '{1}'  , '{2,3}')
, (4, '{4}'  , '{1}');

SELECT aid, actors, a_person.name, benefactors, b_person.name 
FROM   eg_assoc
LEFT   JOIN eg_person a_person on array[a_person.id] @> eg_assoc.actors
LEFT   JOIN eg_person b_person on array[b_person.id] @> eg_assoc.benefactors;

实际结果是这样的。这里的问题是，如果 actors 或 benefactors 包含多个值，则 name 列会出现 NULL。

 aid | actors | name  | benefactors |  name   
-----+--------+-------+-------------+---------
   1 | {1}    | alice | {2}         | bob
   2 | {1,2}  |       | {3}         | charlie
   3 | {1}    | alice | {2,3}       | 
   4 | {4}    |       | {1}         | alice

我期待着这个:

 aid | actors | name  | benefactors |  name   
-----+--------+-------+-------------+---------
   1 | {1}    | alice | {2}         | bob
   2 | {1,2}  | alice | {3}         | charlie
   2 | {1,2}  | bob   | {3}         | charlie
   3 | {1}    | alice | {2,3}       | bob
   3 | {1}    | alice | {2,3}       | charlie
   4 | {4}    |       | {1}         | alice

如果我能让它看起来像这样，那就太好了:

 aid | actors | name        | benefactors |  name   
-----+--------+-------------+-------------+---------
   1 | {1}    | {alice}     | {2}         | {bob}
   2 | {1,2}  | {alice,bob} | {3}         | {charlie}
   3 | {1}    | {alice}     | {2,3}       | {bob, charlie}
   4 | {4}    |             | {1}         | {alice}

我知道这个模式是非规范化的，如果需要的话我愿意使用正常的表示。然而，这是一个汇总查询，它已经涉及比我想要的更多的联接。

Answer 1

是的，overlap operator && can use a GIN index on arrays 。对于像这样的查询非常有用，可以在一组参与者中查找包含给定人员 (1) 的行:

SELECT * FROM eg_assoc WHERE actors && '{1}'::int[]

但是，查询的逻辑是相反的，即查找 eg_assoc 数组中列出的所有人员。 GIN 索引在这里没有帮助。我们只需要 PK person.id 的 btree 索引。

正确的查询

基础知识:

• PostgreSQL unnest() with element number

以下查询完全按照给定的方式保留原始数组，包括可能的重复元素和元素的原始顺序。适用于一维数组。附加维度被折叠成单一维度。保留多个维度更复杂(但完全可能):

Postgres 9.4 或更高版本中的

WITH ORDINALITY

SELECT aid, actors
     , ARRAY(SELECT name
             FROM   unnest(e.actors) WITH ORDINALITY a(id, i)
             JOIN   eg_person p USING (id)
             ORDER  BY a.i) AS act_names
     , benefactors
     , ARRAY(SELECT name
             FROM   unnest(e.benefactors) WITH ORDINALITY b(id, i)
             JOIN   eg_person USING (id)
             ORDER  BY b.i) AS ben_names
FROM   eg_assoc e;

横向查询

对于 PostgreSQL 9.3+。

SELECT e.aid, e.actors, a.act_names, e.benefactors, b.ben_names
FROM   eg_assoc e
, LATERAL (
   SELECT ARRAY( SELECT name
                 FROM   generate_subscripts(e.actors, 1) i
                 JOIN   eg_person p ON p.id = e.actors[i]
                 ORDER  BY i)
   ) a(act_names)
, LATERAL (
   SELECT ARRAY( SELECT name
                 FROM   generate_subscripts(e.benefactors, 1) i
                 JOIN   eg_person p ON p.id = e.benefactors[i]
                 ORDER  BY i)
   ) b(ben_names);

db<> fiddle here 有几个变体。
<子>旧sqlfiddle

微妙的细节:如果找不到一个人，它就会被丢弃。如果在整个数组中找不到人员，这两个查询都会生成一个空数组 ('{}')。其他查询样式将返回 NULL。我向 fiddle 添加了变体。

相关子查询

对于 Postgres 8.4+(其中引入了 generate_subsrcipts()):

SELECT aid, actors
     , ARRAY(SELECT name
             FROM   generate_subscripts(e.actors, 1) i
             JOIN   eg_person p ON p.id = e.actors[i]
             ORDER  BY i) AS act_names
     , benefactors
     , ARRAY(SELECT name
             FROM   generate_subscripts(e.benefactors, 1) i
             JOIN   eg_person p ON p.id = e.benefactors[i]
             ORDER  BY i) AS ben_names
FROM   eg_assoc e;

即使在 Postgres 9.3 中，仍可能表现最佳。
ARRAY constructor比 array_agg() 更快。请参阅:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

您失败的查询

query provided by @a_horse 似乎可以完成这项工作，但它不可靠、具有误导性、可能不正确且不必要地昂贵。

1. 由于两个不相关的连接而导致代理交叉连接。一种狡猾的反模式。请参阅:

   • Two SQL LEFT JOINS produce incorrect result

   在array_agg()中使用DISTINCT进行了表面修复，以消除生成的重复项，但这确实是在给 pig 涂上口红。它还消除了原始内容中的重复项，因为此时无法区分差异 - 这可能是不正确的。

2. 表达式a_person.id = any(eg_assoc.actors)有效，但消除了结果中的重复项(发生了两次次)，除非指定，否则这是错误的。

3. 原始数组元素的顺序不会保留。一般来说，这很棘手。但在这个查询中，情况变得更加严重，因为参与者和捐助者被倍增并再次变得不同，这保证了任意顺序。

4. 外部 SELECT 中没有列别名会导致重复的列名称，这会导致某些客户端失败(没有别名时无法正常工作)。

5. min(actors) 和 min(benefactors) 毫无用处。通常，人们只需将列添加到 GROUP BY 中，而不是假聚合它们。但 eg_assoc.aid 无论如何都是 PK 列(覆盖 GROUP BY 中的整个表)，因此甚至没有必要。只是 Actor 、捐助者。

聚合整个结果一开始就是浪费时间和精力。使用不会将基行相乘的更智能查询，这样您就不必将它们聚合回来。