ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Разбить пары на группы
17 сообщений из 17, страница 1 из 1
Разбить пары на группы
    #39862677
ZiB
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZiB
Гость
Добрый день.
У меня есть таблица с двумя атрибутами: Текс1 и Текс2
В ней N строк, Текс1 всегда меньше Текс2.

Например:
Код: sql
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.
CREATE TABLE tmp (text1 VARCHAR2(10 CHAR),text2 VARCHAR2(10 CHAR),gr NUMBER);
select '3906098914' as text1, '7731634005' as text2, 0 as gr from dual
union all select '7715656026', '7733594911', 1 from dual
union all select '5903134283', '7733271117', 1 from dual
union all select '5008039369', '7715404075', 1 from dual
union all select '5903134283', '7722385070', 1 from dual
union all select '5903134283', '7715656026', 1 from dual
union all select '7715656026', '7722353544', 1 from dual
union all select '7715404075', '7733594911', 1 from dual
union all select '6166084084', '7715656026', 1 from dual
union all select '7715404075', '7715656026', 1 from dual
union all select '5012074091', '7715404075', 1 from dual
union all select '7715656026', '7722385070', 1 from dual
union all select '5024069515', '7715656026', 1 from dual
union all select '7715656026', '7718770714', 1 from dual
union all select '7724922443', '7729418511', 1 from dual
union all select '7715656026', '7718698722', 1 from dual
union all select '5903134283', '6166084084', 1 from dual
union all select '7715656026', '7725841412', 1 from dual
union all select '5008039369', '7715656026', 1 from dual
union all select '5012074091', '7715656026', 1 from dual
union all select '7715656026', '7733271117', 1 from dual
union all select '7715404075', '7733271117', 1 from dual
union all select '7724922443', '7733594911', 1 from dual
union all select '5029137695', '7723004480', 2 from dual
union all select '6234131391', '7714900049', 3 from dual
union all select '6234131391', '7716748907', 3 from dual
union all select '7716748907', '7723716712', 3 from dual
union all select '6229036015', '7716748907', 3 from dual
union all select '7707372905', '7733711505', 4 from dual
union all select '7707372905', '7713446697', 4 from dual
union all select '7707781200', '7728310956', 5 from dual
union all select '7740000076', '7812014560', 6 from dual;



Мне необходимо разбить тексты на группы - заполнить значение gr.
В примере gr заполнено вручную.
Под группой подразумевается конечная цепочка текстов,
надеюсь из примера понятно.

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

Помогите пожалуйста.
...
Рейтинг: 0 / 0
17.09.2019, 11:20
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862688
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
объединение групп, у которых есть пересекающиеся элементы (пятничная задача ?)
...
Рейтинг: 0 / 0
17.09.2019, 11:27
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862699
ZiB
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZiB
Гость
Текс1 и Текс2 вместе уникальны.
Oracle 12.
Желателен SQL, не PL/SQL
...
Рейтинг: 0 / 0
17.09.2019, 11:36
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862827
feagor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
feagor
Гость
ZiB,

Я что-то по примеру не очень хорошо понимаю каким образом формируется группа
...
Рейтинг: 0 / 0
17.09.2019, 13:30
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862834
ZiB
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZiB
Гость
Берется минимальный текст и присваивается ему группа 0.
Все тексты в паре с этим текстом то же попадают в группу 0,
а так же все тексты с ними и т.д.
Из оставшихся, не попавших ни в одну группу, опять берется минимальный текст
и формируется группа 1 и т.д.

