Postgres LEFT JOIN with SUM, отсутствующие записи

Я пытаюсь получить счет некоторых типов записей в связанной таблице. Я использую левое соединение.

Поэтому у меня есть запрос, который не совсем прав, и тот, который возвращает правильные результаты. Правильный запрос результатов имеет более высокую стоимость исполнения. Id нравится использовать первый подход, если я могу исправить результаты. (см. 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; 

Индексы здесь неактуальны, полное сканирование таблицы будет самым быстрым. Кроме того, если живые животные – редкий случай, то частичный индекс должен помочь. Подобно:

 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 .

Похожие ответы:

  • Запрос с LEFT JOIN не возвращает строки для подсчета 0
  • GROUP или DISTINCT после того, как JOIN возвращает дубликаты
  • Получать подсчет внешнего ключа из нескольких таблиц

Ваш WHERE alive=true фактически отфильтровывает запись для person_id = 2 . Используйте приведенный ниже запрос, нажмите условие WHERE alive=true состояние CASE как это можно заметить здесь. Смотрите свою измененную скрипку

 SELECT people.id, pe.alive_dogs_count, pe.alive_cats_count FROM people LEFT JOIN ( select person_id, COALESCE(SUM(case when pets.kind='dog' and alive = true then 1 else 0 end),0) as alive_dogs_count, COALESCE(SUM(case when pets.kind='cat' and alive = true then 1 else 0 end),0) as alive_cats_count from pets GROUP BY person_id ) pe on people.id = pe.person_id 

(ИЛИ) ваша версия

 SELECT people.id, COALESCE(SUM(case when pets.kind='dog' and alive = true then 1 else 0 end),0) as alive_dogs_count, COALESCE(SUM(case when pets.kind='cat' and alive = true then 1 else 0 end),0) as alive_cats_count FROM people LEFT JOIN pets on people.id = pets.person_id GROUP BY people.id;