Полное внешнее соединение SQL или альтернативное решение

Я пытаюсь объединить несколько таблиц вместе, используя full outer join, это приближается к правильному результату, но есть несколько повторяющихся строк из-за предложений объединения. У меня есть несколько таблиц со столбцами id, date, value. Я ищу, чтобы получить таблицу с одной строкой для каждого идентификатора, пары дат, которая имеет все значения из каждой из таблиц.

Вот SQLFiddle, если вы хотите поиграть с ним.

Вот что у меня так далеко:


SELECT
  COALESCE(T1.ID, T2.ID, T3.ID, t4.id) AS ID,
  COALESCE(T1.event_dt, T2.event_dt, T3.event_dt, t4.event_dt) AS DATE,
  T1.AMT1, T2.AMT2, T3.AMT3, t4.AMT4
FROM T1
FULL OUTER JOIN T2
ON
  T2.id = T1.id
  AND T2.event_dt = T1.event_dt
FULL OUTER JOIN T3
ON
  T3.id = T1.id
  AND T3.event_dt = T1.event_dt
FULL OUTER JOIN T4
ON
  T4.id = T1.id
  AND T4.event_dt = T1.event_dt
ORDER BY ID, DATE

Это почти работает, но я получаю несколько повторяющихся строк, когда, например, у T4 есть ID, пара event_dt, которой нет в T1 (как и следовало ожидать, потому что это то, к чему я присоединяюсь). Например, я получу что-то вроде:


1   April, 06 2012 00:00:00+0000    (null)  2   (null)  (null)
1   April, 06 2012 00:00:00+0000    (null)  (null)  (null)  4
1   April, 06 2012 00:00:00+0000    (null)  (null)  3   (null)

When I'm looking to get: 

1   April, 06 2012 00:00:00+0000    (null)   2   3   4

Есть ли способ сгладить / объединить эти строки вместе, или есть лучший способ сделать это вообще?

Источник

4 ответа

Решение

(при условии, что OP хочет полностью симметричное внешнее 4-соединение)

WITH four AS (
        SELECT id, event_dt FROM t1
        UNION
        SELECT id, event_dt FROM t2
        UNION
        SELECT id, event_dt FROM t3
        UNION
        SELECT id, event_dt FROM t4
        )
SELECT f.id, f.event_dt
        , t1.amt1
        , t2.amt2
        , t3.amt3
        , t4.amt4
FROM four f
LEFT JOIN t1 ON t1.id = f.id AND t1.event_dt = f.event_dt
LEFT JOIN t2 ON t2.id = f.id AND t2.event_dt = f.event_dt
LEFT JOIN t3 ON t3.id = f.id AND t3.event_dt = f.event_dt
LEFT JOIN t4 ON t4.id = f.id AND t4.event_dt = f.event_dt
ORDER BY id, event_dt
        ;

Результат:

 id |  event_dt  | amt1 | amt2 | amt3 | amt4 
----+------------+------+------+------+------
  1 | 2012-04-01 |    1 |      |      |     
  1 | 2012-04-02 |    1 |      |    3 |     
  1 | 2012-04-03 |    1 |      |    3 |     
  1 | 2012-04-06 |      |    2 |    3 |    4
  1 | 2012-04-07 |      |    2 |      |     
  2 | 2012-04-01 |   40 |      |      |     
  2 | 2012-04-02 |      |      |    3 |     
  2 | 2012-04-03 |      |      |    3 |     
  2 | 2012-04-04 |   40 |      |      |     
(9 rows)

Кстати: после UNION четыре, LEFT JOINs будет делать то же самое, что и FULL JOINздесь (объединение четыре уже имеет все возможные пары {id, event_dt})

Источник

You could always use an aggregate around the amount колонки:

SELECT
  COALESCE(T1.ID, T2.ID, T3.ID, t4.id) AS ID,
  COALESCE(T1.event_dt, T2.event_dt, T3.event_dt, t4.event_dt) AS DATE,
  max(coalesce(T1.AMT1, 0)) AMT1,  -- use coalesce to replace the null with zero
  max(coalesce(T2.AMT2, 0)) AMT2, 
  max(coalesce(T3.AMT3, 0)) AMT3, 
  max(coalesce(t4.AMT4, 0)) AMT4
FROM T1
FULL OUTER JOIN T2
  ON T2.id = T1.id
  AND T2.event_dt = T1.event_dt
FULL OUTER JOIN T3
  ON T3.id = T1.id
  AND T3.event_dt = T1.event_dt
FULL OUTER JOIN T4
  ON T4.id = T1.id
  AND T4.event_dt = T1.event_dt
group by  COALESCE(T1.ID, T2.ID, T3.ID, t4.id), 
  COALESCE(T1.event_dt, T2.event_dt, T3.event_dt, t4.event_dt)
ORDER BY ID, DATE;

Посмотреть демо

Источник

Я думаю, что вы присоединитесь к Citeria - это просто не то, что вы действительно хотите. Этот должен сделать трюк:

SELECT
  COALESCE(T1.ID, T2.ID, T3.ID, t4.id) AS ID,
  COALESCE(T1.event_dt, T2.event_dt, T3.event_dt, t4.event_dt) AS DATE,
  T1.AMT1, T2.AMT2, T3.AMT3, t4.AMT4
FROM T1
FULL OUTER JOIN T2
ON
  T2.id = T1.id
  AND T2.event_dt = T1.event_dt
FULL OUTER JOIN T3
ON
  T3.id = coalesce(T1.id, T2.id)
  AND T3.event_dt = coalesce(T1.event_dt, T2.event_dt)
FULL OUTER JOIN T4
ON
  T4.id = coalesce(T1.id, T2.id, T3.id)
  AND T4.event_dt = coalesce(T1.event_dt, T2.event_dt, T3.event_dt)
ORDER BY ID, DATE

SQL-Fiddle здесь дает желаемый результат для 2012-04-06.

Источник

Ловите NULL, перераспределяя их нулями, затем найдите значение MAX в каждом столбце.

SELECT
  COALESCE(T1.ID, T2.ID, T3.ID, t4.id) AS ID,
  COALESCE(T1.event_dt, T2.event_dt, T3.event_dt, t4.event_dt) AS DATE,
  max( coalesce(T1.AMT1,0)) as amt1
, max( coalesce(T2.AMT2,0)) as amt2
, max( coalesce(T3.AMT3,0)) as amt3
, max( coalesce(t4.AMT4,0)) as amt4
FROM T1
FULL OUTER JOIN T2
ON
  T2.id = T1.id
  AND T2.event_dt = T1.event_dt
FULL OUTER JOIN T3
ON
  T3.id = T1.id
  AND T3.event_dt = T1.event_dt
FULL OUTER JOIN T4
ON
  T4.id = T1.id
  AND T4.event_dt = T1.event_dt
group by   COALESCE(T1.ID, T2.ID, T3.ID, t4.id),
  COALESCE(T1.event_dt, T2.event_dt, T3.event_dt, t4.event_dt)
ORDER BY ID, DATE

Вот скрипка

Источник
Другие вопросы по тегам