ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятнияная задачка: full equi join.
47 сообщений из 47, показаны все 2 страниц
  все  
Пятнияная задачка: 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
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375564
Nexxxt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nexxxt
Гость
Elic,
Код: 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.
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((SELECT NVL(MIN(T1.KEY),0) FROM T1),(SELECT NVL(MIN(T2.KEY),0) FROM T2)) MINV,
                       GREATEST((SELECT NVL(MAX(T1.KEY),0) FROM T1),(SELECT NVL(MAX(T2.KEY),0) FROM T2)) MAXV
                  FROM DUAL)
        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, 19:38
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375567
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andreymxunion all detectedandreymx,

пропущено...
я могу убрать юнион и насвинячить прочей хрени, но это решение будет явно хужеЕсли говорить про перфоманс - то решение Винта предпочтительнее ибо универсальнее, а перфоманс такой же.

Код: plaintext
1.
2.
3.
create table t1 as select rownum key, lpad(' ', 100, ' ') value from dual connect by rownum <= 5e5;
create table t2 as select rownum+100 key, lpad(' ', 100, ' ') value from dual connect by rownum <= 5e5;
exec dbms_stats.gather_table_stats(user,'t1');
exec dbms_stats.gather_table_stats(user,'t2');

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
alter system flush buffer_cache;
alter session set statistics_level = all;
set timing on
create table t3 as
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;
select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'));
select count(*) from t3;

Такой же тест выполнен для его запроса (без сортировки).

Код: 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.
-----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
-----------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |      1 |        |      0 |00:00:08.88 |   33994 |  21908 |  21217 |
|   1 |  LOAD AS SELECT        |      |      1 |        |      0 |00:00:08.88 |   33994 |  21908 |  21217 |
|   2 |   HASH GROUP BY        |      |      1 |    500K|    500K|00:00:06.37 |   15394 |  21898 |   6510 |
|   3 |    VIEW                |      |      1 |   1000K|   1000K|00:00:02.98 |   15394 |  15388 |   0 |
|   4 |     UNION-ALL          |      |      1 |        |   1000K|00:00:02.77 |   15394 |  15388 |   0 |
|   5 |      TABLE ACCESS FULL | T1   |      1 |    500K|    500K|00:00:00.87 |    7697 |   7694 |   0 |
|   6 |      TABLE ACCESS FULL | T2   |      1 |    500K|    500K|00:00:01.48 |    7697 |   7694 |   0 |
-----------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT  |      |      1 |        |      0 |00:00:08.43 |   49389 |  30784 |  14707 |
|   1 |  LOAD AS SELECT         |      |      1 |        |      0 |00:00:08.43 |   49389 |  30784 |  14707 |
|*  2 |   HASH JOIN RIGHT OUTER |      |      1 |   1000K|    500K|00:00:06.31 |   30788 |  30774 |   0 |
|   3 |    TABLE ACCESS FULL    | T1   |      1 |    500K|    500K|00:00:00.66 |    7697 |   7694 |   0 |
|*  4 |    HASH JOIN OUTER      |      |      1 |   1000K|    500K|00:00:04.72 |   23091 |  23080 |   0 |
|   5 |     VIEW                |      |      1 |   1000K|    500K|00:00:02.12 |   15394 |  15387 |   0 |
|   6 |      SORT UNIQUE        |      |      1 |   1000K|    500K|00:00:02.04 |   15394 |  15387 |   0 |
|   7 |       UNION-ALL         |      |      1 |        |   1000K|00:00:01.40 |   15394 |  15387 |   0 |
|   8 |        TABLE ACCESS FULL| T1   |      1 |    500K|    500K|00:00:00.61 |    7697 |   7693 |   0 |
|   9 |        TABLE ACCESS FULL| T2   |      1 |    500K|    500K|00:00:00.44 |    7697 |   7694 |   0 |
|  10 |     TABLE ACCESS FULL   | T2   |      1 |    500K|    500K|00:00:01.83 |    7697 |   7693 |   0 |
------------------------------------------------------------------------------------------------------------



