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

Откуда:
Сообщений: 23
Товарищи здравствуйте!

Существует модельная таблица следующей структуры:
+

CREATE TABLE #BUHPROV (
   UID INT --ПК
,  SCHET_DT VARCHAR(21) --Счет дебета
,  ANALYT_DT#1# VARCHAR(200) --Аналитика дебета 1
,  ANALYT_DT#2# VARCHAR(200) --2
,  TYPE_ANALYT_DT#1# INT --Тип аналитики 1
,  TYPE_ANALYT_DT#2# INT --2
,  SCHET_KT VARCHAR(21) --Счет кредита
,  ANALYT_KT#1# VARCHAR(200) --Аналитика кредита 1
,  ANALYT_KT#2# VARCHAR(200) --2
,  TYPE_ANALYT_KT#1# INT --Тип аналитики 1
,  TYPE_ANALYT_KT#2# INT --2
,  DATE_PROV DATE --Дата проведения
,  MNTH_PROV DATE --Месяц проводки
,  INDEX i1 CLUSTERED (UID)
)

INSERT INTO #BUHPROV VALUES
(1,'0001','Шины','Камаз',1,2,'0002','Покупка','Прямые затраты',3,4,'2018-01-15','2018-01-01')
(2,'0002','Покупка','Прямые затраты',3,4,'0003','Приобретение ТМЦ','Сторонние компании',5,6,'2018-01-12','2018-01-01')
(3,'0003','Приобретение ТМЦ','Сторонние компании',5,6,'0004','Договор №1','ООО РОГА И КОПЫТА',7,8,'2018-01-10','2018-01-01')
(4,'0003','Приобретение ТМЦ','Сторонние компании',5,6,'0004','Договор №2','ЗАО ШИНПРОМТОРГ',7,8,'2018-01-05','2018-01-01')

UIDSCHET_DTANALYT_DT#1#ANALYT_DT#2#TYPE_ANALYT_DT#1#TYPE_ANALYT_DT#2#SCHET_KTANALYT_KT#1#ANALYT_KT#2#TYPE_ANALYT_KT#1#TYPE_ANALYT_KT#2#DATE_PROVMNTH_PROV
10001ШиныКамаз120002ПокупкаПрямые затраты342018-01-152018-01-01
20002ПокупкаПрямые затраты340003Приобретение ТМЦСторонние компании562018-01-122018-01-01
30003Приобретение ТМЦСторонние компании560004Договор №1ООО РОГА И КОПЫТА782018-01-102018-01-01
40003Приобретение ТМЦСторонние компании560004Договор №2ЗАО ШИНПРОМТОРГ782018-01-052018-01-01



Идея состоит в том, чтобы определить от какого контрагента поступили шины, а именно получить таблицу вида:
UID КЛЮЧЕВОЙ ЗАПИСИ UID ЗАПИСИ-ИСТОЧНИКА
13
14

Явно просматривается иерархия.

Я знаю как решить данную задачу, используя процедурный подход, но производительность при нем крайне неудовлетворительна - 1кк записей обрабатываются за ~4 часа.
Мне кажется, что рекурсивное CTE поможет, но не могу сообразить как построить запрос.

Требования к данным при поиске:
  • Дебет каждой последующей записи в иерархии(потомок) должен равняться кредиту предыдущей записи(родитель);
  • Дата проведения(DATE_PROV) потомка должна быть в интервале [MNTH_PROV ;DATE_PROV ] родителя;
  • Запись считается интересующей, если для проводки существует аналитика с типом 8(Организация).

    Сейчас поиск данных производится при помощи рекурсивной процедуры на стороне клиента, но это жутко медленно.

    SQL SERVER 2008R2.
    Прошу помочь с данным вопросом. Заранее благодарю.
  • 21 май 18, 14:44    [21426030]     Ответить | Цитировать Сообщить модератору
     Re: Обработка древовидных структур  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7780
    Oomel,
    А почему Вы не хотите нормализовать данные? Зачем в одну кучу свалили?
    21 май 18, 14:51    [21426079]     Ответить | Цитировать Сообщить модератору
     Re: Обработка древовидных структур  [new]
    Oomel
    Member

    Откуда:
    Сообщений: 23
    Владислав Колосов, система была разработана до меня, ведение учета было настроено без оглядки на здравый смысл. Изменить методику учета никто не хочет. Переписать БД возможности нет из-за огромного количества зависимостей.
    21 май 18, 14:53    [21426092]     Ответить | Цитировать Сообщить модератору
     Re: Обработка древовидных структур  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7780
    Oomel,

    Если глубина вложений постоянна - постройте два объединения таблицы самой к себе по указанным Вами условиям. Рекурсия хорошо работает только для одного корневого элемента.
    21 май 18, 15:40    [21426359]     Ответить | Цитировать Сообщить модератору
     Re: Обработка древовидных структур  [new]
    Oomel
    Member

    Откуда:
    Сообщений: 23
    Владислав Колосов
    Oomel,

    Если глубина вложений постоянна - постройте два объединения таблицы самой к себе по указанным Вами условиям. Рекурсия хорошо работает только для одного корневого элемента.


    Глубина неизвестна.
    21 май 18, 16:05    [21426448]     Ответить | Цитировать Сообщить модератору
     Re: Обработка древовидных структур  [new]
    dies irae
    Member

    Откуда:
    Сообщений: 78
    Oomel,

    ;WITH t AS (
    	SELECT UID AS target_id
    		,*
    		, 1 AS depth
    	FROM #BUHPROV 
    	WHERE [TYPE_ANALYT_DT#1#] = 1
    	UNION ALL
    	SELECT child.target_id
    		, parent.*
    		,child.depth + 1 
    	FROM #BUHPROV AS parent
    		JOIN t AS child 
    			ON  child.SCHET_KT = parent.SCHET_DT
    			AND child.ANALYT_KT#1# = parent.ANALYT_DT#1#
    			AND child.TYPE_ANALYT_KT#1# = parent.TYPE_ANALYT_DT#1#
    			AND child.ANALYT_KT#2# = parent.ANALYT_DT#2#
    			AND child.TYPE_ANALYT_KT#2# = parent.TYPE_ANALYT_DT#2#
    			AND parent.DATE_PROV BETWEEN child.MNTH_PROV AND child.DATE_PROV
    	WHERE 8 NOT IN (child.TYPE_ANALYT_KT#1#, child.TYPE_ANALYT_KT#2#)
    	)
    SELECT TOP(1) WITH TIES
    	target_id, UID AS source_id
    FROM t
    ORDER BY RANK() OVER (PARTITION BY t.target_id ORDER BY t.depth DESC)
    
    21 май 18, 20:25    [21427077]     Ответить | Цитировать Сообщить модератору
     Re: Обработка древовидных структур  [new]
    Oomel
    Member

    Откуда:
    Сообщений: 23
    dies irae,

    Огромное спасибо! Отлично работает!
    22 май 18, 10:19    [21427932]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить