sql - 带有 SUM 的 Postgres LEFT JOIN，缺少记录

Question

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

所以我有一个不太正确的查询，但返回了正确的结果。正确的结果查询具有更高的执行成本。如果我可以更正结果，我喜欢使用第一种方法。 (参见 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);

我的目标是找回一张 table ，上面有所有人和他们还活着的宠物种类的数量:

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

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

Answer 1

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

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;

索引在这里无关紧要，全表扫描将是最快的。 除非如果活着的宠物是罕见的情况，那么partial index应该有帮助。喜欢:

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

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

SQL Fiddle.

通常小子集或单行最快:

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 条件。

相关回答:

• Query with LEFT JOIN not returning rows for count of 0
• GROUP or DISTINCT after JOIN returns duplicates
• Get count of foreign key from multiple tables