Аналог твоего без union, очевидно, будет похуже оригинала.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select nvl(x.key, t2.key) key,
       max(x.value_1) value_1,
       max(t2.value) value_2
  from (select x.type, t1.key, t1.value value_1
          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
 group by nvl(x.key, t2.key)
 order by 1, 2, 3


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
----------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT      |      |      1 |        |      0 |00:00:12.82 |   33995 |  30507 |  29815 |
|   1 |  LOAD AS SELECT             |      |      1 |        |      0 |00:00:12.82 |   33995 |  30507 |  29815 |
|   2 |   HASH GROUP BY             |      |      1 |     25M|    500K|00:00:10.30 |   15397 |  30498 |  15108 |
|*  3 |    HASH JOIN OUTER          |      |      1 |     25M|   1000K|00:00:07.10 |   15397 |  22965 |   7575 |
|*  4 |     HASH JOIN OUTER         |      |      1 |   5000 |    500K|00:00:00.71 |    7700 |   7696 |      0 |
|   5 |      VIEW                   |      |      1 |      1 |      2 |00:00:00.01 |       3 |      2 |      0 |
|   6 |       COUNT                 |      |      1 |        |      2 |00:00:00.01 |       3 |      2 |      0 |
|   7 |        VIEW                 |      |      1 |      1 |      2 |00:00:00.01 |       3 |      2 |      0 |
|   8 |         SORT GROUP BY ROLLUP|      |      1 |      1 |      2 |00:00:00.01 |       3 |      2 |      0 |
|   9 |          TABLE ACCESS FULL  | DUAL |      1 |      1 |      1 |00:00:00.01 |       3 |      2 |      0 |
|  10 |      TABLE ACCESS FULL      | T1   |      1 |    500K|    500K|00:00:00.44 |    7697 |   7694 |      0 |
|  11 |     TABLE ACCESS FULL       | T2   |      1 |    500K|    500K|00:00:01.93 |    7697 |   7694 |      0 |
----------------------------------------------------------------------------------------------------------------


PS. Если вспомнить про перфоманс вариантов, где в условии соединения есть or - там вообще все печально.
Зато какое поле для творчества.
...
Рейтинг: 0 / 0
26.12.2016, 19:41
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375572
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender
 
уровень 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
/

connect by - это уж для слишком вырожденных случаев, но такой подход через коррелированные скалярчики допиливается и без оного.
...
Рейтинг: 0 / 0
26.12.2016, 19:49
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375574
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshopбез оного.а смысл? задача-то извращенческая и явно не на оптимизацию..
...
Рейтинг: 0 / 0
26.12.2016, 19:56
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375576
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Интересно, являются ли min/max операциями над множеством
...
Рейтинг: 0 / 0
26.12.2016, 19:59
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375577
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dbms_photoshopПризнаться в моей заготовке тоже не был учтен случай пустоты одного из наборовНе туда глянул. :))
Версия "Без учета уникальности" - работает для всех случаев.
...
Рейтинг: 0 / 0
26.12.2016, 20:00
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375582
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
dbms_photoshopУровень 2. Можно использовать только inner joins.


Неясно можно ли использовать CONNECT BY. Если да:

Код: 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.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
with x1 as (
            select  rownum rn,
                    t1.*
              from  t1
           ),
     x2 as (
            select  rownum rn,
                    t2.*
              from  t2
           ),
     c1 as (
            select  greatest(count(*),1) cnt
              from  t1
           ),
     c2 as (
            select  greatest(count(*),1) cnt
              from  t2
           ),
     l1 as (
            select  ceil(level / c2.cnt) t1_rn,
                    mod(level - 1,c2.cnt) + 1 t2_rn
              from  c1,
                    c2
              connect by level <= c1.cnt * c2.cnt
           ),
     l2 as (
            select  t1_rn,
                    t2_rn,
                    (
                     select  key
                       from  x1
                       where rn = t1_rn
                    ) t1_key,
                    (
                     select  key
                       from  x2
                       where rn = t2_rn
                    ) t2_key
              from  l1
           )