Вот так получилось:
Код: sql
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.
with par as ( -- Тестовая выборка
  -- Группа №0: 3906098914
  select '3906098914' as text1, '7731634005' as text2, '3906098914' as gr from dual
  -- Группа №1: 5008039369
  union all select '7715656026', '7733594911', '5008039369' from dual
  union all select '5903134283', '7733271117', '5008039369' from dual
  union all select '5008039369', '7715404075', '5008039369' from dual
  union all select '5903134283', '7722385070', '5008039369' from dual
  union all select '5903134283', '7715656026', '5008039369' from dual
  union all select '7715656026', '7722353544', '5008039369' from dual
  union all select '7715404075', '7733594911', '5008039369' from dual
  union all select '6166084084', '7715656026', '5008039369' from dual
  union all select '7715404075', '7715656026', '5008039369' from dual
  union all select '5012074091', '7715404075', '5008039369' from dual
  union all select '7715656026', '7722385070', '5008039369' from dual
  union all select '5024069515', '7715656026', '5008039369' from dual
  union all select '7715656026', '7718770714', '5008039369' from dual
  union all select '7724922443', '7729418511', '5008039369' from dual
  union all select '7715656026', '7718698722', '5008039369' from dual
  union all select '5903134283', '6166084084', '5008039369' from dual
  union all select '7715656026', '7725841412', '5008039369' from dual
  union all select '5008039369', '7715656026', '5008039369' from dual
  union all select '5012074091', '7715656026', '5008039369' from dual
  union all select '7715656026', '7733271117', '5008039369' from dual
  union all select '7715404075', '7733271117', '5008039369' from dual
  union all select '7724922443', '7733594911', '5008039369' from dual
  -- Группа №2: 5029137695
  union all select '5029137695', '7723004480', '5029137695' from dual
  -- Группа №3: 6229036015
  union all select '6234131391', '7714900049', '6229036015' from dual
  union all select '6234131391', '7716748907', '6229036015' from dual
  union all select '7716748907', '7723716712', '6229036015' from dual
  union all select '6229036015', '7716748907', '6229036015' from dual
  -- Группа №4: 7707372905
  union all select '7707372905', '7733711505', '7707372905' from dual
  union all select '7707372905', '7713446697', '7707372905' from dual
  -- Группа №5: 7707781200
  union all select '7707781200', '7728310956', '7707781200' from dual
  -- Группа №6: 7740000076
  union all select '7740000076', '7812014560', '7740000076' from dual
)
  select
    text1 -- Все тексты
    ,gr -- Ручная группа
    ,min(least(connect_by_root text1,connect_by_root text2))  as min_text -- Автоматическая группа
  from (select text1, text2, gr from par union all select text2, text1, gr from par) par
  connect by nocycle decode(text2, prior text1, 1, 0) /*+ decode(text1, prior text2, 1, 0)*/ = 1
  group by text1,gr
  order by gr,text1



Однако при увеличении данных время выполнения запроса увеличивается экспоненциально.
...
Рейтинг: 0 / 0
17.09.2019, 13:38
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862837
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
ZiBЖелателен SQL, не PL/SQL
Критерий сортировки где?
...
Рейтинг: 0 / 0
17.09.2019, 13:42
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862848
ZiB
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZiB
Гость
andrey_anonymous, не понимаю что за критерий сортировки
...
Рейтинг: 0 / 0
17.09.2019, 13:59
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862920
Да ну
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Да ну
Участник
ZiB,

Попытаюсь переформулировать задачу в терминах графов:

Дано: имеется неориентированный граф, заданный множеством ребер (концы ребер соответственно являются вершинами)

Надо: разбить граф на множество несвязанных подграфов (группы).
Изолированных вершин в графе быть не может, поскольку граф задан множеством ребер

Так?
...
Рейтинг: 0 / 0
17.09.2019, 15:36
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862924
ZiB
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZiB
Гость
Да ну,

все так!
...
Рейтинг: 0 / 0
17.09.2019, 15:42
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39862963
ZiB
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZiB
Гость
Еще нашел один вариант решения,
однако так же при увеличении данных время выполнения запроса увеличивается экспоненциально.

