Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
Существует большой список элементов, у каждого из них есть 2 признака, хранящиеся в полях f1 и f2

+ declare @t table...
declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,1,222),
(3,0,222),
(4,3,222),
(5,4,222),
(6,3,333),
(7,4,555),
(8,0,777),
(9,5,888),
(10,5,999)


idf1f2
11111
21222
30222
43222
54222
63333
74555
80777
95888
105999


нужно объединить элементы в одну группу по следующему принципу:
1. ищем все элементы с совпадающим значением в f1 и объединяем их в группы
2. для каждого элемента из группы, получившейся на предыдущем шаге, ищем во всей таблице элементы с таким же значением в f1 и дополняем группу найденными элементами, если у элемента группы в поле f1 установлен ноль, то для таких поиск совпадений искать не нужно
3. если элемент уже есть в какой-то группе, то для него новую группу создавать не нужно, т.е. если элемент уже попал хотя бы в одну группу, то он не должен встречаться в других группах

результат должен быть таким

idf1f2group
111111
212221
302221
432221
542221
633331
745551
807772
958883
1059993


поля f1 и f2 из результирующей выборки можно исключить

единственное, что приходит в голову - это многократное соединение таблицы с самой собой и соединение результатов выборки с результатами выборки, получается какая-то дичь

может есть более красивое решение?
14 сен 17, 14:10    [20795839]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
Рекурсия
14 сен 17, 14:14    [20795859]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
aleks222
Guest
TJ001
нужно объединить элементы в одну группу по следующему принципу:
1. ищем все элементы с совпадающим значением в f1 и объединяем их в группы
2. для каждого элемента из группы, получившейся на предыдущем шаге, ищем во всей таблице элементы с таким же значением в f1 и дополняем группу найденными элементами, если у элемента группы в поле f1 установлен ноль, то для таких поиск совпадений искать не нужно


Я один ничо не понял?

"все элементы с совпадающим значением в f1"
"элементы с таким же значением в f1"

В чем разница, Карл?
14 сен 17, 14:16    [20795870]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
TJ001
1. ищем все элементы с совпадающим значением в f1 и объединяем их в группы

извиняюсь, очепятка
должно быть так:
1. ищем все элементы с совпадающим значением в f2 и объединяем их в группы
14 сен 17, 14:18    [20795883]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
aleks222
Guest
iap
Рекурсия

Нахрена?

Два update справятся.
14 сен 17, 14:20    [20795892]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
iap
Member

Откуда: Москва
Сообщений: 46977
aleks222
iap
Рекурсия

Нахрена?

Два update справятся.
1. Возьмём все записи с f1=1
2. Добавим записи с f2, полученными в п.1
3. Добавим записи с f1, полученными в п.2
и т.д. пока добавлять будет нечего.

N. Берём записи с f1, не попавшим в сформированное до этого множество.
N+1. Повторяем предыдущий алгоритм отбора с п. 2.

Что это, если не рекурсивный обход, ибо количество итераций неизвестно?
14 сен 17, 14:30    [20795954]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
получилось как-то так...

declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,1,222),
(3,0,222),
(4,3,222),
(5,4,222),
(6,3,333),
(7,4,555),
(8,0,777),
(9,5,888),
(10,5,999)

drop table if exists #tmp

select
isnull(t2.id,t1.id)id
,Dense_Rank() over( order by t1.f2) as Grp
,Count( * ) over ( partition by t1.f2) as GrpCnt
into #tmp
from @t t1
left join @t t2
on t2.f1=t1.f1 and t1.f1<>0

select distinct id
     , ( select top 1 Grp
        from #tmp as tt
        where tt.id = t.id
        order by GrpCnt desc, Grp desc)
from #tmp as t

drop table if exists #tmp
14 сен 17, 15:20    [20796152]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
а вот так еще лучше

select id
,Grp
 from (
select 
id
,Grp
,GrpCnt
,row_number() over (partition by id order by GrpCnt desc, Grp desc) RN
from(
select
isnull(t2.id,t1.id)id
,Dense_Rank() over( order by t1.f2) as Grp
,Count( * ) over ( partition by t1.f2) as GrpCnt
from @t t1
left join @t t2
on t2.f1=t1.f1 and t1.f1<>0
)a)b
where RN =1


что скажете, господа?
14 сен 17, 15:59    [20796289]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
aleks222
Guest
TJ001
а вот так еще лучше

select id
,Grp
 from (
select 
id
,Grp
,GrpCnt
,row_number() over (partition by id order by GrpCnt desc, Grp desc) RN
from(
select
isnull(t2.id,t1.id)id
,Dense_Rank() over( order by t1.f2) as Grp
,Count( * ) over ( partition by t1.f2) as GrpCnt
from @t t1
left join @t t2
on t2.f1=t1.f1 and t1.f1<>0
)a)b
where RN =1


что скажете, господа?

Плохо.
14 сен 17, 16:05    [20796310]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
aleks222
Плохо.

хороший вариант покажете?
14 сен 17, 16:07    [20796317]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
aleks222
Guest
TJ001
aleks222
Плохо.

хороший вариант покажете?


declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,1,222),
(3,0,222),
(4,3,222),
(5,4,222),
(6,3,333),
(7,4,555),
(8,0,777),
(9,5,888),
(10,5,999)
;
with t0 as ( select * from @t )
   , t1 as ( select * from t0 where exists( select * from t0 as x where x.f2 = t0.f2 and x.id <> t0.id ) )
   , t2  as ( select t0.*, g = isnull(x.f2, t0.f2) from t0 outer apply ( select top(1) * from t1 where f1 = t0.f1 and f1 <> 0 order by f2 ) as x )
  select *, Grp = Dense_Rank() over( order by g ) from t2;
14 сен 17, 20:27    [20797149]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
aleks222,

а t0 зачем
15 сен 17, 08:57    [20797752]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
если использовать вот такие входные данные, то элемент с id=17 выпадает в отдельную группу, даже не связанную с id=16, будто он сам по себе

declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,1,222),
(3,0,222),
(4,3,222),
(5,4,222),
(6,3,333),
(7,4,555),
(8,0,777),
(9,5,888),
(10,5,999),
(11,6,9991),
(12,6,8881),
(13,6,99911),
(14,7,9991),
(15,7,9992),
(16,7,9993),
(17,0,9993)


пока даже не знаю как это победить, по идее он должен был бы попасть в одну группу с 11,12,13,14,15,16
15 сен 17, 10:25    [20797950]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
aleks222,

declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,1,222),
(3,0,222),
(4,3,222),
(5,4,222),
(6,3,333),
(7,4,555),
(8,0,777),
(11,1,777),
(9,5,888),
(10,5,999)
;
?
15 сен 17, 10:30    [20797971]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
TJ001,

Для приведенного мной набора данных какой должен быть результат? Куда должна попасть запись с id=1? К 222 или 777?
15 сен 17, 10:32    [20797981]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
Павел Воронцов,

в идеале они все (ид: 1,2,3,4,5,6,7,11,8) должны быть в одной группе

из такого набора по идее должна получиться одна группа
declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,2,111),
(3,2,333),
(4,4,333),
(5,4,555),
(6,0,555)
15 сен 17, 11:18    [20798153]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
aleks222
Guest
TJ001
Павел Воронцов,

в идеале они все (ид: 1,2,3,4,5,6,7,11,8) должны быть в одной группе

из такого набора по идее должна получиться одна группа
declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,2,111),
(3,2,333),
(4,4,333),
(5,4,555),
(6,0,555)


Шобы "транзитивные связи" учесть - надо херачить update в цикле.

Однако, в вашем первоначальном описании этого НЕ написано.
Учись описывать задачу.
15 сен 17, 11:40    [20798258]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
aleks222,
Когда я открыл тему, то еще не столкнулся с таким нюансом, он выяснился позже - при проверке результатов. На моих данных (400к записей) она встретилась лишь раз
15 сен 17, 11:53    [20798312]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
TJ001,

declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,1,222),
(3,0,222),
(4,3,222),
(5,4,222),
(6,3,333),
(7,4,555),
(8,0,777),
(11,1,777),
(9,5,888),
(10,5,999)
;

with t0 (f2,f22) as ( select distinct a.f2, b.f2 as f22 from @t a join @t b on a.f2 <= b.F2 and a.f1 = b.f1 and a.f1!=0)
, t1 as (select a.f2, a.f22, 1 as lvl from t0 a where a.f2 <> a.f22
     union all
	 select a.f2, b.f22, a.lvl+1 from t1 as a join t0 as b on b.f2 = a.f22 and a.lvl < 90  and b.f2 <> b.f22
	 )
, t2 as (select b.f2, b.f22, b.lvl from t1 as b -- where not exists(select 1 from t1 as c where b.f2 = c.f22)
	union all
	 select a.f2, a.f22, 0 as lvl from t0 as a where a.f22 = a.f2 )
, t3 as (select min(f2) as grp, f22 from t2 group by f22)
select tt.*, t3.grp from @t tt join t3 on tt.f2 = t3.f22
15 сен 17, 14:35    [20798958]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
TJ001,
поправил

declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,1,222),
(3,0,222),
(4,3,222),
(5,4,222),
(6,3,333),
(7,4,555),
(8,0,777),
(11,1,777),
(9,5,888),
(10,5,999)
;

with t0 (f2,f22) as ( select distinct a.f2, b.f2 as f22 from @t a join @t b on a.f2 <= b.F2 and a.f1 = b.f1 and a.f1!=0)
, t1 as (select a.f2, a.f22, 1 as lvl from t0 a where a.f2 <> a.f22
     union all
	 select a.f2, b.f22, a.lvl+1 from t1 as a join t0 as b on b.f2 = a.f22 and a.lvl < 90  and b.f2 <> b.f22
	 )
, t2 as (select b.f2, b.f22, b.lvl from t1 as b -- where not exists(select 1 from t1 as c where b.f2 = c.f22)
	union all
	 select distinct a.f2 as f2, a.f2 as f22, 0 as lvl from @t as a)
, t3 as (select min(f2) as grp, f22 from t2 group by f22)
select tt.*, t3.grp from @t tt join t3 on tt.f2 = t3.f22
15 сен 17, 14:39    [20798972]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
TJ001,

Еще вариант, без cte
declare @t table (id int,f1 int, f2 int)
insert into @t values 
(1,1,111),
(2,1,222),
(3,0,222),
(4,3,222),
(5,4,222),
(6,3,333),
(7,4,555),
(8,0,777),
(11,1,777),
(9,5,888),
(10,5,999)
;
with t0 as (select f1 from @t where f1 != 0 group by f1 having count(distinct f2) > 0)
, t1 as (select tt0.f1, tt1.f1 as f2 from t0 tt0 join t0 tt1 on tt0.f1 < tt1.f1
where exists (select 1 from @t t where t.f1 = tt0.f1 and exists (select 1 from @t tt where tt.f2 = t.f2 and tt.f1 = tt1.f1))
)
, t2 as (select min(tt1.f1) as grp, tt1.f2 
from (select f1, f2 from t1 
union all
select distinct tt2.f1, tt2.f1 as f2 from @t tt2 where f1 != 0)
tt1 group by tt1.f2)
select tt.*, isnull(isnull(t2.grp,(select min(t_in.f1) from @t t_in where t_in.f2 = tt.f2 and t_in.f1 != 0)),tt.f2) as grp
 from @t tt left join t2 on tt.f1 = t2.f2 
18 сен 17, 08:49    [20802381]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
Павел Воронцов,

поторопился, без cte не получится. Ну да сами доделайте.... Принцип тот же, что и в первом варианте.
18 сен 17, 08:51    [20802384]     Ответить | Цитировать Сообщить модератору
 Re: Перекрывающее объединение записей в одну группу  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 408
Павел Воронцов,

Спасибо! Бум смотреть Картинка с другого сайта.
18 сен 17, 15:11    [20803694]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить