SQL-запрос возвращает одно и то же значение в каждом столбце

У меня возникла проблема с SQL-соединениями в запросе, который предназначен для запроса таблицы Post, присоединенной к таблице comment, click and vote и возвращающей статистику о каждом действии posts. Мой запрос ниже - это то, что я использую.

SELECT
    p.PostID, 
    p.Title, 
    CASE 
        WHEN COUNT(cm.CommentID) IS NULL THEN 0
        ELSE COUNT(cm.CommentID)
    END AS CommentCount, 
    CASE
        WHEN COUNT(cl.ClickID) IS NULL THEN 0 
        ELSE COUNT(cl.ClickID)
    END AS ClickCount, 
    CASE
        WHEN SUM(vt.Value) IS NULL THEN 0
        ELSE SUM(vt.Value)
    END AS VoteScore
FROM 
    Post p
    LEFT OUTER JOIN Comment cm ON p.PostID = cm.PostID
    LEFT OUTER JOIN Click cl ON p.PostID = cl.PostID
    LEFT OUTER JOIN Vote vt ON p.PostID = vt.PostID
GROUP BY
    p.PostID, 
    p.Title

Дает следующий результат 

| PostID | CommentCount | ClickCount | VoteScore |
|--------|--------------|------------|-----------|
| 41     |            60|          60|         60|
| 50     |          1683|        1683|       1683|

Это, я знаю, неправильно. Когда комментируют все соединения, кроме одного: 

SELECT
     p.PostID 
    ,p.Title 
    ,CASE 
        WHEN COUNT(cm.CommentID) IS NULL THEN 0
        ELSE COUNT(cm.CommentID)
     END AS CommentCount
/*
    ,CASE
        WHEN COUNT(cl.ClickID) IS NULL THEN 0 
        ELSE COUNT(cl.ClickID)
     END AS ClickCount
    ,CASE
        WHEN SUM(vt.Value) IS NULL THEN 0
        ELSE SUM(vt.Value)
     END AS VoteScore
 */
FROM 
    Post p
    LEFT OUTER JOIN Comment cm ON p.PostID = cm.PostID
/*
    LEFT OUTER JOIN Click cl ON p.PostID = cl.PostID
    LEFT OUTER JOIN Vote vt ON p.PostID = vt.PostID
 */
GROUP BY
    p.PostID, 
    p.Title

Я получаю 

| PostID | CommentCount |
|--------|--------------|
| 41     |             3|

Что верно. Есть идеи, что я сделал не так?

Спасибо.

5 4
sql sql-server left-join

5 ответов:

Возвращаемый результат является ожидаемым, поскольку запрос производит декартово (или полукартово) произведение. Запрос в основном говорит MySQL выполнять операции "перекрестного соединения" над строками, возвращенными из comment, click и vote.

Каждая строка, возвращенная из comment (для данного postid), сопоставляется каждой строке из click (для того же postid). И тогда каждая из строк в этом результате будет сопоставлена каждой строке из vote (для одного и того же postid).

Итак, для двоих строки из comment, три строки из click и четыре строки из vote, которые вернут в общей сложности 24 (=2x3x4) строки.

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

Есть несколько подходов, чтобы сделать это.

Коррелированные подзапросы в списке выбора

Если вам требуется только один агрегат (например, COUNT или SUM) из каждой из трех таблиц, вы можете удалить соединения и использовать коррелированные подзапросы в таблице. список выбора. Напишите запрос, который получает число для одного postid, например 

 SELECT COUNT(1)
   FROM comment cmt
  WHERE cmt.postid = ?

Затем оберните этот запрос в parens, сошлитесь на него в списке выбора другого запроса и замените вопросительный знак ссылкой на postid из таблицы, на которую ссылается внешний запрос.

SELECT p.postid
     , ( SELECT COUNT(1)
           FROM comment cmt
           WHERE cmt.postid = p.postid
       ) AS comment_count
  FROM post p

Повторите тот же шаблон, чтобы получить "подсчеты" из click и vote.

Недостатком этого подхода является то, что подзапрос в списке выбора будет выполняться для каждой строки. возвращается внешним запросом. Так что это может дорого стоить, если внешний запрос возвращает много строк. Если comment является большой таблицей, то для получения разумной производительности очень важно, чтобы на comment был доступен соответствующий индекс.

Предварительная агрегация во встроенных представлениях

Другой подход заключается в" предварительной агрегации " результатов встроенных представлений. Напишите запрос, который возвращает количество комментариев для postid. Например 
SELECT cmt.postid 
     , COUNT(1) 
  FROM comment cmt
 GROUP BY cmt.postid

Оберните этот запрос в parens и ссылайтесь на него в условие другого запроса, присвоить псевдоним. Встроенный запрос представления в основном занимает место таблицы во внешнем запросе.

SELECT p.postid
     , cm.postid
     , cm.comment_count
  FROM post p 
  LEFT
  JOIN ( SELECT cmt.postid 
              , COUNT(1) AS comment_count
           FROM comment cmt
          GROUP BY cmt.postid
       ) cm
    ON cm.postid = p.postid

И повторите тот же шаблон для click и vote. Хитрость здесь заключается в предложении GROUP BY в запросе встроенного представления, которое гарантирует, что оно не вернет никаких повторяющихся значений postid. И декартово произведение (перекрестное соединение) к этому не будет производить дубликатов.

Недостатком этого подхода является то, что производная таблица не индексируется. Так большое количество postid, это может быть дорого, чтобы выполнить соединение во внешнем запросе. (Более поздние версии MySQL частично устраняют этот недостаток, автоматически создавая соответствующий индекс.)