select  case
          when t1_key = t2_key or t2_rn = 1 then nvl(t1_key,t2_key)
          else t2_key
        end key,
        case
          when t1_key = t2_key or t2_rn = 1 then (
                                                  select  value
                                                    from  x1
                                                    where rn = t1_rn
                                                 )
        end value_1,
        case
          when t1_key = t2_key or t1_rn = 1 then (
                                                  select  value
                                                    from  x2
                                                    where rn = t2_rn
                                                 )
        end value_2
  from  l2
  where t1_key = t2_key
     or (
             l2.t2_rn = 1
         and
              not exists(
                         select  1
                           from  l2 l22
                           where l2.t1_rn = l22.t1_rn
                             and l2.t1_key = l22.t2_key
                        )
        )
     or (
             l2.t1_rn = 1
         and
              not exists(
                         select  1
                           from  l2 l22
                           where l2.t2_rn = l22.t2_rn
                             and l2.t2_key = l22.t1_key
                        )
        )
  order by key,
           value_1,
           value_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.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
SQL> with x1 as (
  2              select  rownum rn,
  3                      t1.*
  4                from  t1
  5             ),
  6       x2 as (
  7              select  rownum rn,
  8                      t2.*
  9                from  t2
 10             ),
 11       c1 as (
 12              select  greatest(count(*),1) cnt
 13                from  t1
 14             ),
 15       c2 as (
 16              select  greatest(count(*),1) cnt
 17                from  t2
 18             ),
 19       l1 as (
 20              select  ceil(level / c2.cnt) t1_rn,
 21                      mod(level - 1,c2.cnt) + 1 t2_rn
 22                from  c1,
 23                      c2
 24                connect by level <= c1.cnt * c2.cnt
 25             ),
 26       l2 as (
 27              select  t1_rn,
 28                      t2_rn,
 29                      (
 30                       select  key
 31                         from  x1
 32                         where rn = t1_rn
 33                      ) t1_key,
 34                      (
 35                       select  key
 36                         from  x2
 37                         where rn = t2_rn
 38                      ) t2_key
 39                from  l1
 40             )
 41  select  case
 42            when t1_key = t2_key or t2_rn = 1 then nvl(t1_key,t2_key)
 43            else t2_key
 44          end key,
 45          case
 46            when t1_key = t2_key or t2_rn = 1 then (
 47                                                    select  value
 48                                                      from  x1
 49                                                      where rn = t1_rn
 50                                                   )
 51          end value_1,
 52          case
 53            when t1_key = t2_key or t1_rn = 1 then (
 54                                                    select  value
 55                                                      from  x2
 56                                                      where rn = t2_rn
 57                                                   )
 58          end value_2
 59    from  l2
 60    where t1_key = t2_key
 61       or (
 62               l2.t2_rn = 1
 63           and
 64                not exists(
 65                           select  1
 66                             from  l2 l22
 67                             where l2.t1_rn = l22.t1_rn
 68                               and l2.t1_key = l22.t2_key
 69                          )
 70          )
 71       or (
 72               l2.t1_rn = 1
 73           and
 74                not exists(
 75                           select  1
 76                             from  l2 l22
 77                             where l2.t2_rn = l22.t2_rn
 78                               and l2.t2_key = l22.t1_key
 79                          )
 80          )
 81    order by key,
 82             value_1,
 83             value_2
 84  /

       KEY VA VA
---------- -- --
         0 A1 A2
         0 A1 Z2
         0 OO A2
         0 OO Z2
         1 B1 B2
         2 C1
         3 D1
         4 E1
         5    F2
        10    X2

10 rows selected.

SQL> delete t1
  2  /

