Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Обработка древовидных структур / 7 сообщений из 7, страница 1 из 1
21.05.2018, 14:44
    #39647599
Oomel
Oomel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка древовидных структур
Товарищи здравствуйте!

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
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_PROV10001ШиныКамаз120002ПокупкаПрямые затраты342018-01-152018-01-0120002ПокупкаПрямые затраты340003Приобретение ТМЦСторонние компании562018-01-122018-01-0130003Приобретение ТМЦСторонние компании560004Договор №1ООО РОГА И КОПЫТА782018-01-102018-01-0140003Приобретение ТМЦСторонние компании560004Договор №2ЗАО ШИНПРОМТОРГ782018-01-052018-01-01



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

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

Требования к данным при поиске:
Дебет каждой последующей записи в иерархии(потомок) должен равняться кредиту предыдущей записи(родитель);

Дата проведения(DATE_PROV) потомка должна быть в интервале [MNTH_PROV ;DATE_PROV ] родителя;

Запись считается интересующей, если для проводки существует аналитика с типом 8(Организация).

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

SQL SERVER 2008R2.
Прошу помочь с данным вопросом. Заранее благодарю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2018, 14:51
    #39647604
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка древовидных структур
Oomel,
А почему Вы не хотите нормализовать данные? Зачем в одну кучу свалили?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2018, 14:53
    #39647606
Oomel
Oomel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка древовидных структур
Владислав Колосов, система была разработана до меня, ведение учета было настроено без оглядки на здравый смысл. Изменить методику учета никто не хочет. Переписать БД возможности нет из-за огромного количества зависимостей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2018, 15:40
    #39647630
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка древовидных структур
Oomel,

Если глубина вложений постоянна - постройте два объединения таблицы самой к себе по указанным Вами условиям. Рекурсия хорошо работает только для одного корневого элемента.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2018, 16:05
    #39647641
Oomel
Oomel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка древовидных структур
Владислав КолосовOomel,

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

Глубина неизвестна.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.05.2018, 20:25
    #39647754
dies irae
dies irae
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка древовидных структур
Oomel,

Код: 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.
;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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.05.2018, 10:19
    #39647920
Oomel
Oomel
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Обработка древовидных структур
dies irae,

Огромное спасибо! Отлично работает!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Обработка древовидных структур / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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