Два SQL LEFT JOINS дают неверный результат

Question

У меня есть 3 таблицы:

users(id, account_balance)
grocery(user_id, date, amount_paid)
fishmarket(user_id, date, amount_paid)

И то и другое fishmarket а также grocery Таблицы могут иметь несколько вхождений для одного и того же user_id с разными датами и выплаченными суммами или вообще не иметь ничего для любого данного пользователя. Когда я пытаюсь следующий запрос:

SELECT
     t1."id" AS "User ID",
     t1.account_balance AS "Account Balance",
     count(t2.user_id) AS "# of grocery visits",
     count(t3.user_id) AS "# of fishmarket visits"
FROM users t1
LEFT OUTER JOIN grocery t2 ON (t2.user_id=t1."id") 
LEFT OUTER JOIN fishmarket t3 ON (t3.user_id=t1."id") 
GROUP BY t1.account_balance,t1.id
ORDER BY t1.id

Это дает неверные результаты: "1", "12", "12",
Но когда я пытаюсь LEFT JOIN только к одной таблице это дает правильные результаты для любого grocery или же fishmarket посещения, которые "1", "3", "4",

Что я здесь не так делаю?
Я использую PostgreSQL 9.1.

Answer 1

Объединения обрабатываются слева направо (если в скобках не указано иное). если ты LEFT JOIN (или просто JOIN, аналогичный эффект) три бакалеи для одного пользователя вы получаете 3 ряда (1 х 3). Если вы затем присоединитесь к 4 рыбным рынкам для одного и того же пользователя, вы получите 12 (3 x 4) строк, умножив предыдущий счет в результате, не добавляя к нему, как вы, возможно, надеялись.
Тем самым увеличивается количество посещений продуктового и рыбного рынков.

Это должно работать так:

SELECT u.id
     , u.account_balance
     , g.grocery_visits
     , f.fishmarket_visits
FROM   users u
LEFT   JOIN (
   SELECT user_id, count(*) AS grocery_visits
   FROM   grocery
   GROUP  BY user_id
   ) g ON g.user_id = u.id
LEFT   JOIN (
   SELECT user_id, count(*) AS fishmarket_visits
   FROM   fishmarket
   GROUP  BY user_id
   ) f ON f.user_id = u.id
ORDER  BY u.id;

Чтобы найти агрегированные значения для одного или нескольких пользователей, коррелированные подзапросы, например, предоставленные @Vince, вполне подходят. Для всей таблицы или ее основных частей (гораздо) эффективнее агрегировать n-таблицы и объединиться с результатом один раз. Таким образом, нам также не нужен другой GROUP BY во внешнем запросе.

Answer 2

Для исходного запроса, если вы уберете группу, чтобы посмотреть на предварительно сгруппированный результат, вы поймете, почему были получены подсчеты, которые вы получали.

Возможно, следующий запрос с использованием подзапросов достигнет желаемого результата:

SELECT
 t1."id" AS "User ID",
 t1.account_balance AS "Account Balance",
 (SELECT count(*) FROM grocery     t2 ON (t2.user_id=t1."id")) AS "# of grocery visits",
 (SELECT count(*) FROM fishmarket  t3 ON (t3.user_id=t1."id")) AS "# of fishmarket visits"
FROM users t1
ORDER BY t1.id

Answer 3

Это связано с тем, что когда пользовательская таблица присоединяется к продуктовой таблице, сопоставляются 3 записи. Затем каждая из этих трех записей совпадает с 4 записями на рыбном рынке, что дает 12 записей. Вам нужны подзапросы, чтобы получить то, что вы ищете.

Answer 4

      SELECT
     t1."id" AS "User ID",
     t1.account_balance AS "Account Balance",
     Sum(Case When t2.user_id is null then 0 else 1 end) AS "# of grocery visits",
     Sum(Case When t3.user_id is null then 0 else 1 end) AS "# of fishmarket visits"
FROM users t1
LEFT OUTER JOIN grocery t2 ON (t2.user_id=t1."id") 
LEFT OUTER JOIN fishmarket t3 ON (t3.user_id=t1."id") 
GROUP BY t1.account_balance,t1.id
ORDER BY t1.id

Вышеупомянутое также позволит вам добавить дополнительные критерии по мере необходимости.