Код: sql
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.
with par as ( -- Тестовая выборка
  -- Группа №0: 3906098914
  select '3906098914' as text1, '7731634005' as text2, '3906098914' as gr from dual
  -- Группа №1: 5008039369
  /*union all select '7715656026', '7733594911', '5008039369' from dual
  union all select '5903134283', '7733271117', '5008039369' from dual
  union all select '5008039369', '7715404075', '5008039369' from dual
  union all select '5903134283', '7722385070', '5008039369' from dual
  union all select '5903134283', '7715656026', '5008039369' from dual
  union all select '7715656026', '7722353544', '5008039369' from dual
  union all select '7715404075', '7733594911', '5008039369' from dual
  union all select '6166084084', '7715656026', '5008039369' from dual
  union all select '7715404075', '7715656026', '5008039369' from dual
  union all select '5012074091', '7715404075', '5008039369' from dual
  union all select '7715656026', '7722385070', '5008039369' from dual
  union all select '5024069515', '7715656026', '5008039369' from dual
  union all select '7715656026', '7718770714', '5008039369' from dual
  union all select '7724922443', '7729418511', '5008039369' from dual
  union all select '7715656026', '7718698722', '5008039369' from dual
  union all select '5903134283', '6166084084', '5008039369' from dual
  union all select '7715656026', '7725841412', '5008039369' from dual
  union all select '5008039369', '7715656026', '5008039369' from dual
  union all select '5012074091', '7715656026', '5008039369' from dual
  union all select '7715656026', '7733271117', '5008039369' from dual
  union all select '7715404075', '7733271117', '5008039369' from dual
  union all select '7724922443', '7733594911', '5008039369' from dual*/
  -- Группа №2: 5029137695
  union all select '5029137695', '7723004480', '5029137695' from dual
  -- Группа №3: 6229036015
  union all select '6234131391', '7714900049', '6229036015' from dual
  union all select '6234131391', '7716748907', '6229036015' from dual
  union all select '7716748907', '7723716712', '6229036015' from dual
  union all select '6229036015', '7716748907', '6229036015' from dual
  -- Группа №4: 7707372905
  union all select '7707372905', '7733711505', '7707372905' from dual
  union all select '7707372905', '7713446697', '7707372905' from dual
  -- Группа №5: 7707781200
  union all select '7707781200', '7728310956', '7707781200' from dual
  -- Группа №6: 7740000076
  union all select '7740000076', '7812014560', '7740000076' from dual
)
select
  text1 -- Текст1
  ,text2 -- Текст2
  ,gr -- Ручная группа
  ,min(gr2) gr2 -- Автоматическая группа
from (
  select g1.text1,g1.text2,g1.gr, connect_by_root g1.text1 gr2
  from par g1, par g2
  connect by nocycle (prior g1.text1 = g1.text1) or (prior g1.text2 = g1.text2)
  start with g1.text1 = g2.text1
)
group by text1, text2, gr
...
Рейтинг: 0 / 0
17.09.2019, 16:43
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39863040
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
ZiB,

Connected components: Weighted quick-union algorithm http://orasql.org/2017/09/29/connected-components/
...
Рейтинг: 0 / 0
17.09.2019, 18:19
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39863041
Да ну
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Да ну
Участник
ZiBЕще нашел один вариант решения,
...
не то:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
with par(text1,text2,gr) as (
  select 'A', 'B', 1 from dual union all
  select 'B', 'C', 1 from dual union all
  select 'C', 'D', 1 from dual union all
  select 'A', 'C', 1 from dual 
)
select
  text1 -- Текст1
  ,text2 -- Текст2
  ,gr -- Ручная группа
  ,min(gr2) gr2 -- Автоматическая группа
from (
  select g1.text1,g1.text2,g1.gr, connect_by_root g1.text1 gr2
  from par g1, par g2
  connect by nocycle (prior g1.text1 = g1.text1) or (prior g1.text2 = g1.text2)
  start with g1.text1 = g2.text1
)
group by text1, text2, gr


TEXT1TEXT2GRGR2BC1ACD1CAC1AAB1A
...
Рейтинг: 0 / 0
17.09.2019, 18:19
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39863646
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
ZiB,

раз уж у тебя оракл 12:
 
