ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятнияная задачка: full equi join.
25 сообщений из 47, страница 1 из 2
  1  все
Пятнияная задачка: full equi join.
    #39373741
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Формулировка проста: написать запрос эквивалентный
Код: plsql
1.
select key, t1.value value_1, t2.value value_2 from t1 full join t2 using (key) order by 1, 2, 3

, но без использования union (и любых других операций над множествами) и ansi.

Уровень 1. Можно использовать (native) outer joins.
Уровень 2. Можно использовать только inner joins.

Ясное дело, чем меньше сканируются таблицы (в идеале по разу каждая), тем лучше.

 
Данные для тестирования
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
create table t1 as
select 0 key, 'OO' value from dual
union all select 0 key, 'A1' value from dual
union all select 1 key, 'B1' value from dual
union all select 2 key, 'C1' value from dual
union all select 3 key, 'D1' value from dual
union all select 4 key, 'E1' value from dual;

create table t2 as
select 0 key, 'A2' value from dual
union all select 0 key, 'Z2' value from dual
union all select 1 key, 'B2' value from dual
union all select 5 key, 'F2' value from dual
union all select 10 key, 'X2' value from dual;


Я на 100% уверен, что подобное упражнение не ново, но быстро нагуглить не удалось.
Для обоих случаев мои запросы работают на 8.1.7.4, но не вижу причин, чтоб они не работали на 8, 7 или даже 6-й версии.

PS. На магию данных завязываться не стоит. Подход должен работать для любых дублей и ключей.
...
Рейтинг: 0 / 0
23.12.2016, 13:50
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39373803
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
dbms_photoshop,

 
извини, не понял зачем такие ограничения... задачу по твоим условиям я не решал. смысла не вижу так извращаться.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
WITH t1 AS
 (SELECT 0 key, 'OO' VALUE
    FROM dual
  UNION ALL
  SELECT 0 key, 'A1' VALUE
    FROM dual
  UNION ALL
  SELECT 1 key, 'B1' VALUE
    FROM dual
  UNION ALL
  SELECT 2 key, 'C1' VALUE
    FROM dual
  UNION ALL
  SELECT 3 key, 'D1' VALUE
    FROM dual
  UNION ALL
  SELECT 4 key, 'E1' VALUE FROM dual),
t2 AS
 (SELECT 0 key, 'A2' VALUE
    FROM dual
  UNION ALL
  SELECT 0 key, 'Z2' VALUE
    FROM dual
  UNION ALL
  SELECT 1 key, 'B2' VALUE
    FROM dual
  UNION ALL
  SELECT 5 key, 'F2' VALUE
    FROM dual
  UNION ALL
  SELECT 10 key, 'X2' VALUE FROM dual),
t3 AS
 (SELECT t1.key FROM t1
  UNION
  SELECT t2.key FROM t2)
SELECT t3.key, t1.VALUE value_1,t2.VALUE value_2
  FROM t3, t2, t1
 WHERE t3.key = t2.key(+)
   AND t3.key = t1.key(+)

...
Рейтинг: 0 / 0
23.12.2016, 14:48
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39373837
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Vintdbms_photoshop,

 
извини, не понял зачем такие ограничения... задачу по твоим условиям я не решал. смысла не вижу так извращаться.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
WITH t1 AS
 (SELECT 0 key, 'OO' VALUE
    FROM dual
  UNION ALL
  SELECT 0 key, 'A1' VALUE
    FROM dual
  UNION ALL
  SELECT 1 key, 'B1' VALUE
    FROM dual
  UNION ALL
  SELECT 2 key, 'C1' VALUE
    FROM dual
  UNION ALL
  SELECT 3 key, 'D1' VALUE
    FROM dual
  UNION ALL
  SELECT 4 key, 'E1' VALUE FROM dual),
t2 AS
 (SELECT 0 key, 'A2' VALUE
    FROM dual
  UNION ALL
  SELECT 0 key, 'Z2' VALUE
    FROM dual
  UNION ALL
  SELECT 1 key, 'B2' VALUE
    FROM dual
  UNION ALL
  SELECT 5 key, 'F2' VALUE
    FROM dual
  UNION ALL
  SELECT 10 key, 'X2' VALUE FROM dual),
t3 AS
 (SELECT t1.key FROM t1
  UNION
  SELECT t2.key FROM t2)
SELECT t3.key, t1.VALUE value_1,t2.VALUE value_2
  FROM t3, t2, t1
 WHERE t3.key = t2.key(+)
   AND t3.key = t1.key(+)

А почему ты не написал просто классический вариант раз уж "неохота извращаться"? :))
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select key,
       t1.value value_1,
       t2.value value_2
  from t1 left join t2 using (key)
