Эффективное Четырехходовое соединение в Oracle SQL
У меня есть две таблицы, Table1 и Table2,, к которым я хочу присоединиться. Каждая таблица имеет два уникальных ключа, назовем их Key1 и Key2. То, что я хочу сделать, - это левое соединение Table2 с Table1 , где любой из ключей соответствует любой из четырех возможных комбинаций:
- Таблица1.Ключ1 = Таблица 2.Key1
- Таблица1.Ключ1 = Таблица 2.Key2
- Таблица1.Ключ2 = Таблица 2.Key1
- Таблица1.Ключ2 = Таблица 2.Key2
Мой вопрос: есть ли есть ли какой-нибудь эффективный способ сделать это? Прямо сейчас я придумал что-то вроде этого, но это займет целую вечность, чтобы убежать.
CREATE TABLE NEW_TABLE AS
SELECT a.*,
CASE WHEN a.Key1 = b.Key1 THEN 1 ELSE 0 END AS match1,
CASE WHEN a.Key1 = c.Key2 THEN 1 ELSE 0 END AS match2,
CASE WHEN a.Key2 = b.Key1 THEN 1 ELSE 0 END AS match3,
CASE WHEN a.Key2 = c.Key2 THEN 1 ELSE 0 END AS match4
FROM Table1 a
LEFT JOIN (Select Key1 From Table2 Where Key1 is not null) b
on a.Key1 = b.Key1 or a.Key2 = b.Key1
LEFT JOIN (Select Key2 From Table2 Where Key2 is not null) c
on a.Key1 = c.Key2 or a.Key2 = c.Key2
;
Безнадежно, я знаю...
Редактировать: пример данных и желаемых результатов ниже:
Таблица 1:
Key1 Key2 Sales Revenue
qwer!@ dhfgfw 455 30005
asdf#$ dfg654 221 28711
edfr2# gg%%^f 213 31667
gthy!2 awd^&5 133 13345
rf$#22 34ffgg 655 41237
bhjk%g w3erff 122 10066
f&*yhj dffghj 126 11004
Таблица 2:
Key1 Key2
qwer!@ {null}
{null} dfg654
ffgww2 ppolkk
{null} gthy!2
jjjj33 l00kjl
nmnmnm 34ffgg
awd^&5 {null}
Желаемый Результат:
Key1 Key2 Sales Revenue match1 match2 match3 match4
qwer!@ dhfgfw 455 30005 1 0 0 0
asdf#$ dfg654 221 28711 0 0 0 1
edfr2# gg%%^f 213 31667 0 0 0 0
gthy!2 awd^&5 133 13345 1 0 1 0
rf$#22 34ffgg 655 41237 0 0 0 1
bhjk%g w3erff 122 10066 0 0 0 0
f&*yhj dffghj 126 11004 0 0 0 0
2 ответа:
Если вам нужны только комбинации строк, а не перестановки (т. е. если
table1.key
имеет то же значение, что иtable2.key1
иtable2.key2
, то возвращается только одна строка), то это должно работать:SELECT a.*, CASE WHEN a.Key1 = b.Key1 THEN 1 ELSE 0 END AS match1, CASE WHEN a.Key1 = b.Key2 THEN 1 ELSE 0 END AS match2, CASE WHEN a.Key2 = b.Key1 THEN 1 ELSE 0 END AS match3, CASE WHEN a.Key2 = b.Key2 THEN 1 ELSE 0 END AS match4 FROM Table1 a LEFT JOIN Table2 b on a.Key1 in (b.Key1, b.key2) or a.key2 in (b.key1, b.key2);
Подключая предоставленные данные, это решение действительно работает, но оно должно быть агрегировано, чтобы обеспечить результаты, которые вы ищете:
SELECT a.key1, a.key2, a.sales, a.revenue, MAX (CASE WHEN a.key1 = b.key1 THEN 1 ELSE 0 END) AS match1, MAX (CASE WHEN a.key1 = b.key2 THEN 1 ELSE 0 END) AS match2, MAX (CASE WHEN a.key2 = b.key1 THEN 1 ELSE 0 END) AS match3, MAX (CASE WHEN a.key2 = b.key2 THEN 1 ELSE 0 END) AS match4 FROM table1 a LEFT JOIN table2 b ON a.key1 IN (b.key1, b.key2) OR a.key2 IN (b.key1, b.key2) GROUP BY a.key1, a.key2, a.sales, a.revenue;
or
в условиях соединения могут возникнуть проблемы с производительностью. Я бы предложил вместо этого использоватьexists
:SELECT a.*, (case when exists (select 1 from table2 b where a.Key1 = b.Key1) then 1 else 0 end) as match1, (case when exists (select 1 from table2 b where a.Key1 = b.Key2) then 1 else 0 end) as match2, (case when exists (select 1 from table2 b where a.Key2 = b.Key1) then 1 else 0 end) as match3, (case when exists (select 1 from table2 b where a.Key2 = b.Key2) then 1 else 0 end) as match4 FROM Table1 a;
Отфильтровывание
NULL
На самом деле не имеет значения, потому чтоNULL
все равно провалит сравнение.Для лучшей производительности вам нужны индексы на
table2(key1)
иtable2(key2)
.