Блог

Предыстория

У меня есть две таблицы с разными типами элементов обратной связи в MySQL. Я создал запрос для объединения этих таблиц с помощью FULL OUTER JOIN (который на самом деле записывается как два объединения и объединение в MySQL) и для подсчета некоторых средних оценок. Этот запрос, похоже, работает отлично:

   (SELECT name, AVG(l.overallQuality) AS avgLingQual,
    AVG(s.overallSatisfaction) AS avgSvcQual
  FROM feedback_linguistic AS l
  LEFT JOIN feedback_service AS s USING(name)
  GROUP BY name)
UNION ALL
  (SELECT name, AVG(l.overallQuality) AS avgLingQual, 
    AVG(s.overallSatisfaction) AS avgSvcQual
  FROM feedback_linguistic AS l
  RIGHT JOIN feedback_service AS s USING(name)
  WHERE l.id IS NULL
  GROUP BY name)
ORDER BY name;
 

(Это несколько упрощено для удобства чтения, но здесь это не имеет значения)

Проблема

Затем я попытался добавить фильтрацию по дате (т. Е. Учитываются только элементы обратной связи, созданные после определенной даты). С моими навыками SQL и проведенными исследованиями я смог придумать это:

   (SELECT name, AVG(l.overallQuality) AS avgLingQual,
    AVG(s.overallSatisfaction) AS avgSvcQual
  FROM feedback_linguistic AS l
  LEFT JOIN feedback_service AS s USING(name)
  WHERE (s.createdTime >= '" amp; date amp; "' OR s.createdTime IS NULL)
    AND (l.createdTime >= '" amp; date amp; "' OR l.createdTime IS NULL)
  GROUP BY name)
UNION ALL
  (SELECT name, AVG(l.overallQuality) AS avgLingQual, 
    AVG(s.overallSatisfaction) AS avgSvcQual
  FROM feedback_linguistic AS l
  RIGHT JOIN feedback_service AS s USING(name)
  WHERE l.id IS NULL
    AND (s.createdTime >= '" amp; date amp; "' OR s.createdTime IS NULL)
  GROUP BY name)
ORDER BY name;
 

Это почти работает: результаты, которые я получаю, выглядят примерно правильно. Однако отсутствует пара элементов обратной связи. Например, установив дату месяц назад, я насчитал отзывы для 21 разных людей в базе данных, но этот запрос возвращает только 19 человек. Хуже всего то, что я не могу найти никакого сходства между отсутствующими элементами.

Я делаю что-то не так в этом запросе? Я думаю, что WHERE предложение выполняет фильтрацию по дате после JOIN , и в идеале я, вероятно, делал бы это раньше. Опять же, я не знаю, вызывает ли это мою проблему, и я также понятия не имею, как написать этот запрос по-другому.

Я принял ответ Йохана, поскольку он проделал хорошую работу, объяснив мне этот материал, и ответ полезен даже в более общем смысле. Тем не менее, я подумал, что также опубликую первое решение, к которому я пришел. Он использовал подзапросы:

   (SELECT name, AVG(l.overallQuality) AS avgLingQual,
    AVG(s.overallSatisfaction) AS avgSvcQual
  FROM (
    SELECT * FROM feedback_linguistic WHERE createdTime >= '" amp; date amp; "'
  ) AS l
  LEFT JOIN (
    SELECT * FROM feedback_service WHERE createdTime >= '" amp; date amp; "'
  ) AS s USING(name)
  GROUP BY name)
UNION ALL
  (SELECT name, AVG(l.overallQuality) AS avgLingQual, 
    AVG(s.overallSatisfaction) AS avgSvcQual
  FROM (
    SELECT * FROM feedback_linguistic WHERE createdTime >= '" amp; date amp; "'
  ) AS l
  RIGHT JOIN (
    SELECT * FROM feedback_service WHERE createdTime >= '" amp; date amp; "'
  ) AS s USING(name)
  WHERE l.id IS NULL
  GROUP BY name)
ORDER BY name;
 

Результаты верны для этого запроса. Однако решение на самом деле не выглядит оптимальным, поскольку, по моему опыту, подзапросы иногда выполняются медленно. Опять же, я не проводил никакого анализа производительности, поэтому, возможно, использование подзапросов здесь не является узким местом. В любом случае это сработало достаточно быстро в моем приложении.

Полное внешнее соединение представляет собой комбинацию из 3 соединений:

1- внутреннее соединение между A и B
2- соединение с левым исключением между A и B
3- соединение с правым исключением между A и B

Обратите внимание, что комбинация внутреннего и левого исключающего соединения является левым внешним соединением, поэтому обычно вы переписываете запрос как left outer join right exclusion join .
Однако для целей отладки это может быть полезно для union всех 3 объединений и для добавления некоторого маркера относительно того, какое соединение что делает:

   /*inner join*/
  (SELECT
     'inner' as join_type 
     , COALESCE(s.name, l.name) as listname
     , AVG(l.overallQuality) AS avgLingQual
     , AVG(s.overallSatisfaction) AS avgSvcQual 
  FROM feedback_linguistic l 
  INNER JOIN feedback_service s ON (l.name = s.name) 
  WHERE (s.createdTime >= '" amp; date amp; "' OR s.createdTime IS NULL) 
    AND (l.createdTime >= '" amp; date amp; "' OR l.createdTime IS NULL) 
  GROUP BY l.name) 
UNION ALL
  (SELECT
     'left exclusion' as join_type 
     , COALESCE(s.name, l.name) as listname
     , AVG(l.overallQuality) AS avgLingQual
     , AVG(s.overallSatisfaction) AS avgSvcQual 
  FROM feedback_linguistic l 
  LEFT JOIN feedback_service s ON (l.name = s.name) 
  WHERE s.id IS NULL
    /*AND (s.createdTime >= '" amp; date amp; "' OR s.createdTime IS NULL) */
    AND (l.createdTime >= '" amp; date amp; "' OR l.createdTime IS NULL) 
  GROUP BY l.name) 
UNION ALL
  (SELECT 
     'right exclusion' as join_type
     , COALESCE(s.name, l.name) as listname
     , AVG(l.overallQuality) AS avgLingQual 
     , AVG(s.overallSatisfaction) AS avgSvcQual 
  FROM feedback_linguistic l 
  RIGHT JOIN feedback_service s ON (s.name = l.name) 
  WHERE l.id IS NULL
    AND (s.createdTime >= '" amp; date amp; "' OR s.createdTime IS NULL) 
    /*AND (l.createdTime >= '" amp; date amp; "' OR l.createdTime IS NULL) */
  GROUP BY s.name) 
ORDER BY listname; 
 

Я думаю, что предложение WHERE выполняет фильтрацию по дате после объединения, и в идеале я, вероятно, делал бы это раньше.

Если вы хотите выполнить фильтрацию раньше, поместите ее в предложение join .