Postgres рекурсивный с o PL: сколько там групп?

#postgresql #recursion #relationship

#postgresql #рекурсия #взаимосвязь

Вопрос:

У меня есть 2 таблицы: Table1: целое число ID_ELEMENT, только одно поле со списком людей, которые меня интересуют.

Relation_table: целое число ID_ELEMENT, целое число ID_SECONDARY_ELEMENT

 create table Table1 (ID_Element integer);
create table Relation_table (ID_Element integer,ID_SECONDARY_ELEMENT integer);

ID_SECONDARY_ELEMENT не может присутствовать в Table1.

Все элементы, имеющие отношение (в качестве первого или второго члена в Relation_table, без разницы), принадлежат к одной группе.

Я хочу знать, сколько существует групп элементов и кто принадлежит к какой группе.

Пример: Таблица1: 1,2,3,4,5,6,9,11 Relation_table: (1,2),(1,3),(1,4),(2,1),(4,6),(5,777),(5,898),(11,9)

 |id_element |
|-----------|
|1          |
|2          |
|3          |
|4          |
|5          |
|6          |
|9          |
|11         |


|id_element |id_secondary_element|
|-----------|--------------------|
|1          |2                   |
|1          |3                   |
|1          |4                   |
|2          |1                   |
|4          |6                   |
|5          |777                 |
|5          |898                 |
|11         |9                   |
insert into  Table1 values (1);
insert into  Table1 values (2);
insert into  Table1 values (3);
insert into  Table1 values (4);
insert into  Table1 values (5);
insert into  Table1 values (6);
insert into  Table1 values (9);
insert into  Table1 values (11);

insert into Relation_table values (1,2);
insert into Relation_table values(1,3);
insert into Relation_table values(1,4);
insert into Relation_table values(2,1);
insert into Relation_table values(4,6);
insert into Relation_table values(5,777);
insert into Relation_table values(5,898);
insert into Relation_table values(11,9);

Результаты:
Группа1: 1,2,3,4,6
Group2: 5 (нет ссылок на другие)
Группа3: 9,11

777 и 898 не принадлежат Table1.

Есть предложения?

Ответ №1:

Для достижения этой цели мне пришлось использовать типы массивов.

Сначала удалите повторяющиеся отношения:

 with recursive combine as (
  select distinct least(id_element, id_secondary_element) as lid, 
         greatest(id_element, id_secondary_element) as rid
    from relation_table
),

Затем сгруппируйтесь в обоих направлениях. Одна использует меньшее id значение, а другая — большее id значение. Объедините их в int[] и включите базу id , добавив дополнение в зависимости от агрегации.

 exploded as (
  select lid, lid||array_agg(rid order by rid) as members
    from combine
   group by lid
  union 
  select rid, array_agg(lid order by lid)||rid as members
    from combine
   group by rid
),

На этапе рекурсии используется оператор перекрытия массива amp;amp; для поиска и объединения групп вместе. rpath Необходимо для предотвращения бесконечного цикла.

 iter as (
  select members[1] as first_member, members, array[members[1]] as rpath
    from exploded e
  union all
  select i.first_member, i.members || e.members, i.rpath||e.members[1]
    from iter i
         join exploded e
           on e.members amp;amp; i.members
          and not e.members[1] = any(i.rpath)
),

Устраните повторяющиеся id значения, исключите id значения, которых нет в table1 , а затем сохраните только отдельные members массивы.

 dedup as (
  select distinct array_agg(distinct m.member_id order by m.member_id) as members
    from iter i
         cross join lateral unnest(members) as m(member_id)
         join table1 on table1.id_element = m.member_id
   group by i.first_member
)

Назначьте group_num и верните результаты.

 select rank() over (order by members[1]) as group_num,  members
  from dedup
 order by group_num;

Работа с исходными данными.
Работа с ошибочными данными

Обновить

Учитывая ваши проблемы с размером данных, вы можете захотеть использовать цикл для решения этой проблемы:

  create or replace function group_consolidate() 
  returns table (group_num bigint, members int[]) 