Код: 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.
with

   function get_numbers(cur in sys_refcursor) return clob is
     v_size     constant integer := 100;
     fetch_size constant integer:=1000;
      type numbers_a_array is table of number index by varchar2(v_size);
      type strings_a_array is table of varchar2(v_size) index by varchar2(v_size);

      type num_elems    is table of numbers index by varchar2(v_size);
      type str_elems    is table of strings index by varchar2(v_size);


      root              numbers_a_array;
      root_elems        num_elems;
      idx               number;
      temp1             numbers;
      temp2             numbers;
      res               clob;

       $IF $$DEBUG $THEN
          l integer:=dbms_utility.get_time();
       $END
          procedure print(v in varchar2) is
          begin
            $IF $$DEBUG $THEN
            dbms_output.put_line(to_char((dbms_utility.get_time-l)/100,'0999.99')||' '||v);
            l:=dbms_utility.get_time();
            $ELSE
            null;
            $END
          end;
      
      function get_root(n number) return varchar2 is
      begin
         if root.exists(n) then 
            return root(n);
         else 
            return null;
         end if;
      end;
      
      procedure update_root(old_root number,new_root number) is
         i pls_integer;
      begin
         if old_root!=new_root then 
            root_elems(new_root):=root_elems(new_root) multiset union all root_elems(old_root);
            for i in 1..root_elems(old_root).count
            loop
               root(root_elems(old_root)(i)):=new_root;
            end loop;
            root_elems(old_root).delete;
          end if;
      end;
      
      procedure add_elem(p_root number, p_elem number) is
      begin
         if not root_elems.exists(p_root) then
            dbms_output.put_line(p_root||':'||p_elem);
            root_elems(p_root):=numbers(p_elem);
         elsif not root.exists(p_elem) then
            root_elems(p_root).extend();
            root_elems(p_root)(root_elems(p_root).count):=p_elem;
         end if;
      end;
      
      procedure add_link(p number,q number) is
         r1       varchar2(v_size);
         r2       varchar2(v_size);
         new_root varchar2(v_size);
         old_root varchar2(v_size);
      begin
         r1:=get_root(p);
         r2:=get_root(q);
         
         if r1=r2 then 
            return;
         elsif r1 is null or r2 is null then
            new_root := coalesce(r1,r2,p);
            if r1 is null and p is not null then add_elem(new_root,p); root(p):=new_root; end if;
            if r2 is null and q is not null then add_elem(new_root,q); root(q):=new_root; end if;
         else
            case when root_elems(r1).count > root_elems(r2).count 
                    then new_root:=r1; old_root:=r2;
                    else new_root:=r2; old_root:=r1;
            end case;
            root(p) :=new_root;
            root(q) :=new_root;
            update_root(old_root,new_root);
         end if;
         
      end;

   begin
      print('start');
      loop
         fetch cur bulk collect into temp1, temp2 limit fetch_size;
         for i in 1..temp1.count loop
             add_link(temp1(i), temp2(i));
         end loop;
         dbms_session.free_unused_user_memory;
         exit when cur%notfound;
      end loop;
      print('processed');
      
      -- return results:
      res:='<ROWSET>';
      idx:= root_elems.first();
      while idx is not null loop
         if root_elems(idx).count>0 then
            --pipe row (root_elems(idx));
            --res.extend;
            --res(res.count) := root_elems(idx);
            res:=res||'<ROW>';
            for i in 1..root_elems(idx).count loop
               res:=res||('<N>'||root_elems(idx)(i)||'</N>');
            end loop;
            res:=res||'</ROW>';
         end if;
         idx:=root_elems.next(idx);
      end loop;
      res:=res||'</ROWSET>';
      return (res);
   end get_numbers;
select
  *
