Postgres LEFT JOIN 与 SUM，丢失记录

我正在尝试获取相关表中某些类型记录的计数。我正在使用左连接。

因此，我有一个不太正确的查询，但返回了正确的结果。正确的结果查询具有更高的执行成本。如果我可以纠正结果，我想使用第一种方法。 （看http://sqlfiddle.com/#!15/7c20b/5/2 http://sqlfiddle.com/#!15/7c20b/5/2)

CREATE TABLE people(
  id SERIAL,
  name varchar not null
);

CREATE TABLE pets(
  id SERIAL,
  name varchar not null, 
  kind varchar not null,
  alive boolean not null default false,
  person_id integer not null
);

INSERT INTO people(name) VALUES
('Chad'),
('Buck'); --can't keep pets alive

INSERT INTO pets(name, alive, kind, person_id) VALUES
('doggio', true, 'dog', 1),
('dog master flash', true, 'dog', 1),
('catio', true, 'cat', 1),
('lucky', false, 'cat', 2);

我的目标是找回一张桌子，上面有所有的人以及他们所养宠物的种类：

| ID | ALIVE_DOGS_COUNT | ALIVE_CATS_COUNT |
|----|------------------|------------------|
|  1 |                2 |                1 |
|  2 |                0 |                0 |

我把这个例子变得更简单了。在我们的生产应用程序（不是真正的宠物）中，每人大约有 100,000 只死狗和猫。我知道这很糟糕，但这个例子更容易转发;)我希望在计数之前过滤掉所有“死”的东西。我现在在生产中的查询速度较慢（来自上面的 sqlfiddle），但希望 LEFT JOIN 版本能够正常工作。

如果您获取，通常最快所有或大多数行:

SELECT pp.id
     , COALESCE(pt.a_dog_ct, 0) AS alive_dogs_count
     , COALESCE(pt.a_cat_ct, 0) AS alive_cats_count
FROM   people pp
LEFT   JOIN (
   SELECT person_id
        , count(kind = 'dog' OR NULL) AS a_dog_ct
        , count(kind = 'cat' OR NULL) AS a_cat_ct
   FROM   pets
   WHERE  alive
   GROUP  BY 1
   ) pt ON pt.person_id = pp.id;

索引在这里无关紧要，全表扫描将是最快的。Except如果活着的宠物是rare情况下，那么一个部分索引 http://www.postgresql.org/docs/current/interactive/indexes-partial.html应该有帮助。喜欢：

CREATE INDEX pets_alive_idx ON pets (person_id, kind) WHERE alive;

我包含了查询所需的所有列(person_id, kind)允许仅索引扫描。

SQL 小提琴。 http://sqlfiddle.com/#!15/78baa/2

通常最快的小子集或单行:

SELECT pp.id
     , count(kind = 'dog' OR NULL) AS alive_dogs_count
     , count(kind = 'cat' OR NULL) AS alive_cats_count
FROM   people pp
LEFT   JOIN pets pt ON pt.person_id = pp.id
                   AND pt.alive
WHERE  <some condition to retrieve a small subset>
GROUP  BY 1;

你至少应该有一个索引pets.person_id为此（或上面的部分索引） - 可能还有更多，具体取决于WHERE健康）状况。

相关回答：

• 使用 LEFT JOIN 的查询不返回计数为 0 的行 https://stackoverflow.com/questions/15467624/count-on-left-join-not-returning-0-values/15468034#15468034
• JOIN 返回重复项后的 GROUP 或 DISTINCT https://stackoverflow.com/questions/25486942/group-or-distinct-after-join-returns-duplicates/25487898#25487898
• 获取多个表中外键的数量 https://stackoverflow.com/questions/24745091/get-count-of-foreign-key-from-multiple-tables/24747523#24747523