as $$
  begin 
    create temp table group1 (id int not null, group_num int not null, primary key (id, group_num)) on commit drop;
    create temp table group2 (id int not null, group_num int not null, primary key (id, group_num)) on commit drop;
    
    with base as (
      select row_number() over (order by id_element) as group_num,
             id_element, id_secondary_element 
        from relation_table
    )
    insert into group1
    select base.id_element as id, base.group_num
      from base
     union
    select base.id_secondary_element as id, base.group_num
      from base
     order by 1, 2;
    
    while exists (select id from group1 group by id having count(*) > 1
                  union
                  select id from group2 group by id having count(*) > 1) loop

      raise notice 'Iteration %', clock_timestamp();
      truncate table group2;
      with reduce as (
        select a.group_num, min(b.group_num) new_group_num
          from group1 a
          join group1 b
            on a.id = b.id
           and a.group_num >=  b.group_num
         group by a.group_num
      )
      insert into group2
      select distinct g.id, r.new_group_num
        from group1 g
        join reduce r
          on r.group_num = g.group_num;

 
      truncate table group1;
      with reduce as (
        select a.group_num, min(b.group_num) new_group_num
          from group2 a
          join group2 b
            on a.id = b.id
           and a.group_num >=  b.group_num
         group by a.group_num
      )
      insert into group1
      select distinct g.id, r.new_group_num
        from group2 g
        join reduce r
          on r.group_num = g.group_num;


  end loop;
  return query select row_number() over (order by min(id)), array_agg(id order by id) as members
                 from group1
                      join table1 on table1.id_element = group1.id
                group by group1.group_num; 
end $$ language plpgsql;

Затем, чтобы использовать его:

 select * from group_consolidate();
NOTICE:  Iteration 2020-08-26 10:22:55.925093-04
NOTICE:  Iteration 2020-08-26 10:22:55.926501-04
 group_num |    members    
----------- ---------------
         1 | {2,3,4,6,132}
         2 | {5}
         3 | {9,11}
(3 rows)

И вот функция, которая перебирает отдельные записи, используя метод, с которым я столкнулся для сопоставления групп. Это должно быть медленнее, чем функция, описанная выше, из-за update операторов, которые накапливаются, но меня интересует, какова разница во времени для вашего набора данных.

 create or replace function onetab_consolidate() 
  returns table (group_num bigint, members int[])
as $$
  declare
    cur_loop cursor for
      select distinct array[
               least(id_element, id_secondary_element),
               greatest(id_element, id_secondary_element)
             ] as ary
        from relation_table
       order by ary; 
  begin
    create temporary table tempmatch (
      hid int not null primary key, 
      lid int not null
    ) on commit drop;

    for rec in cur_loop loop
      with new_rel as (
        select rec.ary[1] as lid, x.hid
          from unnest(rec.ary) as x(hid)
      ), upd_needed as (
        select distinct t.lid, min(least(t.lid, n.lid)) over () as newlid
          from tempmatch t
          join new_rel n on n.hid = t.hid 
      ), do_update as (
        update tempmatch
           set lid = un.newlid
          from upd_needed un 
         where un.lid = tempmatch.lid
           and un.newlid != tempmatch.lid
      )
      insert into tempmatch (hid, lid)
      select n.hid, coalesce(u.newlid, n.lid)
        from new_rel n
             cross join (select min(newlid) as newlid from upd_needed) u
      on conflict do nothing; 

    end loop;

    return query
      select dense_rank() over (order by min(hid)) as group_num,
             array_agg(hid order by hid) as members
        from tempmatch t
             join table1 on table1.id_element = t.hid
       group by lid
       order by group_num;
end $$ language plpgsql;

Для использования:

 select * from onetab_consolidate();
 group_num |    members    
----------- ---------------
         1 | {2,3,4,6,132}
         2 | {5}
         3 | {9,11}
(3 rows)

Для проверки результатов из обеих с использованием 6000 случайных отношений:

 select * from group_consolidate() a join  (select * from onetab_consolidate()) b on a.group_num = b.group_num and a.members != b.members;
