SQL – множественное ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

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

forums с колоннами id, label, name
discussions с колоннами id, name, creation_date, open, forum_id, user_id
messages с колоннами id, content, date, updated_date, discussion_id, user_id

Я хочу получить каждый форум и для каждого форума получить количество обсуждений и количество сообщений. В настоящее время у меня есть следующий код:

SELECT 
    "Forum"."id", "Forum"."label", "Forum"."name",    
    COUNT("Discussions"."id") AS "nbDiscussions", 
    COUNT("Discussions->Messages"."id") AS "nbMessages"
FROM 
    "forums" AS "Forum"
LEFT OUTER JOIN 
    "discussions" AS "Discussions" ON "Forum"."id" = "Discussions"."forum_id"
LEFT OUTER JOIN 
    "messages" AS "Discussions->Messages" ON "Discussions"."id" = "Discussions->Messages"."discussion_id"
GROUP BY 
    "Forum"."id"
ORDER BY 
    "Forum"."id";

Это почти работает, за исключением того, что nbDiscussions имеет то же значение, что и nbMessages. Почему они одинаковые?

В результате получается, что nbDiscussions принял значение nbMessages.

sql postgresql

18.05.2024 16:19

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете исправить проблему со счетом в nbDiscussions, посчитав вместо этого:

COUNT(DISTINCT "Discussions"."id") AS "nbDiscussions"

Полный запрос:

SELECT 
    "Forum"."id", "Forum"."label", "Forum"."name",    
    COUNT(DISTINCT "Discussions"."id") AS "nbDiscussions", 
    COUNT("Discussions->Messages"."id") AS "nbMessages"
FROM "forums" AS "Forum"
LEFT JOIN "discussions" AS "Discussions"
    ON "Forum"."id" = "Discussions"."forum_id"
LEFT JOIN "messages" AS "Discussions->Messages"
    ON "Discussions"."id" = "Discussions->Messages"."discussion_id"
GROUP BY 
    "Forum"."id"
ORDER BY 
    "Forum"."id";

Здесь следует понимать, что окончательное соединение от discussions к messages приведет к созданию итоговой таблицы, в которой данное обсуждение id может появиться в нескольких записях, по одной для каждого сообщения. Использование DISTINCT позволяет избежать чрезмерного подсчета.

18.05.2024 16:33

При агрегировании всех (или большинства) строк значительно быстрее сначала агрегировать таблицы на стороне «многих», а затем объединять их. Это исключает неправильные (умноженные) подсчеты, и столбцы из основной таблицы вообще не нужно агрегировать (после того, как они не были умножены изначально):

SELECT f.id, f.label, f.name
     , COALESCE(d.ct_d, 0) AS ct_discussions  -- ①
     , COALESCE(d.ct_m, 0) AS ct_messages
FROM   forums f
LEFT   JOIN (
   SELECT d.forum_id
        , count(*) AS ct_d
        , sum(m.ct_m) AS ct_m
   FROM   discussions d
   LEFT   JOIN (
      SELECT m.discussion_id
           , count(*) AS ct_m
      FROM   messages m
      GROUP  BY 1
      ) m ON m.discussion_id = d.id
   GROUP  BY 1
   ) d ON d.forum_id = f.id
ORDER  BY f.id;

① Добавляйте COALESCE только в том случае, если могут быть форумы без обсуждений или обсуждения без сообщений, и вы хотите отображать 0 вместо нуля.

Видеть:

Лучше всего ли таким образом самостоятельно ссылаться на свои собственные ответы?

— 19.05.2024 01:39

@Tim Дело в том, что ссылки актуальны. Я уже давно отвечал на подобные случаи и постоянно обновлял некоторые канонические ответы. Нет смысла каждый раз это писать. (Если вы знаете лучшие ссылки, дайте нам знать.)

— 19.05.2024 03:24

18.05.2024 23:30