union all
select key,
       null,
       value
  from t2
 where not exists
          (select null
             from t1
            where t1.key = t2.key)
order by 1,
         2,
         3


 
Впрочем, аналог твоего был ответом на уровень 1.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select keys.key,
       value_1,
       t2.value value_2
  from (select keys.key, t1.value value_1
          from (select distinct nvl(x.key, t2.key) key
                  from (select x.type, t1.key
                          from (select rownum type from (select * from dual group by rollup(dummy))) x,
                               t1
                         where nvl2(t1.key(+), 1, 1) = x.type) x,
                       t2
                 where nvl2(t2.key(+), 2, 2) = x.type) keys,
               t1
         where keys.key = t1.key(+)) keys,
       t2
 where keys.key = t2.key(+)
order by 1,
         2,
         3

...
Рейтинг: 0 / 0
23.12.2016, 15:16
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39373866
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
dbms_photoshop,
класический вариант на 6 лямах записей дает 15 сек. мой 12. твой 5-7. с флашем лень замерять. классический никогда не использовал.
...
Рейтинг: 0 / 0
23.12.2016, 15:38
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39373872
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Еще для уровня 1 можно решить за один проход каждой из таблиц, но при условии, что ключи уникальны .

Код: plaintext
1.
2.
delete from t1 where key = 0 and value = 'OO'; 
delete from t1 where key = 3 and value = 'ZZ';
delete from t2 where key = 0 and value = 'Z2';

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select max(nvl(t1.key, t2.key)) key,
       max(t1.value) value_1,
       max(t2.value) value_2
  from (select key,
               value,
               type
          from t1,
               (select rownum type from (select * from dual group by rollup(dummy))) x
         where nvl2(t1.key(+), 1, 1) = x.type) t1,
       t2
 where case
          when t1.type = 1 and t1.key = t2.key(+) or t1.type = 2 then 1
       end = 1
group by t2.key, decode(t2.key, null, t1.key)
order by 1,
         2,
         3
...
Рейтинг: 0 / 0
23.12.2016, 15:41
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39373886
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Уточнение для уровня 2.
Можно предлагать как варианты работающие с уникальными ключами, так и с любыми ключами.
Для тестирования произвольного случая, к исходным данным можно добавить.
Код: plaintext
insert into t1 values (3, 'ZZ')
...
Рейтинг: 0 / 0
23.12.2016, 15:49
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39374086
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Ну чо, идеи есть? Подожду до след недели.
...
Рейтинг: 0 / 0
23.12.2016, 18:41
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39374297
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopНу чо, идеи есть?Не хотелось бы тебя расстраивать, но ты слегка оторван от жизни...

P.S. И смотри там, не подавись сигаретой.
...
Рейтинг: 0 / 0
24.12.2016, 08:38
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39374310
ORA__SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORA__SQL
Участник
dbms_photoshopНу чо, идеи есть? Подожду до след недели.
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
select nvl(t1.key, t2.key) as key, 
       t1.value, 
       t2.value
from 
    (
        select t1.join_key_1, 
               nvl2(x1.dummy, to_char(t1.key), 'B') as join_key_2,
               nvl2(x1.dummy, t1.key, null) as key,
               nvl2(x1.dummy, t1.value, null) as value
          from (select 'A' join_key_1, t1.* from t1) t1,
               (select dummy from dual group by rollup(dummy)) x1
        group by t1.join_key_1, nvl2(x1.dummy, to_char(t1.key), 'B'), nvl2(x1.dummy, t1.key, null), nvl2(x1.dummy, t1.value, null)
    ) t1,
    (    
        select t2.join_key_1, 
               nvl2(x2.dummy, to_char(t2.key), 'A') as join_key_2,
               nvl2(x2.dummy, t2.key, null) as key,
               nvl2(x2.dummy, t2.value, null) as value
          from (select 'B' join_key_1, t2.* from t2) t2,
               (select dummy from dual group by rollup(dummy)) x2
        group by t2.join_key_1, nvl2(x2.dummy, to_char(t2.key), 'A'), nvl2(x2.dummy, t2.key, null), nvl2(x2.dummy, t2.value, null)
    ) t2
where (     t1.key = t2.key 
        or (t1.join_key_2 = 'B' and t2.join_key_1 = 'B' and not exists (select null from t1 where t1.key = t2.key)) 
        or (t1.join_key_1 = 'A' and t2.join_key_2 = 'A' and not exists (select null from t2 where t2.key = t1.key))
      )
      and nvl(t1.key, t2.key) is not null
order by 1, 2, 3