NOTICE:  Iteration 2020-08-27 06:38:11.170305-04
NOTICE:  Iteration 2020-08-27 06:38:11.257287-04
 group_num | members | group_num | members 
----------- --------- ----------- ---------
(0 rows)

Time: 3013.271 ms (00:03.013)

select count(*) from group_consolidate();
NOTICE:  Iteration 2020-08-27 06:38:25.112396-04
NOTICE:  Iteration 2020-08-27 06:38:25.188946-04
 count 
-------
  2073
(1 row)

Time: 210.014 ms

select count(*) from onetab_consolidate();
 count 
-------
  2073
(1 row)

Time: 2782.337 ms (00:02.782)

Мой генератор тестовых данных:

 -- truncate table relation_table;
with randoms as (
  select floor(random() * 200000   1)::int as id1, 
         floor(random() * 200000   1)::int as id2
    from generate_series(1, 6000)
)
insert into relation_table
select * from randoms where id1 != id2
;

-- truncate table table1;
insert into table1
select *
  from generate_series(1,200000) where random() > 0.8;

1. Спасибо тебе, Майк, но это не работает должным образом. Если вы измените таблицу следующим образом:

2. @qbexample Я обновил свой ответ, чтобы исправить ошибку.

3. Ты молодец, Майк, я пробовал на своем примере, и это сработало, но когда я протестировал его на производстве (Table1 содержит 2.000 строк и Relation_table 28000) в течение 20 минут, он не был завершен. Есть ли какая-либо договоренность?

4. @qbexample Этот подход может быть слишком медленным. Каков результат select count(*) from relation_table r join table1 t1 on t1.id_element = r.id_element join table1 t2 on t2.id_element = r.id_secondary_element ? Является ли результат значительно меньшим, чем 28 000?

5. @qbexample Пожалуйста, попробуйте выполнить запрос здесь db-fiddle.com/f/cFUUW2fCfPRX3AkAm9YxLJ/4 Это уменьшает количество базовых элементов в iter CTE и игнорирует любые relation_table записи со значениями, не найденными в table1 . Если это все еще слишком медленно для ваших данных, то есть другой подход, который использует циклирование и временные таблицы.

Ответ №2:

Спасибо, Майк,

Всегда с помощью одного и того же теста я находил дубликаты:

выберите unnest (участники),count() из группы test_group_consolidate по unnest(участники), имеющей count() > 1 порядок по unnest (участники);

 INSERT INTO table2 (id_element) VALUES 
(19983)
,(20458)
,(20176)
,(19659)
,(567413)
,(19892)
,(5932)
,(20484)
,(20261)
,(19988)
,(19939)
,(20137)
;

