Два SQL LEFT JOINS производят неверный результат
У меня есть 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.
Ответы
Ответ 1
Соединения обрабатываются слева направо (если только в скобках не указано иное). Если вы LEFT JOIN
(или просто JOIN
, аналогичный эффект) три бакалейных товаров одному пользователю, вы получаете 3 строки (1 x 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
во внешнем запросе.
Ответ 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
Ответ 3
Это потому, что, когда таблица пользователя присоединяется к таблице продуктов, есть три записи. Затем каждая из этих трех записей совпадает с 4 записями на рыбном рынке, производя 12 записей. Вам нужны подзапросы, чтобы получить то, что вы ищете.