...
Рейтинг: 0 / 0
24.12.2016, 10:15
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375089
Это не фулл джойн
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Это не фулл джойн
Гость
ORA__SQL, магия данных

Код: plsql
1.
2.
delete from t1;
select * from t1 full join t2 on t1.key = t2.key;
...
Рейтинг: 0 / 0
26.12.2016, 10:50
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375133
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
dbms_photoshopЕще для уровня 1 можно решить за один проход каждой из таблиц, но при условии, что ключи уникальны .
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
WITH t1 AS
 (
  SELECT 0 key, 'A1' VALUE FROM dual  UNION ALL
  SELECT 1 key, 'B1' VALUE FROM dual  UNION ALL
  SELECT 2 key, 'C1' VALUE FROM dual  UNION ALL
  SELECT 3 key, 'D1' VALUE FROM dual  UNION ALL
  SELECT 4 key, 'E1' VALUE FROM dual
  ),
t2 AS
 (
  SELECT 0 key, 'Z2' VALUE    FROM dual  UNION ALL
  SELECT 1 key, 'B2' VALUE    FROM dual  UNION ALL
  SELECT 5 key, 'F2' VALUE    FROM dual  UNION ALL
  SELECT 10 key, 'X2' VALUE FROM dual)
select key,
       max(case when t=1 then value end) value_1,
       max(case when t=2 then value end) value_2
from(
select key, value, 1 t from t1 union all
select key, value, 2 t from t2
)
group by key
order by key
...
Рейтинг: 0 / 0
26.12.2016, 11:28
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375316
union all detected
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
union all detected
Гость
andreymx,

авторбез использования union (и любых других операций над множествами)
...
Рейтинг: 0 / 0
26.12.2016, 14:25
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375382
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
union all detectedandreymx,

авторбез использования union (и любых других операций над множествами)я могу убрать юнион и насвинячить прочей хрени, но это решение будет явно хуже
...
Рейтинг: 0 / 0
26.12.2016, 15:38
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375430
Plohoy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Plohoy
Гость
Без union, left join (зато работает)

WITH t1 AS
(SELECT 0 key, 'OO' VALUE
FROM dual
UNION ALL
SELECT 0 key, 'A1' VALUE
FROM dual
UNION ALL
SELECT 1 key, 'B1' VALUE
FROM dual
UNION ALL
SELECT 2 key, 'C1' VALUE
FROM dual
UNION ALL
SELECT 3 key, 'D1' VALUE
FROM dual
UNION ALL
SELECT 4 key, 'E1' VALUE FROM dual),
t2 AS
(SELECT 0 key, 'A2' VALUE
FROM dual
UNION ALL
SELECT 0 key, 'Z2' VALUE
FROM dual
UNION ALL
SELECT 1 key, 'B2' VALUE
FROM dual
UNION ALL
SELECT 5 key, 'F2' VALUE
FROM dual
UNION ALL
SELECT 10 key, 'X2' VALUE FROM dual)
, t3 as (
select p1.*
, (select count(1) from t2 p2 where p2.key = p1.key and rownum = 1) p_exist
from t1 p1
)
, t4 as (
select p1.*
, (select count(1) from t1 p2 where p2.key = p1.key and rownum = 1) p_exist
from t2 p1
)
select distinct
case when pp1.p_exist = 1 and pp2.p_exist = 1
then pp1.key
when pp1.p_exist = 1 and pp2.p_exist = 0
then pp2.key
else pp1.key
end key
, case when pp1.p_exist = 1 and pp2.p_exist = 1
then pp1.value
when pp1.p_exist = 1 and pp2.p_exist = 0
then null
else pp1.value
end value_1
, case when pp1.p_exist = 1 and pp2.p_exist = 1
then pp2.value
when pp1.p_exist = 1 and pp2.p_exist = 0
then pp2.value
else null
end value_2
from t3 pp1
, t4 pp2
where (pp1.key = pp2.key and pp1.p_exist = 1 and pp2.p_exist = 1)
or (pp1.p_exist = 0)
or (pp2.p_exist = 0)
...
Рейтинг: 0 / 0
26.12.2016, 16:35
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375441
Это не фулл джойн
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Это не фулл джойн
Гость
Plohoy, магия данных, как и у ORA__SQL

Если один из наборов пуст, возвращает пустой результат, в то время, как full join вернет другой набор.
...
Рейтинг: 0 / 0
26.12.2016, 16:46
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375444
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
union all detectedandreymx,

