ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / вопрос по СТЕ
8 сообщений из 8, страница 1 из 1
вопрос по СТЕ
    #39952780
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
Всем привет.

Застрял на задаче, помогите решить ее, с рекурсиями на ВЫ.

Задача: В исходных данных есть колонка "RelationPartnerId", это ссылка на одного из партнеров указанных в колонке "PartnerId" (если значение 0, тогда партнер не ссылается на другого), помимо этого в таблице уже есть первичный набор связей "RelationId", который так же нужно учесть в расчете.

Нужно собрать все и всех в общие связи (на основании колонок "RelationPartnerId" и "RelationId"), имя полученных связей = максимальному значению из колонки "RelationId".

Код: 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.
  create table #Temp_PartnerRelations
       ( PartnerId         Int primary key  -- Идентификатор партнера
       , RelationPartnerId Int              -- Идентификатор партнера из другой учетной системы (0 - не связан)
       , RelationId        Char( 24)       -- Идентификатор первичной связи
       )

insert into #Temp_PartnerRelations
    -- set1
 values( 3048380, 267467, 'erp-3049044|3')
        , ( 3049044, 178700, 'erp-3049044|3')
        , ( 267467,  0, '1c7-178700')
        , ( 267637,  0, '1c7-178700')
        , ( 178700,  0, '1c7-178701')

    -- set2   
        , ( 8888, 0, 'erp-8888|3')
        , ( 7777, 8888, '1c7-8888')
        , ( 1111,  0, '1c7-2222')
        , ( 2222,  0, '1c7-2222')


create nonclustered index Index_RelatonId  -- данных около 600 000 строк
          on #Temp_PartnerRelations
              ( RelationId asc
              )



Таким образом, Set1 должен собраться в одну связь, Set2 соберется в две связи, на выходе получается 3 связи:

Результат:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select PartnerId
     , RelationPartnerId
     , Relation
from(
   values( 3048380, 267467, 'erp-3049044|3')
          , ( 3049044, 178700, 'erp-3049044|3')
          , ( 267467,  0, 'erp-3049044|3')
          , ( 267637,  0, 'erp-3049044|3')
          , ( 178700,  0, 'erp-3049044|3')
          , ( 8888, 0, 'erp-8888|3')
          , ( 7777, 8888, 'erp-8888|3')
          , ( 1111,  0, '1c7-2222')
          , ( 2222,  0, '1c7-2222')
    ) as x ( PartnerId
	       , RelationPartnerId
           , Relation
           )




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

Решал так:

Код: 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.
   ;with cte( PartnerId
            , RelationPartnerId
            , RelationId
			)
    as( 
        select top 1 PartnerId
             , RelationPartnerId
             , RelationId 
        from #Temp_PartnerRelations
        where RelationId <> ''
              and RelationId not like 'calc%'  -- сопоставленные партнеры с клиентами 1С7, неимеющие связи
		
		union all

        select tmp.PartnerId
             , tmp.RelationPartnerId
             , tmp.RelationId
        from #Temp_PartnerRelations as tmp
             inner join cte
                        on tmp.PartnerId = cte.RelationPartnerId
                            or tmp.RelationId = cte.RelationId
		
		union all

        select tmp.PartnerId
             , tmp.RelationPartnerId
             , tmp.RelationId
        from #Temp_PartnerRelations as tmp
             inner join cte
                        on tmp.RelationId = cte.RelationId
                           and tmp.RelationId <> ''
						   and tmp.RelationId not like 'calc%'
						   and tmp.PartnerId <> cte.PartnerId
                           
      )
    select PartnerId
         , RelationPartnerId
         , Max( RelationId) as Rel
		 , Count(*)
    from cte
	group by PartnerId
           , RelationPartnerId
    option( maxrecursion 30000)