from xmltable(
       '/ROWSET/ROW'
       passing xmltype(
          get_numbers(
               cursor(
                   -- тут вставь свой запрос, возвращающий пары:
                        with par as ( -- Тестовая выборка
                          -- Группа №0: 3906098914
                          select '3906098914' as text1, '7731634005' as text2, '3906098914' as gr from dual
                          -- Группа №1: 5008039369
                          union all select '7715656026', '7733594911', '5008039369' from dual
                          union all select '5903134283', '7733271117', '5008039369' from dual
                          union all select '5008039369', '7715404075', '5008039369' from dual
                          union all select '5903134283', '7722385070', '5008039369' from dual
                          union all select '5903134283', '7715656026', '5008039369' from dual
                          union all select '7715656026', '7722353544', '5008039369' from dual
                          union all select '7715404075', '7733594911', '5008039369' from dual
                          union all select '6166084084', '7715656026', '5008039369' from dual
                          union all select '7715404075', '7715656026', '5008039369' from dual
                          union all select '5012074091', '7715404075', '5008039369' from dual
                          union all select '7715656026', '7722385070', '5008039369' from dual
                          union all select '5024069515', '7715656026', '5008039369' from dual
                          union all select '7715656026', '7718770714', '5008039369' from dual
                          union all select '7724922443', '7729418511', '5008039369' from dual
                          union all select '7715656026', '7718698722', '5008039369' from dual
                          union all select '5903134283', '6166084084', '5008039369' from dual
                          union all select '7715656026', '7725841412', '5008039369' from dual
                          union all select '5008039369', '7715656026', '5008039369' from dual
                          union all select '5012074091', '7715656026', '5008039369' from dual
                          union all select '7715656026', '7733271117', '5008039369' from dual
                          union all select '7715404075', '7733271117', '5008039369' from dual
                          union all select '7724922443', '7733594911', '5008039369' from dual
                          -- Группа №2: 5029137695
                          union all select '5029137695', '7723004480', '5029137695' from dual
                          -- Группа №3: 6229036015
                          union all select '6234131391', '7714900049', '6229036015' from dual
                          union all select '6234131391', '7716748907', '6229036015' from dual
                          union all select '7716748907', '7723716712', '6229036015' from dual
                          union all select '6229036015', '7716748907', '6229036015' from dual
                          -- Группа №4: 7707372905
                          union all select '7707372905', '7733711505', '7707372905' from dual
                          union all select '7707372905', '7713446697', '7707372905' from dual
                          -- Группа №5: 7707781200
                          union all select '7707781200', '7728310956', '7707781200' from dual
                          -- Группа №6: 7740000076
                          union all select '7740000076', '7812014560', '7740000076' from dual
                        )
                      select text1,text2 from par
                   -- конец твоего запроса
               )
          )
       )
       columns 
         grp for ordinality
        ,elements varchar2(1000) path 'string-join(./N,",")'
       )
/

...
Рейтинг: 0 / 0
19.09.2019, 02:00
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39863647
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
или если хочешь по отдельности:
 