авторбез использования union (и любых других операций над множествами)я могу убрать юнион и насвинячить прочей хрени, но это решение будет явно хуже
...
Рейтинг: 0 / 0
26.12.2016, 16:47
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375447
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Похоже, сайт никогда не починят - чтобы хождение по истории не создавало повторных ответов в теме
...
Рейтинг: 0 / 0
26.12.2016, 16:48
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375496
Nexxxt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nexxxt
Гость
Уровень 1:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
with t1 as (
select 0 key, 'OO' value from dual
union all select 0 key, 'A1' value from dual
union all select 1 key, 'B1' value from dual
union all select 2 key, 'C1' value from dual
union all select 3 key, 'D1' value from dual
union all select 4 key, 'E1' value from dual),
t2 as (
select 0 key, 'A2' value from dual
union all select 0 key, 'Z2' value from dual
union all select 1 key, 'B2' value from dual
union all select 5 key, 'F2' value from dual
union all select 10 key, 'X2' value from dual)
SELECT T1.KEY,T1.VALUE,
       T2.KEY,T2.VALUE
  FROM (SELECT MINV + LEVEL - 1 KEY 
          FROM (SELECT LEAST(MIN(T1.KEY),MIN(T2.KEY)) MINV,
                       GREATEST(MAX(T1.KEY),MAX(T2.KEY)) MAXV
                  FROM T1,T2)
        CONNECT BY MINV + LEVEL - 1 <= MAXV) A
  LEFT JOIN T1 
    ON T1.KEY = A.KEY
  LEFT JOIN T2
    ON T2.KEY = A.KEY  
 WHERE NOT (T1.KEY IS NULL AND T2.KEY IS NULL)
...
Рейтинг: 0 / 0
26.12.2016, 17:57
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375506
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Nexxxt
Код: plsql
1.
FROM T1,T2)

20049702
...
Рейтинг: 0 / 0
26.12.2016, 18:14
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375512
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
не глядя другие варианты:
 

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with 
  mkey as (select greatest(
                     (select max(key) from t1)
                    ,(select max(key) from t2)
                 ) max_key
           from dual
          )
,g as (select level - 1 as key from mkey connect by level<= max_key+1)
select g.key, t1.value value_1, t2.value value_2 
from g, t1, t2
where 
      g.key = t1.key(+)
  and g.key = t2.key(+)
  and (t1.key is not null or t2.key is not null)
order by 1, 2, 3
/


...
Рейтинг: 0 / 0
26.12.2016, 18:23
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375523
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
а ежели кей - строка?
а ежели кей - от -триллиона до плюс триллиона?
и сканировать таблицы предлагали один раз
...
Рейтинг: 0 / 0
26.12.2016, 18:41
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375528
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
фикс для пустых наборов:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with 
  mkey as (select greatest(
                     (select nvl(max(key),0) from t1)
                    ,(select nvl(max(key),0) from t2)
                 ) max_key
           from dual
          )
,g as (select level - 1 as key from mkey connect by level<= max_key+1)
select g.key, 
       t1.value as value_1, 
       t2.value value_2 
from g, t1, t2
where 
      g.key = t1.key(+)
  and g.key = t2.key(+)
  and (t1.key is not null or t2.key is not null)
order by 1, 2, 3

...
Рейтинг: 0 / 0
26.12.2016, 18:49
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375537
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
 
уровень 2 - на иннер джойнах
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
with 
  mkey as (select greatest(
                     (select nvl(max(key),0) from t1)
                    ,(select nvl(max(key),0) from t2)
                 ) max_key
           from dual
          )
,g as (select level - 1 as key from mkey connect by level<= max_key+1)
,v1 as (select key, row_number()over(partition by key order by value) rn1, value as value_1 from t1)
,v2 as (select key, row_number()over(partition by key order by value) rn2, value as value_2 from t2)
,gg as (select key
               ,nvl((select max(rn1) from v1 where v1.key=g.key),1) cnt1 
               ,nvl((select max(rn2) from v2 where v2.key=g.key),1) cnt2 
         from g
        )
select *
from (
   select gg.key, 
          (select v1.value_1 from v1 where v1.key=gg.key and v1.rn1 = g1.key) as value_1, 
          (select v2.value_2 from v2 where v2.key=gg.key and v2.rn2 = g2.key) as value_2
   from gg, g g1, g g2
   where 
         g1.key between 1 and gg.cnt1
     and g2.key between 1 and gg.cnt2
)
where value_1 is not null or value_2 is not null
order by 1, 2, 3
/

...
Рейтинг: 0 / 0
26.12.2016, 19:06
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375538
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Elicdbms_photoshopНу чо, идеи есть?Не хотелось бы тебя расстраивать, но ты слегка оторван от жизниСоединения, работа с наборами данных и реляционная алгебра уже не в тренде?
А что сейчас модно? NoSQL?
...
Рейтинг: 0 / 0
26.12.2016, 19:06
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375542
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Это не фулл джойнORA__SQL, магия данных

Код: plsql
1.
2.
delete from t1;
select * from t1 full join t2 on t1.key = t2.key;

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

 
Если ключ уникален
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
select key, max(value_1) value1, max(value_2) value_2
  from (select rownum rn,
               t1.type type_1,
               t1.key key_1,
               t1.value value_1,
               t2.type type_2,
               t2.key key_2,
               t2.value value_2,
               nvl(t1.key, t2.key) key
          from (select type, key, value
                  from (select rownum type from (select * from dual group by rollup(dummy))) x,
                       t1
                 where nvl2(t1.key(+), 1, 1) = x.type) t1,
               (select type, key, value
                  from (select rownum type from (select * from dual group by rollup(dummy))) x,
                       t2
                 where nvl2(t2.key(+), 1, 1) = x.type) t2
         where (t1.type = t2.type and t1.type = 1 and t2.type = 1 and
               t1.key = t2.key)
            or (t1.type <> 1 and t2.type = 1)
            or (t2.type <> 1 and t1.type = 1))
 group by decode(type_1, type_2, rn, null), key
having count(*) = 1 or decode(type_1, type_2, rn, null) is not null
 order by 1, 2, 3


 
Без учета уникальностиПодход аналогичный ORA__SQL, вместо exists используется аналитика.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
select nvl(key_1, key_2) key, value_1, value_2
  from (select t1.type type_1,
               t1.key key_1,
               t1.value value_1,
               t2.type type_2,
               t2.key key_2,
               t2.value value_2,
               count(decode(t1.type, t2.type, null, t2.key)) over(partition by nvl(t1.key, t2.key)) c1,
               count(decode(t1.type, t2.type, null, t1.key)) over(partition by nvl(t1.key, t2.key)) c2
          from (select type, key, value
                  from (select rownum type from (select * from dual group by rollup(dummy))) x,
                       t1
                 where nvl2(t1.key(+), 1, 1) = x.type) t1,
               (select type, key, value
                  from (select rownum type from (select * from dual group by rollup(dummy))) x,
                       t2
                 where nvl2(t2.key(+), 1, 1) = x.type) t2
         where (t1.type = t2.type and t1.type = 1 and t2.type = 1 and
               t1.key = t2.key)
            or (t1.type <> 1 and t2.type = 1)
            or (t2.type <> 1 and t1.type = 1)
         order by type_1, type_2, nvl(key_1, key_2))
 where type_1 = type_2
    or key_1 is not null
   and c1 = 0
    or key_2 is not null
   and c2 = 0
 order by 1, 2, 3


 
Всего одно внутреннее соединение
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
select nvl(key_1, key_2) key, value_1, value_2
  from (select t1.type type_1,
               t1.key key_1,
               t1.value value_1,
               t2.type type_2,
               t2.key key_2,
               t2.value value_2,
               count(decode(t1.type, t2.type, null, t2.key)) over(partition by nvl(t1.key, t2.key)) c1,
               count(decode(t1.type, t2.type, null, t1.key)) over(partition by nvl(t1.key, t2.key)) c2
          from (select grouping_id(rowid) + 1 as type,
                       decode(grouping_id(rowid), 0, max(key)) key,
                       decode(grouping_id(rowid), 0, max(value)) value
                  from (select * from t1)
                 group by grouping sets(rowid,())) t1,
               (select grouping_id(rowid) + 1 as type,
                       decode(grouping_id(rowid), 0, max(key)) key,
                       decode(grouping_id(rowid), 0, max(value)) value
                  from (select * from t2)
                 group by grouping sets(rowid,())) t2
         where (t1.type = t2.type and t1.type = 1 and t2.type = 1 and
               t1.key = t2.key)
            or (t1.type <> 1 and t2.type = 1)
            or (t2.type <> 1 and t1.type = 1)
         order by type_1, type_2, nvl(key_1, key_2))
 where type_1 = type_2
    or key_1 is not null and c1 = 0
    or key_2 is not null and c2 = 0
 order by 1, 2, 3

Надо еще включить фикс для пустоты. :)
...
Рейтинг: 0 / 0
26.12.2016, 19:12
| Ответить | Цитировать | Написать  
25 сообщений из 47, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятнияная задачка: full equi join.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=182&tid=1886689]:
 0ms
get settings:
 10ms
get forum list:
 21ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 68ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 58ms
get tp. blocked users:
 1ms
others: 207ms
total:  386ms
созд. / загр.: 386ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]