INSERT INTO relation_table2 (id_element,id_secondary_element) VALUES 
(347133,346261)
,(95290,224342)
,(244702,204657)
,(97183,462539)
,(48864,48866)
,(86370,62853)
,(71824,77285)
,(477616,574296)
,(19659,20176)
,(516462,517132)
,(286373,340871)
,(57720,94328)
,(150074,412364)
,(499547,489631)
,(509899,367403)
,(341161,300297)
,(273910,241434)
,(261169,499283)
,(420699,420174)
,(574293,113781)
,(20176,567413)
,(26698,26258)
,(293765,293832)
,(485842,203903)
,(21233,28963)
,(218337,507312)
,(97183,97155)
,(563319,113781)
,(574293,124609)
,(423925,475583)
,(199786,201773)
,(33979,34823)
,(415817,341161)
,(19939,5932)
,(342705,343841)
,(77728,68220)
,(565348,136420)
,(289726,292819)
,(100574,42377)
,(27237,21147)
,(99398,99560)
,(365933,366012)
,(19659,20458)
,(321947,479974)
,(97183,97795)
,(91186,470477)
,(97965,58131)
,(367403,107286)
,(204123,495141)
,(99560,98583)
,(268360,495097)
,(97726,100574)
,(133571,134626)
,(503106,517592)
,(29369,33979)
,(97184,470462)
,(477116,489806)
,(136815,137227)
,(305482,305481)
,(23591,32745)
,(139195,139497)
,(100179,99398)
,(510076,322241)
,(548956,503410)
,(510743,518816)
,(475545,495703)
,(203925,204950)
,(457781,454316)
,(99398,99521)
,(446427,449889)
,(300297,341161)
,(218337,219036)
,(432788,432627)
,(47098,47001)
,(328419,340259)
,(20176,20137)
,(305348,286449)
,(516464,516463)
,(491821,473827)
,(507312,219036)
,(417130,186087)
,(39395,49318)
,(321888,323168)
,(365933,365880)
,(344847,345201)
,(367701,367403)
,(323168,321888)
,(491821,203540)
,(23591,31941)
,(342705,342159)
,(565348,136419)
,(100179,98583)
,(485842,324334)
,(20176,19988)
,(113781,563319)
,(47606,46939)
,(403537,351085)
,(353592,349450)
,(218349,218230)
,(518775,77285)
,(241538,273910)
,(285413,475114)
,(293765,328419)
,(91092,90107)
,(21726,22420)
,(265849,267666)
,(142561,134537)
,(514190,500724)
,(246838,246808)
,(200346,504095)
,(114759,135208)
,(90479,304912)
,(90628,494151)
,(22142,139497)
,(322651,497643)
,(496564,497368)
,(275738,241538)
,(493766,493247)
,(202552,489441)
,(574293,574295)
,(23591,23611)
,(465858,465548)
,(423925,424999)
,(241538,241434)
,(554660,30026)
,(100179,99521)
,(493363,493252)
,(417062,344313)
,(22855,565862)
,(462572,514469)
,(199786,198405)
,(458396,443427)
,(20176,19659)
,(200461,204853)
,(421619,396140)
,(287978,287881)
,(417062,344265)
,(517132,516463)
,(420327,420149)
,(200461,203137)
,(21726,21367)
,(275738,42723)
,(246838,246807)
,(417615,107117)
,(489419,88552)
,(30767,34823)
,(136987,137044)
,(150074,509467)
,(244702,475544)
,(103430,473827)
,(47573,47668)
,(129917,107117)
,(460567,460210)
,(201298,483702)
,(420327,420904)
,(459867,455799)
,(19659,19983)
,(523586,577430)
,(458396,449326)
,(28379,32745)
,(515377,489087)
,(23618,23646)
,(366405,366481)
,(410236,411683)
,(2026,113474)
,(499547,493187)
,(366405,366595)
,(21726,21876)
,(420327,420603)
,(322651,323255)
,(94644,102164)
,(286373,413743)
,(499547,493473)
,(516464,516462)
,(420768,420053)
,(20176,19892)
,(20176,19983)
,(107117,129917)
,(174987,439505)
,(66640,491408)
,(205695,486233)
,(203925,514528)
,(482833,204591)
,(427434,156566)
,(338116,476809)
,(88296,89599)
,(418928,417615)
,(487856,32108)
,(417615,129711)
,(99398,98583)
,(107117,129711)
,(108003,134564)
,(321947,479122)
,(415817,414664)
,(513001,513000)
,(3133,327745)
,(200580,224609)
,(21032,21032)
,(136419,136420)
,(19070,21700)
,(94464,98352)
,(81670,293766)
,(473827,103430)
,(203049,203051)
,(109648,105403)
,(42372,105547)
,(23076,23455)
,(475545,475541)
,(432982,432842)
,(73646,161680)
,(343935,344110)
,(416184,186087)
,(474300,433800)
,(421619,375601)
,(420699,304138)
,(95290,224344)
,(71824,77728)
,(21726,21583)
,(565348,140235)
,(77285,77728)
,(517531,223984)
,(236707,35523)
,(33979,30767)
,(200794,197748)
,(260719,50593)
,(423925,423928)
,(156566,427434)
,(366737,366736)
,(517132,516462)
,(487856,495153)
,(420768,458544)
,(516466,516464)
,(375601,396140)
,(347357,347725)
,(23824,27867)
,(292819,296716)
,(77285,68220)
,(344847,343789)
,(246838,246809)
,(445993,445994)
,(32159,33628)
,(102968,489054)
,(95745,91692)
,(23591,25927)
,(411683,410236)
,(88296,502544)
,(458396,449328)
,(200580,188106)
,(129917,129711)
,(338671,338380)
,(574293,8497)
,(95081,324093)
,(47573,501048)
,(30767,571281)
,(265502,273910)
,(423925,423926)
,(30643,28568)
,(513532,484413)
,(434075,382798)
,(367701,107286)
,(47606,47381)
,(26950,8339)
,(324048,503526)
,(95892,328375)
,(413108,413108)
,(97726,96844)
,(517531,495600)
,(417130,416184)
,(19659,20261)
,(493337,493252)
,(512127,457953)
,(87999,93566)
,(47573,47617)
,(236707,228537)
,(273910,275738)
,(492781,88103)
,(274868,565862)
,(173062,182262)
,(419087,421399)
,(516463,517132)
,(293766,293832)
,(246806,246808)
,(99398,100179)
,(370190,57160)
,(97184,97948)
,(87999,93602)
,(200461,203213)
,(139195,142437)
,(71824,77332)
,(574293,477616)
,(253935,274118)
,(516463,516464)
,(195245,191285)
,(19659,19988)
,(290057,66437)
,(30767,33979)
,(516464,517132)
,(101121,311967)
,(97965,97726)
,(150074,150224)
,(133537,235654)
,(420768,420851)
,(32492,32489)
,(123929,479086)
,(260659,249872)
,(139195,139109)
,(200346,201929)
,(203925,201567)
,(287907,290531)
,(273910,237094)
,(26698,26950)
,(482833,204590)
,(28369,32215)
,(554660,555585)
,(56832,102880)
,(237745,242271)
,(288015,346642)
,(476809,354274)
,(416920,434467)
,(458396,451732)
,(95290,93176)
,(224206,514616)
,(197748,202849)
,(491821,130394)
,(445993,457899)
,(461880,467496)
,(199786,203205)
,(204590,204591)
,(517482,43030)
,(22142,139109)
,(304138,420699)
,(286373,413310)
,(156468,214434)
,(474951,523171)
,(19659,20137)
,(475545,482639)
,(8497,113781)
,(139557,573654)
,(136987,142688)
,(477616,574293)
,(266925,267742)
,(475545,482784)
,(100574,42372)
,(97184,96583)
,(518775,71824)
,(499547,498554)
,(28369,500856)
,(32745,28379)
,(198405,324150)
,(89146,88556)
,(417062,418169)
,(445993,436630)
,(94644,68890)
,(506152,298762)
,(577208,577215)
,(300297,414664)
,(202027,244746)
,(344847,345200)
,(68220,8497)
,(204046,435570)
,(513532,536041)
,(493766,493245)
,(396140,375601)
,(477616,574295)
,(79045,101121)
,(462697,482834)
,(509899,367564)
,(30767,34824)
,(285413,283902)
,(33979,34824)
,(23591,23605)
,(445993,454818)
,(454229,454158)
,(408401,519901)
,(349599,414911)
,(516462,516464)
,(367701,367564)
,(42372,100574)
,(42372,45419)
,(20176,20484)
,(474299,433800)
,(23623,23646)
,(408401,514877)
,(19659,20484)
,(90628,498794)
,(21233,33922)
,(396140,421619)
,(366362,265350)
,(408401,499129)
,(185507,57731)
,(462572,506631)
,(287978,303277)
,(326098,311967)
,(150074,67052)
,(241538,265515)
,(461880,466048)
,(42377,188299)
,(23591,23250)
,(156468,182830)
,(477616,113781)
,(365933,365686)
,(366291,366362)
,(146144,483557)
,(99560,99398)
,(129842,119812)
,(275738,237094)
,(22142,142437)
,(420327,420599)
,(42372,193525)
,(240332,487601)
,(20176,19939)
,(241538,237094)
,(417615,418928)
,(34824,33979)
,(411022,415520)
,(100574,45419)
,(518775,77728)
,(105547,42377)
,(130390,494967)
,(367701,509899)
,(337777,51738)
,(460169,459865)
,(338671,451512)
,(420768,458513)
,(193525,33325)
,(275738,241434)
,(327745,3133)
,(462697,482835)
,(218230,218349)
,(93176,66352)
,(346903,346269)
,(246838,249319)
,(508571,224577)
,(366362,366291)
,(173062,184717)
,(136419,565348)
,(205695,190265)
,(344847,349900)
,(365933,365785)
,(417062,343964)
,(97726,13567)
,(473902,473904)
,(338116,354274)
,(365933,365894)
,(105547,105403)
,(23591,23632)
,(94269,100835)
,(418928,129711)
,(273910,265502)
,(199786,204990)
,(287907,303271)
,(237088,230540)
,(27562,516462)
,(517132,516466)
,(516463,516466)
,(134564,108003)
,(414664,341161)
,(105403,107492)
,(265849,267376)
,(420603,425301)
,(282962,282971)
,(26624,26052)
,(21726,20507)
,(236707,236575)
,(34824,29369)
,(95745,97497)
,(424999,423926)
,(136987,141964)
,(485965,523204)
,(129842,111596)
,(19207,21032)
,(487601,240332)
,(47606,47456)
,(71824,68220)
,(473827,198471)
,(77285,77332)
,(420768,458481)
,(100179,99560)
,(75839,93309)
,(88296,489548)
,(105547,107492)
,(577208,566644)
,(95081,513835)
,(47098,47085)
,(287907,289134)
,(136987,142561)
,(325714,324701)
,(94644,95892)
,(293766,81670)
,(365933,365877)
,(203925,202415)
,(474299,474300)
,(32492,32490)
,(445993,531028)
,(77728,77332)
,(375601,421619)
,(485965,521183)
,(517132,516464)
,(418928,107117)
,(29369,30767)
,(26698,26819)
,(408401,522095)
,(136987,137794)
,(199499,500811)
,(199786,202199)
,(516466,516463)
,(293765,348556)
,(105403,105547)
,(420768,420320)
,(434075,252448)
,(47573,47497)
,(200580,188122)
,(370190,470631)
,(346261,347133)
,(266925,483608)
,(26698,26530)
,(445993,455651)
,(235335,92784)
,(201298,472349)
,(136419,140235)
,(42372,42377)
,(289726,291294)
,(23618,23623)
,(474300,433891)
,(97935,481263)
,(416920,420108)
,(128348,129425)
,(555375,56238)
,(292671,168022)
,(324640,324291)
,(8497,68220)
,(23623,23618)
,(274868,22855)
,(71824,518775)
,(75839,87384)
,(518775,77332)
,(8497,574293)
,(202552,204673)
,(241920,244053)
,(424999,423928)
,(265350,247905)
,(516463,516462)
,(424999,475583)
,(516466,517132)
,(21726,21707)
,(170064,169024)
,(136815,133522)
,(202552,202653)
,(21726,21232)
,(509899,107286)
,(275738,273910)
,(330248,395840)
,(29369,34824)
,(292819,293766)
,(516464,516466)
,(420327,182568)
,(88296,89521)
,(288015,346641)
,(367403,367564)
,(26950,32160)
,(97726,42372)
,(341514,344891)
,(458396,459259)
,(290945,129917)
,(474951,324144)
,(56832,34347)
,(97726,58131)
,(420174,420699)
,(417062,344053)
,(508571,246277)
,(547443,34594)
,(266925,267051)
,(130390,508158)
,(342705,343519)
,(577430,523586)
,(20176,20458)
,(324640,482104)
,(517531,475013)
,(304138,303392)
,(445994,457899)
,(156468,156469)
,(354274,476809)
,(34824,34823)
,(23591,29516)
,(42377,144086)
,(23632,31941)
,(23618,20980)
,(51738,43325)
,(413310,202368)
,(447093,457251)
,(509899,367701)
,(88552,489419)
,(420768,458282)
,(548956,548977)
,(35316,493792)
,(477616,124609)
,(97184,98012)
,(101121,326098)
,(28369,28632)
,(403537,390953)
,(419087,418397)
,(202027,471268)
,(325714,481256)
,(474951,322651)
,(129917,417615)
,(19659,19892)
,(218180,218349)
,(102545,102535)
,(420768,303376)
,(287978,281150)
,(507312,218337)
,(32171,31506)
,(516466,516462)
,(366291,265350)
,(366291,265408)
,(268939,524032)
,(58131,73646)
,(508550,33809)
,(370190,330512)
,(268331,463164)
,(23455,23076)
,(424999,423925)
,(21700,19070)
,(474299,433891)
,(285413,305952)
,(516462,516463)
,(343964,344053)
,(347357,347190)
,(367403,509899)
,(22855,274868)
,(508868,500865)
,(218349,218180)
,(518775,68220)
,(33979,29369)
,(416184,417130)
,(29369,34823)
,(324481,510987)
,(105547,109648)
,(105547,45419)
,(381445,502788)
,(99560,99521)
,(474300,474299)
,(198405,199786)
,(510743,518818)
,(420768,458533)
,(290057,439384)
,(27237,17005)
,(342705,344770)
,(557407,569214)
,(99560,100179)
,(508571,482492)
,(199396,516942)
,(47606,47516)
,(23442,106434)
,(265502,265515)
,(237745,232760)
,(57720,435760)
,(515377,489086)
,(573654,577430)
,(188288,188295)
,(23591,26233)
,(241538,42723)
,(321947,495049)
,(97935,476554)
,(279121,411089)
,(204841,501427)
,(365933,365921)
,(508868,510077)
,(204959,503276)
,(322651,324442)
,(237088,242368)
,(412797,413108)
,(476564,492620)
,(421399,419087)
,(246838,246806)
,(30767,29369)
,(508571,243959)
,(90479,457779)
,(47573,47471)
,(273910,42723)
,(202552,524204)
,(23623,20980)
,(268331,267355)
,(201298,493465)
,(42372,282962)
,(265350,260659)
,(499547,493501)
,(5932,19939)
,(204529,202644)
,(516462,516466)
,(22142,139195)
,(97935,496138)
,(47606,47367)
,(292819,290932)
,(420699,303392)
,(574293,574296)
,(20176,20261)
,(275417,243034)
,(129917,418928)
,(109648,107492)
,(241538,275738)
,(483557,146144)
,(265350,249872)
,(366362,265408)
,(506803,524066)
,(253935,260779)
,(23591,28379)
,(344847,343326)
,(202552,204412)
,(34824,30767)
,(365933,365675)
,(455879,211842)
,(287978,304095)
,(265515,265502)
,(341161,414664)
,(366405,99235)
,(185571,187316)
,(474951,324481)
,(414664,300297)
,(458396,459486)
,(367403,367701)
,(23442,106893)
,(23632,26233)
,(241920,241922)
,(42372,105403)
,(508550,33573)
,(491821,494855)
;