Код: 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.
with

   function get_numbers(cur in sys_refcursor) return clob is
     v_size     constant integer := 100;
     fetch_size constant integer:=1000;
      type numbers_a_array is table of number index by varchar2(v_size);
      type strings_a_array is table of varchar2(v_size) index by varchar2(v_size);

      type num_elems    is table of numbers index by varchar2(v_size);
      type str_elems    is table of strings index by varchar2(v_size);


      root              numbers_a_array;
      root_elems        num_elems;
      idx               number;
      temp1             numbers;
      temp2             numbers;
      res               clob;

       $IF $$DEBUG $THEN
          l integer:=dbms_utility.get_time();
       $END
          procedure print(v in varchar2) is
          begin
            $IF $$DEBUG $THEN
            dbms_output.put_line(to_char((dbms_utility.get_time-l)/100,'0999.99')||' '||v);
            l:=dbms_utility.get_time();
            $ELSE
            null;
            $END
          end;
      
      function get_root(n number) return varchar2 is
      begin
         if root.exists(n) then 
            return root(n);
         else 
            return null;
         end if;
      end;
      
      procedure update_root(old_root number,new_root number) is
         i pls_integer;
      begin
         if old_root!=new_root then 
            root_elems(new_root):=root_elems(new_root) multiset union all root_elems(old_root);
            for i in 1..root_elems(old_root).count
            loop
               root(root_elems(old_root)(i)):=new_root;
            end loop;
            root_elems(old_root).delete;
          end if;
      end;
      
      procedure add_elem(p_root number, p_elem number) is
      begin
         if not root_elems.exists(p_root) then
            dbms_output.put_line(p_root||':'||p_elem);
            root_elems(p_root):=numbers(p_elem);
         elsif not root.exists(p_elem) then
            root_elems(p_root).extend();
            root_elems(p_root)(root_elems(p_root).count):=p_elem;
         end if;
      end;
      
      procedure add_link(p number,q number) is
         r1       varchar2(v_size);
         r2       varchar2(v_size);
         new_root varchar2(v_size);
         old_root varchar2(v_size);
      begin
         r1:=get_root(p);
         r2:=get_root(q);
         
         if r1=r2 then 
            return;
         elsif r1 is null or r2 is null then
            new_root := coalesce(r1,r2,p);
            if r1 is null and p is not null then add_elem(new_root,p); root(p):=new_root; end if;
            if r2 is null and q is not null then add_elem(new_root,q); root(q):=new_root; end if;
         else
            case when root_elems(r1).count > root_elems(r2).count 
                    then new_root:=r1; old_root:=r2;
                    else new_root:=r2; old_root:=r1;
            end case;
            root(p) :=new_root;
            root(q) :=new_root;
            update_root(old_root,new_root);
         end if;
         
      end;

   begin
      print('start');
      loop
         fetch cur bulk collect into temp1, temp2 limit fetch_size;
         for i in 1..temp1.count loop
             add_link(temp1(i), temp2(i));
         end loop;
         dbms_session.free_unused_user_memory;
         exit when cur%notfound;
      end loop;
      print('processed');
      
      -- return results:
      res:='<ROWSET>';
      idx:= root_elems.first();
      while idx is not null loop
         if root_elems(idx).count>0 then
            --pipe row (root_elems(idx));
            --res.extend;
            --res(res.count) := root_elems(idx);
            res:=res||'<ROW>';
            for i in 1..root_elems(idx).count loop
               res:=res||('<N>'||root_elems(idx)(i)||'</N>');
            end loop;
            res:=res||'</ROW>';
         end if;
         idx:=root_elems.next(idx);
      end loop;
      res:=res||'</ROWSET>';
      return (res);
   end get_numbers;
select
  x.GRP,
  e.N
from xmltable(
       '/ROWSET/ROW'
       passing xmltype(
          get_numbers(
               cursor(
                   -- тут вставь свой запрос, возвращающий пары:
                        with par as ( -- Тестовая выборка
                          -- Группа №0: 3906098914
                          select '3906098914' as text1, '7731634005' as text2, '3906098914' as gr from dual
                          -- Группа №1: 5008039369
                          union all select '7715656026', '7733594911', '5008039369' from dual
                          union all select '5903134283', '7733271117', '5008039369' from dual
                          union all select '5008039369', '7715404075', '5008039369' from dual
                          union all select '5903134283', '7722385070', '5008039369' from dual
                          union all select '5903134283', '7715656026', '5008039369' from dual
                          union all select '7715656026', '7722353544', '5008039369' from dual
                          union all select '7715404075', '7733594911', '5008039369' from dual
                          union all select '6166084084', '7715656026', '5008039369' from dual
                          union all select '7715404075', '7715656026', '5008039369' from dual
                          union all select '5012074091', '7715404075', '5008039369' from dual
                          union all select '7715656026', '7722385070', '5008039369' from dual
                          union all select '5024069515', '7715656026', '5008039369' from dual
                          union all select '7715656026', '7718770714', '5008039369' from dual
                          union all select '7724922443', '7729418511', '5008039369' from dual
                          union all select '7715656026', '7718698722', '5008039369' from dual
                          union all select '5903134283', '6166084084', '5008039369' from dual
                          union all select '7715656026', '7725841412', '5008039369' from dual
                          union all select '5008039369', '7715656026', '5008039369' from dual
                          union all select '5012074091', '7715656026', '5008039369' from dual
                          union all select '7715656026', '7733271117', '5008039369' from dual
                          union all select '7715404075', '7733271117', '5008039369' from dual
                          union all select '7724922443', '7733594911', '5008039369' from dual
                          -- Группа №2: 5029137695
                          union all select '5029137695', '7723004480', '5029137695' from dual
                          -- Группа №3: 6229036015
                          union all select '6234131391', '7714900049', '6229036015' from dual
                          union all select '6234131391', '7716748907', '6229036015' from dual
                          union all select '7716748907', '7723716712', '6229036015' from dual
                          union all select '6229036015', '7716748907', '6229036015' from dual
                          -- Группа №4: 7707372905
                          union all select '7707372905', '7733711505', '7707372905' from dual
                          union all select '7707372905', '7713446697', '7707372905' from dual
                          -- Группа №5: 7707781200
                          union all select '7707781200', '7728310956', '7707781200' from dual
                          -- Группа №6: 7740000076
                          union all select '7740000076', '7812014560', '7740000076' from dual
                        )
                      select text1,text2 from par
                   -- конец твоего запроса
               )
          )
       )
       columns 
         grp for ordinality
        ,elements xmltype path 'N'
       ) x
      ,xmltable('N' passing x.elements columns N number path '.') e