но результата не могу получить, зацикливается.....вроде все делал по примеру, но они простые, с одним анкером. Помогите плз... =(
...
Рейтинг: 0 / 0
29.04.2020, 12:03
| Ответить | Цитировать | Написать  
вопрос по СТЕ
    #39952783
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
поспешил с публикацией, пытался решать так:

Код: 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.
   ;with cte( PartnerId
            , RelationPartnerId
            , RelationId
			)
    as( 
        select top 1 PartnerId
             , RelationPartnerId
             , RelationId 
        from #Temp_PartnerRelations
        where RelationId <> ''
              and RelationId not like 'calc%'  -- сопоставленные партнеры с клиентами 1С7, неимеющие связи
		
		union all

        select tmp.PartnerId
             , tmp.RelationPartnerId
             , tmp.RelationId
        from #Temp_PartnerRelations as tmp
             inner join cte
                        on tmp.PartnerId = cte.RelationPartnerId
		
		union all

        select tmp.PartnerId
             , tmp.RelationPartnerId
             , tmp.RelationId
        from #Temp_PartnerRelations as tmp
             inner join cte
                        on tmp.RelationId = cte.RelationId
                           and tmp.RelationId <> ''
						   and tmp.RelationId not like 'calc%'
                           
      )
    select PartnerId
         , RelationPartnerId
         , Max( RelationId) as Rel
		 , Count(*)
    from cte
	group by PartnerId
           , RelationPartnerId
    option( maxrecursion 30000)
...
Рейтинг: 0 / 0
29.04.2020, 12:11
| Ответить | Цитировать | Написать  
вопрос по СТЕ
    #39952802
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
IDVT,

Вы хотите обойти все ветки, найти среди RelationId этой ветки максимальное значение и присвоить каждой записи этой ветки полученное значение? Причем, началом ветки считается строка, у которой RelationPartnerId = 0? Верно?
...
Рейтинг: 0 / 0
29.04.2020, 12:49
| Ответить | Цитировать | Написать  
вопрос по СТЕ
    #39952834
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
Владислав Колосов,

Да, все верно.
...
Рейтинг: 0 / 0
29.04.2020, 13:36
| Ответить | Цитировать | Написать  
вопрос по СТЕ
    #39953230
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
IDVT,

не думаю, что эта задача эффективно решается без использования hierarchyid типа данных. Выполнение рекурсивного обхода по множеству корней одним запросом на достаточно большом объеме данных будет медленным. Для иерархического типа можно попробовать сгруппировать все элементы по корневому и среди них найти максимальное значение.
...
Рейтинг: 0 / 0
30.04.2020, 12:02
| Ответить | Цитировать | Написать  
вопрос по СТЕ
    #39953285
IDVT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IDVT
Участник
Владислав, спасибо за помощь! Буду открывать для себя hierarchyid.
...
Рейтинг: 0 / 0
30.04.2020, 14:03
| Ответить | Цитировать | Написать  
вопрос по СТЕ
    #39953413
nullin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nullin
Гость
IDVT, Если речь идёт о дереве, charindex следует убрать.
А для скорости стоит надеть на это битовую маску

 
Код: 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.
with RecursiveCTE as
(select 0 as LevelCTE
      , t.PartnerId
      , t.RelationPartnerId
      , t.RelationId
      , convert(varchar(max), concat('/', t.PartnerId, '/')) as PartnerIdChain
      , 0 as PartnerIdChainEndFlag
   from #Temp_PartnerRelations as t
  where t.RelationPartnerId = 0
 
  union all
 
 select r.LevelCTE + 1 as LevelCTE
      , case a.PartnerIdChainEndFlag
         when 0 then a.PartnerId 
                else r.PartnerId
        end as PartnerId
      , case a.PartnerIdChainEndFlag
         when 0 then a.RelationPartnerId
                else r.RelationPartnerId
        end as RelationPartnerId
      , case a.PartnerIdChainEndFlag
         when 0 then a.RelationId
                else r.RelationId
        end as RelationId
      , case a.PartnerIdChainEndFlag
         when 0 then concat(r.PartnerIdChain, a.PartnerId, '/')
                else r.PartnerIdChain
        end as PartnerIdChain
      , a.PartnerIdChainEndFlag
   from RecursiveCTE as r
  outer apply(select t.PartnerId
                   , t.RelationPartnerId
                   , t.RelationId
                   , 0 as PartnerIdChainEndFlag
                from #Temp_PartnerRelations as t
               where r.PartnerId = t.RelationPartnerId
                 and charindex(r.PartnerIdChain, t.PartnerId) = 0 /*Когда граф*/) as a
  where r.PartnerIdChainEndFlag = 0)
  
select r.LevelCTE
     , r.PartnerId
     , r.RelationPartnerId
     , r.RelationId
     , r.PartnerIdChain
     , r.PartnerIdChainEndFlag
  from RecursiveCTE as r
 where r.PartnerIdChainEndFlag is null
 
 order by r.LevelCTE

option(maxrecursion 0);

fiddle
...
Рейтинг: 0 / 0
30.04.2020, 20:40
| Ответить | Цитировать | Написать  
вопрос по СТЕ
    #39953414
nullin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nullin
Гость
А да, точно PartnerId - int primary key. Строчку с charindex нужно затереть
...
Рейтинг: 0 / 0
30.04.2020, 20:43
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / вопрос по СТЕ
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=61&tid=1686168]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 41ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 57ms
get tp. blocked users:
 2ms
others: 306ms
total:  451ms
созд. / загр.: 451ms
x
x
Закрыть


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