6 rows deleted.

SQL> with x1 as (
  2              select  rownum rn,
  3                      t1.*
  4                from  t1
  5             ),
  6       x2 as (
  7              select  rownum rn,
  8                      t2.*
  9                from  t2
 10             ),
 11       c1 as (
 12              select  greatest(count(*),1) cnt
 13                from  t1
 14             ),
 15       c2 as (
 16              select  greatest(count(*),1) cnt
 17                from  t2
 18             ),
 19       l1 as (
 20              select  ceil(level / c2.cnt) t1_rn,
 21                      mod(level - 1,c2.cnt) + 1 t2_rn
 22                from  c1,
 23                      c2
 24                connect by level <= c1.cnt * c2.cnt
 25             ),
 26       l2 as (
 27              select  t1_rn,
 28                      t2_rn,
 29                      (
 30                       select  key
 31                         from  x1
 32                         where rn = t1_rn
 33                      ) t1_key,
 34                      (
 35                       select  key
 36                         from  x2
 37                         where rn = t2_rn
 38                      ) t2_key
 39                from  l1
 40             )
 41  select  case
 42            when t1_key = t2_key or t2_rn = 1 then nvl(t1_key,t2_key)
 43            else t2_key
 44          end key,
 45          case
 46            when t1_key = t2_key or t2_rn = 1 then (
 47                                                    select  value
 48                                                      from  x1
 49                                                      where rn = t1_rn
 50                                                   )
 51          end value_1,
 52          case
 53            when t1_key = t2_key or t1_rn = 1 then (
 54                                                    select  value
 55                                                      from  x2
 56                                                      where rn = t2_rn
 57                                                   )
 58          end value_2
 59    from  l2
 60    where t1_key = t2_key
 61       or (
 62               l2.t2_rn = 1
 63           and
 64                not exists(
 65                           select  1
 66                             from  l2 l22
 67                             where l2.t1_rn = l22.t1_rn
 68                               and l2.t1_key = l22.t2_key
 69                          )
 70          )
 71       or (
 72               l2.t1_rn = 1
 73           and
 74                not exists(
 75                           select  1
 76                             from  l2 l22
 77                             where l2.t2_rn = l22.t2_rn
 78                               and l2.t2_key = l22.t1_key
 79                          )
 80          )
 81    order by key,
 82             value_1,
 83             value_2
 84  /

       KEY VA VA
---------- -- --
         0    A2
         0    Z2
         1    B2
         5    F2
        10    X2

SQL> rollback
  2  /

Rollback complete.

SQL> delete t2
  2  /

5 rows deleted.

SQL> with x1 as (
  2              select  rownum rn,
  3                      t1.*
  4                from  t1
  5             ),
  6       x2 as (
  7              select  rownum rn,
  8                      t2.*
  9                from  t2
 10             ),
 11       c1 as (
 12              select  greatest(count(*),1) cnt
 13                from  t1
 14             ),
 15       c2 as (
 16              select  greatest(count(*),1) cnt
 17                from  t2
 18             ),
 19       l1 as (
 20              select  ceil(level / c2.cnt) t1_rn,
 21                      mod(level - 1,c2.cnt) + 1 t2_rn
 22                from  c1,
 23                      c2
 24                connect by level <= c1.cnt * c2.cnt
 25             ),
 26       l2 as (
 27              select  t1_rn,
 28                      t2_rn,
 29                      (
 30                       select  key
 31                         from  x1
 32                         where rn = t1_rn
 33                      ) t1_key,
 34                      (
 35                       select  key
 36                         from  x2
 37                         where rn = t2_rn
 38                      ) t2_key
 39                from  l1
 40             )
 41  select  case
 42            when t1_key = t2_key or t2_rn = 1 then nvl(t1_key,t2_key)
 43            else t2_key
 44          end key,
 45          case
 46            when t1_key = t2_key or t2_rn = 1 then (
 47                                                    select  value
 48                                                      from  x1
 49                                                      where rn = t1_rn
 50                                                   )
 51          end value_1,
 52          case
 53            when t1_key = t2_key or t1_rn = 1 then (
 54                                                    select  value
 55                                                      from  x2
 56                                                      where rn = t2_rn
 57                                                   )
 58          end value_2
 59    from  l2
 60    where t1_key = t2_key
 61       or (
 62               l2.t2_rn = 1
 63           and
 64                not exists(
 65                           select  1
 66                             from  l2 l22
 67                             where l2.t1_rn = l22.t1_rn
 68                               and l2.t1_key = l22.t2_key
 69                          )
 70          )
 71       or (
 72               l2.t1_rn = 1
 73           and
 74                not exists(
 75                           select  1
 76                             from  l2 l22
 77                             where l2.t2_rn = l22.t2_rn
 78                               and l2.t2_key = l22.t1_key
 79                          )
 80          )
 81    order by key,
 82             value_1,
 83             value_2
 84  /

       KEY VA VA