ты скоро узнаешь
19939 2 события
20176 2 события

например, эти:

 |group_num           |members    |
|--------------------|-----------|
|3                   |
{5932,19939,20176}|
|10                   {19659,19892,19939,19983,19988,20137,20176,20261,20458,20484,567413}

1. Я еще раз взгляну на первую функцию. Если у вас есть несколько примеров, которые отображались как дубликаты, не могли бы вы, пожалуйста, добавить их relation_table строки? Что касается любых дубликатов в последней функции, структура временной таблицы делает дубликаты невозможными, поскольку этот столбец является первичным ключом.

2. Извините. В первой функции произошла ошибка. Я исправил это в своем ответе. В поле вставить в таблицу group1 в нижней части цикла это должно быть left join возвращением к CTE.

3. @MikeOrganek это не работает, с первой функцией у меня много дубликатов. Если вы хотите, я мог бы отправить вам два файла с экземплярами create, чтобы помочь вам протестировать вашу функцию.

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

5. У меня исправлена функция group. Он работает намного быстрее в моем тестовом наборе данных и выдает те же результаты, что и гораздо более медленная вторая функция. Обновленный код приведен в моем ответе выше. Я сгенерировал 99 998 случайных строк для relation_table , и это завершилось за 8 секунд. Имея 60 000 строк, он завершается менее чем за 3 секунды.