ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Обработка древовидных структур
7 сообщений из 7, страница 1 из 1
Обработка древовидных структур
    #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:44
| Ответить | Цитировать | Написать  
Обработка древовидных структур
    #39647604
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Oomel,
А почему Вы не хотите нормализовать данные? Зачем в одну кучу свалили?
...
Рейтинг: 0 / 0
21.05.2018, 14:51
| Ответить | Цитировать | Написать  
Обработка древовидных структур
    #39647606
Oomel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Oomel
Гость
Владислав Колосов, система была разработана до меня, ведение учета было настроено без оглядки на здравый смысл. Изменить методику учета никто не хочет. Переписать БД возможности нет из-за огромного количества зависимостей.
...
Рейтинг: 0 / 0
21.05.2018, 14:53
| Ответить | Цитировать | Написать  
Обработка древовидных структур
    #39647630
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Oomel,

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

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

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

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


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