---------- -- --
         0 A1
         0 OO
         1 B1
         2 C1
         3 D1
         4 E1

6 rows selected.

SQL>




SY.
...
Рейтинг: 0 / 0
26.12.2016, 20:03
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375583
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtenderdbms_photoshopбез оного.а смысл? задача-то извращенческая и явно не на оптимизацию..Если любое упражнение на смекалку для тебя извращение - не совсем понятно зачем ты это решал.
Мог бы просто брызнуть желчью как Элик и всё.

Смысл
...
Рейтинг: 0 / 0
26.12.2016, 20:05
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375588
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andreymxИнтересно, являются ли min/max операциями над множествомВидимо я нечетко сформулировал.
Не операции, а операторы.
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm]The UNION [ALL], INTERSECT, MINUS Operators
...
Рейтинг: 0 / 0
26.12.2016, 20:11
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375590
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
dbms_photoshop,

так я гольфил пошагово, а не оптимизировал, т.к. условия изначально так поставлены, что нужно ухудшить стандартный FOJ
...
Рейтинг: 0 / 0
26.12.2016, 20:12
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375594
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
xtender,

Всё, всё, больше не задеваю твое эго. Спасибо за участие в любом случае.
...
Рейтинг: 0 / 0
26.12.2016, 20:14
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375609
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
dbms_photoshopandreymxИнтересно, являются ли min/max операциями над множествомВидимо я нечетко сформулировал.
Не операции, а операторы.
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm]The UNION [ALL], INTERSECT, MINUS Operators ведь джойн - это тоже операция над множествами :))
...
Рейтинг: 0 / 0
26.12.2016, 20:43
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375610
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
SYdbms_photoshopУровень 2. Можно использовать только inner joins.


Неясно можно ли использовать CONNECT BY.Весьма универсально. connect by не завязан на тип данных ключа.
...
Рейтинг: 0 / 0
26.12.2016, 20:45
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375621
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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

Надо еще включить фикс для пустоты. :)Один inner join с фиксом для пустоты.
Код: 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.
with
tt1 as
(
select key, value
from t1
where key is not null
model unique single reference dimension by (key) measures (value) rules (value[null]=null)
),
tt2 as
(
select key, value
from t2
where key is not null
model unique single reference dimension by (key) measures (value) rules (value[null]=null)
)
-- ^^^^^ ugly fix ^^^^^
select nvl(key_1, key_2) key, value_1, value_2
  from (select t1.key key_1,
               t1.value value_1,
               t2.key key_2,
               t2.value value_2,
               count(decode(t1.key, t2.key, null, t2.key)) over(partition by nvl(t1.key, t2.key)) c1,
               count(decode(t1.key, t2.key, null, t1.key)) over(partition by nvl(t1.key, t2.key)) c2
          from tt1 t1, tt2 t2
         where t1.key = t2.key
            or (t1.key is not null and t2.key is null)
            or (t2.key is not null and t1.key is null))
 where key_1 = key_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, 20:56
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375645
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopElicты слегка оторван от жизниСоединения, работа с наборами данныхВ работе нужно максимально эффективно (не только с точки зрения узколобой производительности) использовать подходящие к месту декларативные возможности языка, а не убегать от них.
dbms_photoshopи реляционная алгебра уже не в тренде?А вот тут ты точно оторвался. Эта сухая теоретичка в практической жизни не нужна. Одно деление чего только не стоит.