/

...
Рейтинг: 0 / 0
19.09.2019, 02:03
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39991987
ZiB
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ZiB
Гость
Код: 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.
with par as ( -- Тестовая выборка
  -- Группа №0: 3906098914
  select 1 as n, '3906098914' as text1, '7731634005' as text2, '3906098914' as gr from dual
  -- Группа №1: 5008039369
  union all select 2, '7715656026', '7733594911', '5008039369' from dual
  union all select 3, '5903134283', '7733271117', '5008039369' from dual
  union all select 4, '5008039369', '7715404075', '5008039369' from dual
  union all select 5, '5903134283', '7722385070', '5008039369' from dual
  union all select 6, '5903134283', '7715656026', '5008039369' from dual
  union all select 7, '7715656026', '7722353544', '5008039369' from dual
  union all select 8, '7715404075', '7733594911', '5008039369' from dual
  union all select 9, '6166084084', '7715656026', '5008039369' from dual
  union all select 10, '7715404075', '7715656026', '5008039369' from dual
  union all select 11, '5012074091', '7715404075', '5008039369' from dual
  union all select 12, '7715656026', '7722385070', '5008039369' from dual
  union all select 13, '5024069515', '7715656026', '5008039369' from dual
  union all select 14, '7715656026', '7718770714', '5008039369' from dual
  union all select 15, '7724922443', '7729418511', '5008039369' from dual
  union all select 16, '7715656026', '7718698722', '5008039369' from dual
  union all select 17, '5903134283', '6166084084', '5008039369' from dual
  union all select 18, '7715656026', '7725841412', '5008039369' from dual
  union all select 19, '5008039369', '7715656026', '5008039369' from dual
  union all select 20, '5012074091', '7715656026', '5008039369' from dual
  union all select 21, '7715656026', '7733271117', '5008039369' from dual
  union all select 22, '7715404075', '7733271117', '5008039369' from dual
  union all select 23, '7724922443', '7733594911', '5008039369' from dual
  -- Группа №2: 5029137695
  union all select 24, '5029137695', '7723004480', '5029137695' from dual
  -- Группа №3: 6229036015
  union all select 25, '6234131391', '7714900049', '6229036015' from dual
  union all select 26, '6234131391', '7716748907', '6229036015' from dual
  union all select 27, '7716748907', '7723716712', '6229036015' from dual
  union all select 28, '6229036015', '7716748907', '6229036015' from dual
  -- Группа №4: 7707372905
  union all select 29, '7707372905', '7733711505', '7707372905' from dual
  union all select 30, '7707372905', '7713446697', '7707372905' from dual
  -- Группа №5: 7707781200
  union all select 31, '7707781200', '7728310956', '7707781200' from dual
  -- Группа №6: 7740000076
  union all select 32, '7740000076', '7812014560', '7740000076' from dual
)
,tree (n, text1, text2, gr, gr_min, lev1) as ( -- Рекрусивный: первый шаг, разбиваем ориентированный граф на множество несвязанных подграфов (но граф неориентированный)
  select n, text1, text2, gr, text1 as gr_min, 0 as lev1
  from par

  union all
  
  select tree.n, tree.text1, tree.text2, tree.gr, par_down.text1 as gr_min, tree.lev1+1 as lev1
  from tree
  join par par_down on par_down.text2 = tree.gr_min and par_down.text1 < tree.gr_min
)
,tree0 as ( -- сводим
  select n, text1, text2, gr, min(gr_min) as gr_min, max(lev1) as lev1
  from tree
  group by n, text1, text2, gr
  order by n
)
/*
,tree2 (n, text1, text2, gr, gr_min, lev1, lev2) as ( -- Рекрусивный: второй шаг - для одинаковых text2 указываем группу
  select n, text1, text2, gr, gr_min, lev1, 0 as lev2
  from tree0 tree

  union all
  
  select tree2.n, tree2.text1, tree2.text2, tree2.gr, par_up.gr_min as gr_min, tree2.lev1, tree2.lev2+1 as lev2
  from tree2
  join tree0 par_up on par_up.text2 = tree2.text2 and par_up.gr_min < tree2.gr_min
)
*/
,tree2 as ( -- НеРекрусивный: второй шаг - для одинаковых text2 указываем группу
  select tree0.n, tree0.text1, tree0.text2, tree0.gr, nvl(par_up.gr_min,tree0.gr_min) as gr_min, tree0.lev1, case when par_up.gr_min is null then 0 else 1 end as lev2
  from tree0
  left join tree0 par_up on par_up.text2 = tree0.text2 and par_up.gr_min < tree0.gr_min
)
 --select * from tree2 order by n, lev1, lev2