(мы можем обойти это ограничение, создав временную возможность с соответствующим индексом. Но этот подход требует дополнительных операторов SQL и не совсем подходит для одного оператора adhoc. Но для пакетной обработки больших наборов дополнительная сложность может стоить того для некоторого значительного увеличения производительности.

Свернуть декартово произведение на различные значения

Как совершенно другой подход, оставьте ваш запрос как есть, с операциями перекрестного соединения, и позвольте MySQL произвести декартово произведение. Затем агрегаты в списке выбора могут отфильтровать дубликаты. Это требует, чтобы у вас был столбец (или выражение, созданное) из comment, который уникален для каждой строки в комментарии для данного сообщения дан.

SELECT p.postid
     , COUNT(DISTINCT c.id) AS comment_count
  FROM post p
  LEFT
  JOIN comment c
    ON c.postid = p.postid
 GROUP BY p.postid
Большим недостатком этого подхода является то, что он имеет потенциал для получения огромного промежуточного результата, который затем "сворачивается" с помощью операции "Using filesort" (чтобы удовлетворить группу BY). И это может быть довольно дорого для больших наборов.

Это не исчерпывающий список всех возможных шаблонов запросов для достижения желаемого результата. Просто репрезентативная выборка.

3

Вы, вероятно, хотите что-то вроде этого: 

SELECT
    p.PostID, 
    p.Title,
   (SELECT COUNT(*) FROM Comment cm
    WHERE cm.PostID = p.PostID) AS CommentCount,
   (SELECT COUNT(*) FROM Click cl 
    WHERE  p.PostID = cl.PostID) AS ClickCount ,
   (SELECT SUM(vt.Value) FROM Vote vt 
    WHERE p.PostID = vt.PostID) AS VoteScore 
FROM 
    Post p
Проблема с вашим запросом заключается в том, что вторая и третья операции LEFT JOIN дублируют записи: после применения первой операции LEFT JOIN у вас есть, например, 3 записи для post having PostID = 41. Второй LEFT JOIN теперь присоединяется к этим 3 записям, поэтому PostID = 41 используется 3 раза во втором LEFT JOIN.

Если существует отношение 1: Много непосредственно между (Post, Comment), (Post, Click) и еще (Post, Vote), а потом ... приведенный выше запрос, скорее всего, даст вам то, что вы хотите.

2

Ваш запрос делает не то, что вы думаете. Когда вы соединяете и подсчитываете строки таким образом, вы создаете новый набор данных с x строками, а затем просто подсчитываете строки в этом наборе данных три раза. Следовательно, вы получаете один и тот же счет три раза.

То, что вы хотите сделать, это только подсчитать строки для комментариев и и кликов, где левое соединение находит данные в этих двух таблицах, например: 

SELECT
   p.PostID 
   ,p.Title 
   ,COUNT(CASE 
      WHEN cm.PostID IS NULL THEN 0
      ELSE 1
    END) AS CommentCount

  ,COUNT(CASE
     WHEN cl.PostID IS NULL THEN 0 
     ELSE 1
   END) AS ClickCount
  ,SUM(CASE
     WHEN vt.PostID IS NULL THEN 0
     ELSE ISNULL(vt.Value,0)
   END) AS VoteScore

FROM 
  Post p
  LEFT OUTER JOIN Comment cm ON p.PostID = cm.PostID
  LEFT OUTER JOIN Click cl ON p.PostID = cl.PostID
  LEFT OUTER JOIN Vote vt ON p.PostID = vt.PostID
GROUP BY
  p.PostID, 
  p.Title
1

Уже было объяснено, что не так с вашим запросом: скажем, 3 комментария, 5 кликов и 4 голоса (каждый голос со значением 1) для postid 41, Вы получаете 3x5x4=60 подсчетов для первого и второго выражения подсчета и 3x5x4x1=60 для суммы.

При работе с несколькими внешними соединениями в сочетании с агрегированием, вы должны не присоединять таблицы сначала и агрегировать позже, а сначала агрегировать и затем присоединять агрегаты: 

select
  p.postid, 
  p.title, 
  coalesce(cm.cnt, 0) as commentcount, 
  coalesce(cl.cnt, 0) as clickcount, 
  coalesce(vt.total, 0) as votescore 
from post p
left outer join (select postid, count(*) as cnt from comment group by postid) cm
  on cm.postid = p.postid
left outer join (select postid, count(*) as cnt from click group by postid) cl
  on cl.postid = p.postid
left outer join (select postid, sum(value) as total from vote group by postid) vt
  on vt.postid = p.postid;
1

COUNT подсчитывает ненулевые значения. Но при установке нулей в 0 они считаются. Измените свои подсчеты на сумму и переместите их за пределы случая, когда и я думаю, что это исправит проблему.

Например 

SELECT
    p.PostID, 
    p.Title, 
    SUM(CASE 
        WHEN cm.CommentID IS NULL THEN 0
        ELSE cm.CommentID
    END) AS CommentCount, 
    SUM(CASE
        WHEN cl.ClickID IS NULL THEN 0 
        ELSE cl.ClickID
    END) AS ClickCount, 
    SUM(CASE
        WHEN SUM(vt.Value IS NULL THEN 0
        ELSE SUM(vt.Value
    END) AS VoteScore
FROM 
    Post p
    LEFT OUTER JOIN Comment cm ON p.PostID = cm.PostID
    LEFT OUTER JOIN Click cl ON p.PostID = cl.PostID
    LEFT OUTER JOIN Vote vt ON p.PostID = vt.PostID
GROUP BY
    p.PostID, 
    p.Title
1