具有多个值的数组列上的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;

实际结果是这样的。这里的问题是，NULL如果其中一个actors或benefactors包含多个值，则会出现“名称”列。

 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

&& 运算符和索引？

没错，重叠运算符&&可以在arrays上使用GIN索引。对于以下查询非常有用（在参与者之间查找人为1的行）：

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

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

正确的查询

这不是一个小问题。首先阅读以下内容：

• PostgreSQL unnest（），元素编号

以下查询将 完全按照给定的 顺序保留原始数组，包括可能重复的元素和元素的原始顺序。适用于 一维数组
。其他尺寸将折叠为一个尺寸。保留多个维度较为复杂（但完全可能）：

相关子查询

对于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;

即使在第9.3页中，仍可能表现最佳。
使用比快的数组构造函数array_agg()。

LATERAL 询问

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

SQL Fiddle 具有两个变体（第pg 9.4版除外）。

微妙的细节：如果找不到人，则将其遗漏。如果找不到整个数组，则这两个查询都会生成一个 空数组 （ '{}' ）。其他查询样式将返回
NULL 。我在小提琴中添加了变体。

WITH ORDINALITY 在Postgres 9.4+

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;

查询失败

@a_horse提供
的查询 似乎
可以完成任务，但是它不可靠，具有误导性，可能不正确且不必要地昂贵。

1. 代理交叉联接，因为有两个不相关的联接。偷偷摸摸的反模式。细节：

   • 两个SQL LEFT JOINS产生不正确的结果

使用DISTINCTin固定在表面上，array_agg()以消除生成的重复项，但这实际上是在唇膏上涂上了猪。它还 消除了原件中的重复项，
因为此时无法分辨出差异-这可能是不正确的。

1. 该表达式a_person.id = any(eg_assoc.actors) 有效 ，但会从结果中 消除重复项 （在此查询中发生两次），除非指定，否则是错误的。

2. 不保留数组元素的 原始 顺序 。一般来说，这很棘手。但这在查询中会加剧，因为参与者和恩人被相乘并再次变得不同，从而 保证了 任意顺序。

3. 外部没有列别名会SELECT导致重复的列名，这会使某些客户端失败（在没有别名的小提琴中无法使用）。

4. min(actors)而且min(benefactors)没有用。通常情况下，只需将列添加到其中，GROUP BY而不用假汇总它们。但是eg_assoc.aid无论如何PK列（在中包含整个表GROUP BY），所以甚至没有必要。只是actors, benefactors。

汇总整个结果会浪费时间和精力。使用更智能的查询，而不是将基本行相乘，则不必将它们汇总在一起。