,tree20 as ( -- сводим
  select n, text1, text2, gr, min(gr_min) as gr_min, max(lev1) as lev1, max(lev2) as lev2
  from tree2
  group by n, text1, text2, gr
  order by n
)

,tree3 as ( -- НеРекрусивный: третий шаг - для одинаковых text1 указываем группу
  select tree2.n, tree2.text1, tree2.text2, tree2.gr, nvl(tree3.gr_min,tree2.gr_min) as gr_min, tree2.lev1, tree2.lev2, case when tree3.gr_min is not null then 1 else 0 end as lev3
  from tree20 tree2
  left join tree2 tree3 on tree3.text1 = tree2.gr_min and tree3.gr_min < tree2.gr_min
)
select
  n -- Номер пары по порядку
  ,text1 -- text1
  ,text2 -- text2
  --,gr -- Правильная ручная группа
  ,min(gr_min) as gr_min -- Вычисленная группа
  --,max(lev1) as lev1 -- Итераций на первом шаге рекрусивном
  --,max(lev2) as lev2 -- Итераций на втором шаге
  --,max(lev3) as lev3 -- Итераций на третьем шаге
  --,case when gr = min(gr_min) then 1 else 0 end as pr -- признак что правильно
from tree3
group by n, text1, text2, gr
order by n
...
Рейтинг: 0 / 0
24.08.2020, 14:58
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39992000
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
ZiB,

не вглядывался, но
1. что-то у тебя не работает на таком тесте:
Код: plsql
1.
2.
3.
4.
with par as ( -- Тестовая выборка
            select level as n, to_char(level,'tm9') as text1, to_char(level+1,'tm9') as text2, to_char(level*100,'tm9') as gr from dual
            connect by level<=100
)


2. плюс в SQL все та же проблема:
ZiB
однако так же при увеличении данных время выполнения запроса увеличивается экспоненциально.
смотри pl/sql-ные решения, если у тебя данных много
...
Рейтинг: 0 / 0
24.08.2020, 15:22
| Ответить | Цитировать | Написать  
Разбить пары на группы
    #39992003
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
собственно, не работает уже на таком:
Код: plsql
1.
2.
3.
4.
with par(n,text1,text2,gr) as ( -- Тестовая выборка
            select 1,  '2',  '10', '100' from dual
  union all select 2, '10', '999', '101' from dual
)
...
Рейтинг: 0 / 0
24.08.2020, 15:32
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Разбить пары на группы
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39862688&tid=1880946]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 58ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 1ms
others: 16ms
total:  174ms
созд. / загр.: 174ms
x
x
Закрыть


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