聚合多个联接表上的函数

Question

我有三个表：

CREATE TABLE foo (
    id bigint PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE foo_bar (
    id bigint PRIMARY KEY,
    foo_id bigint NOT NULL
);

CREATE TABLE tag (
    name text NOT NULL,
    target_id bigint NOT NULL,
    PRIMARY KEY (name, target_id)
);

我正在尝试创建一个视图，以便获得table的所有字段，where中foo的项目计数以及foo_barwherefoo.id = foo_bar.foo_id中所有标签的文本数组foo.id = tag.target_id。如果我们有：

INSERT INTO foo VALUES (1, 'one');
INSERT INTO foo VALUES (2, 'two');
INSERT INTO foo_bar VALUES (1, 1);
INSERT INTO foo_bar VALUES (2, 1);
INSERT INTO foo_bar VALUES (3, 2);
INSERT INTO foo_bar VALUES (4, 1);
INSERT INTO foo_bar VALUES (5, 2);
INSERT INTO tag VALUES ('a', 1);
INSERT INTO tag VALUES ('b', 1);
INSERT INTO tag VALUES ('c', 2);

结果应返回：

foo.id    | foo.name     | count       | array_agg
--------------------------------------------------
1         | one          | 3           | {a, b}
2 聽 聽 聽 聽 | two 聽 聽 聽 聽 聽| 2 聽 聽 聽 聽 聽 | {c}

这是我到目前为止所拥有的：

SELECT DISTINCT f.id, f.name, COUNT(b.id), array_agg(t.name)
FROM foo AS f, foo_bar AS b, tag AS t
WHERE f.id = t.target_id AND f.id = b.foo_id
GROUP BY f.id, b.id;

这些是我得到的结果（请注意，这count是不正确的）：

foo.id    | foo.name     | count       | array_agg
--------------------------------------------------
1         | one          | 2           | {a, b}
2 聽 聽 聽 聽 | two 聽 聽 聽 聽 聽| 1 聽 聽 聽 聽 聽 | {c}

将count始终是标签的数量，而不是不同的计foo_bar数值。我尝试重新排序/修改GROUP BY和SELECT子句，它们返回不同的结果，但不是我想要的结果。我认为我在使用该array_agg()功能时遇到了麻烦，但是我不确定是否是这种情况或如何解决。

Answer 1

SELECT f.id, f.name, b.fb_ct, t.tag_names
FROM   foo f
LEFT JOIN  (
    SELECT foo_id AS id, count(*) AS fb_ct
    FROM   foo_bar
    GROUP  BY 1
    ) b USING (id)
LEFT JOIN  (
    SELECT target_id AS id, array_agg(name) AS tag_names
    FROM   tag
    GROUP  BY 1
    ) t USING (id)
ORDER  BY f.id;

产生所需的结果。

• 用显式JOIN语法重写。使其更易于阅读和理解（和调试）。

• 通过联接到多个1:n相关的表，行将相互乘以产生笛卡尔乘积-这是非常昂贵的废话。这是CROSS JOIN代理人意料之外的。有关的：

  • 两个SQL LEFT JOINS产生不正确的结果
  • 为避免这种情况，请在汇总（）之前将 一个 n-table与1-table最多连接GROUP BY。您可以聚合两次，但是 在将 它们n联接到-table 之前 ，分别聚合-tables更加干净快捷1。

• 与您的原始图片（带有隐式INNER JOIN）相反。我通常 LEFT JOIN 会避免丢失或中foo没有匹配行的行。foo_bar``tag

• 一旦将意外CROSS JOIN内容从查询中删除，则无需再添加DISTINCT任何内容-假设这foo.id是唯一的。