dbms_photoshopПризнаться в моей заготовке тоже не был учтен случай пустоты одного из наборов.
Код: plsql
1.
2.
model
-- ^^^^^ ugly fix ^^^^^

Стоило ли банить full с union-ом, чтобы притащить за уши уродца?
...
Рейтинг: 0 / 0
26.12.2016, 21:42
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375649
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Elic,

Да сколько же можно объяснять.
Упражнение было, чтоб размять мозги.

PS. Что не так с "учтен"? Ожидается ё?
...
Рейтинг: 0 / 0
26.12.2016, 21:49
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375652
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
dbms_photoshopОжидается ё?Почему-то носители забыли, что буквы не зубы.
...
Рейтинг: 0 / 0
26.12.2016, 21:55
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39375788
NVL2
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NVL2
Гость
Фотошоп, большое человечье спасибо тебе за трюк с nvl2 20042086
...
Рейтинг: 0 / 0
27.12.2016, 09:18
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39381788
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
dbms_photoshopЕще для уровня 1 можно решить за один проход каждой из таблиц, но при условии, что ключи уникальны .

union all detectedandreymx,

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

сильно хуже не стало
Код: 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 1 r, 0 KEY, 'A1' VALUE FROM dual  UNION ALL
  SELECT 1 r, 1 KEY, 'B1' VALUE FROM dual  UNION ALL
  SELECT 1 r, 2 KEY, 'C1' VALUE FROM dual  UNION ALL
  SELECT 1 r, 3 KEY, 'D1' VALUE FROM dual  UNION ALL
  SELECT 1 r, 4 KEY, 'E1' VALUE FROM dual
  ),
T2 AS
 (
  SELECT 2 r,  0 KEY, 'Z2' VALUE FROM dual  UNION ALL
  SELECT 2 r,  1 KEY, 'B2' VALUE FROM dual  UNION ALL
  SELECT 2 r,  5 KEY, 'F2' VALUE FROM dual  UNION ALL
  SELECT 2 r, 10 KEY, 'X2' VALUE FROM dual)
SELECT NVL(t1.KEY, T2.KEY) KEY,
       MAX(T1.VALUE) value_1,
       MAX(T2.VALUE) value_2
  FROM t1,
       T2,
       (SELECT ROWNUM rn FROM dual connect BY ROWNUM < 3) D
 WHERE t1.r(+) = D.rn
   AND T2.r(+) = D.rn
 GROUP BY NVL(t1.KEY, T2.KEY)
 order by key

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

Да это же самый настоящий аналог аналога. :)

Аналог твоего без union, очевидно, будет похуже оригинала.
...
Рейтинг: 0 / 0
10.01.2017, 20:01
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39381829
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
dbms_photoshop,

там был юнион!
...
Рейтинг: 0 / 0
10.01.2017, 20:07
| Ответить | Цитировать | Написать  
Пятнияная задачка: full equi join.
    #39381849
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andreymx,

Ты прочитал мой пост по приведенной ссылке? Полностью?
Интересно узнать где там union после процитированных слов.
...
Рейтинг: 0 / 0
10.01.2017, 20:38
| Ответить | Цитировать | Написать  
47 сообщений из 47, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятнияная задачка: full equi join.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1886689]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 198ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 104ms
get tp. blocked users:
 2ms
others: 225ms
total:  573ms
созд. / загр.: 573ms
x
x
Закрыть


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