#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 секунды.