Блог

Главная - Вопросы по программированию - Боковое соединение нескольких подмножеств таблицы

Боковое соединение нескольких подмножеств таблицы

#sql #sum #left-join #inner-join #aggregate-functions

#sql #сумма #левое соединение #внутреннее соединение #агрегатные функции

Вопрос:

Я пытаюсь выполнить полное соединение между подмножеством таблицы и каждым другим подмножеством одной и той же таблицы, даже если некоторые строки отсутствуют. У меня есть таблица вида:

 CREATE TABLE #Test ( 
         SiteID      int NOT NULL
       , DayOfWeekID tinyint NOT NULL
       , Quantity    float NOT NULL );

Это заполняется количеством продаж на каждом сайте за каждый день недели. Затем я использую SiteID = -1 в качестве агрегированного (среднего) значения, и я ищу ошибку между каждым сайтом и этим средним значением, например

 CREATE TABLE #TestError ( 
         SiteID int NOT NULL
       , Error  float NOT NULL );

INSERT INTO #TestError
   ( SiteID
   , Error
   ) 
SELECT t1.SiteID
 , SUM(ABS(isnull(t1.Quantity, 0) - t2.Quantity))
  FROM #Test t1
       JOIN #Test t2
           ON t1.DayOfWeekID = t2.DayOfWeekID
              AND t2.SiteID = -1
  GROUP BY t1.SiteID;

Это работает нормально при условии, что для каждого идентификатора сайта присутствуют все семь дней недели. Однако, если на сайте нет продаж за данный день недели, то эта строка будет отсутствовать при суммировании, и сообщаемая ошибка будет ниже, чем должна быть. С другой стороны, если я использую ПРАВОЕ СОЕДИНЕНИЕ вместо СОЕДИНЕНИЯ, я не могу группировать по t1.SiteID, поскольку это значение будет равно НУЛЮ в сгенерированной строке. Очевидный ответ заключается в том, что мне нужно убедиться, что у каждого идентификатора сайта есть все семь дней, путем создания экземпляра #Test как перекрестного соединения между таблицей идентификаторов сайтов и таблицей, содержащей идентификаторы DayOfWeekIDs 1-7, с начальными значениями, равными нулю, а затем обновления его реальными значениями там, где они существуют. Но это кажется неэффективным, и в более широком смысле это то, что возникало у меня раньше, и я хотел бы понять «правильный» способ SQL для написания такого соединения. Спасибо!

Ответ №1:

Если на сайте -1 указаны все дни недели, вы можете cross join добавить в его записи список отдельных сайтов, затем привести таблицу с помощью a left join и aggregate:

 select s.siteid, sum(abs(coalesce(t1.quantity, 0) - t0.quantity)) as diff
from #test t0
cross join (select distinct siteid from #test) s
left join #test t1 on t1.dayofweekid = t0.dayofweekid and t1.siteid = s.siteid
where t0.siteid = -1
group by s.siteid