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

Откуда:
Сообщений: 457
Доброе утро!
Пытаюсь сочинить запрос следующей задачи :
исключить из товарооборота внутренние манипуляции по складу
Имеется
[товар] [от кого] [кому]
1 12 10
1 10 20
1 20 101
2 12 10
2 10 114
3 12 10
3 10 NULL
В отчете должно быть
[товар] [от кого] [кому]
1 12 101
2 12 114
3 12 NULL
т.е. нужно исключить 10 и 20 конрагентов (подразделения склада) оставить только внешних
group by по товару и далее туплю
Заранее спасибо!
21 ноя 13, 07:32    [15165858]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Может быть и так
Имеется
[товар] [от кого] [кому]
1 12 10
1 10 20
1 20 101
1 101 10
1 10 NULL
2 12 10
2 10 114
3 12 10
3 10 NULL
В отчете должно быть
[товар] [от кого] [кому]
1 12 101
1 101 NULL
2 12 114
3 12 NULL
Все телодвижения отсортированы по дате события
21 ноя 13, 07:40    [15165863]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Draginsv
т.е. нужно исключить 10 и 20 конрагентов (подразделения склада) оставить только внешних

WHERE [кому] not in (10,20)

Рекомендации по оформлению сообщений в форуме
21 ноя 13, 07:45    [15165869]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Jaffar
Member

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

declare @__TEMP table(ID int identity(1, 1) primary key, IDProduct int, StoreFrom int, StoreTo int)

insert @__TEMP(IDProduct, StoreFrom, StoreTo)
          select 1, 12, 10
union all select 1, 10, 20
union all select 1, 20, 101
union all select 2, 12, 10
union all select 2, 10, 114
union all select 3, 12, 10
union all select 3, 10, NULL

--select * from @__TEMP

select t.IDProduct, t1.StoreFrom, t2.StoreTo
from (select IDProduct, min(ID) ID_min, max(ID) ID_max from @__TEMP group by IDProduct) t
join @__TEMP t1 on t1.IDProduct = t.IDProduct and t1.ID = t.ID_min
join @__TEMP t2 on t2.IDProduct = t.IDProduct and t2.ID = t.ID_max
where
		t1.StoreFrom not in (10, 20)
or		t1.StoreFrom not in (10, 20)

--если условия в реальности хитрее чем в примере то можни использовать cross apply
select t.IDProduct, t1.StoreFrom, t2.StoreTo
from (select distinct IDProduct from @__TEMP) t
cross apply(select top 1 t1.StoreFrom
			from @__TEMP t1
			where
					t1.IDProduct = t.IDProduct
			order by t1.ID asc) t1
cross apply(select top 1 t2.StoreTo
			from @__TEMP t2
			where
					t2.IDProduct = t.IDProduct
			order by t2.ID desc) t2

where
		t1.StoreFrom not in (10, 20)
or		t1.StoreFrom not in (10, 20)


НО - в реале так делать нельзя.
Только если есть однозначные сведения что именно этот экз. товара двигается по складу.
21 ноя 13, 08:13    [15165914]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Jaffar
Member

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

т.е. по цепочке перемещений для каждого продукта вы должны найти из них начальную и конечную точки,
а потом проверить что хотябы одна из них не является внутренней, так?
21 ноя 13, 08:17    [15165923]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Draginsv
Member

Откуда:
Сообщений: 457
да правильно, но как min и max здесь сработают если есть внутри цепочки внешние контрагенты. min и max берут только крайние точки, а промежуточные игнорируют. вот поэтому я и туплю.
21 ноя 13, 08:41    [15165986]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Draginsv
да правильно, но как min и max здесь сработают если есть внутри цепочки внешние контрагенты. min и max берут только крайние точки, а промежуточные игнорируют. вот поэтому я и туплю.



т.е. насколько под_цепочек распадутся все перемещения 1 продукта заранее не известно, так?
т.е. нужно определиться что является условием начала и конца для под_цепочки.
определитесь с условиями - тогда напишу вам по ним запрос.
а то так данных мало.


без курсора можно функцию табличную сделать которая будет по ID продукта возвращать все его под_цепочки.
а снаружи просто проверяете что in или out - не внутр.

если у вас есть решение с курсором - выложите его, попробуем преобразовать к без_курсорному виду.
21 ноя 13, 09:02    [15166073]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Draginsv
Member

Откуда:
Сообщений: 457
в том то и дело что кол-во под_цепочек не ограничено. а в курсоре ход рассуждений такой
создать курсор по товарам с внутренними контрагентами, цикл по нему, внутри цикла:
создать курсор по конкретному товару с сортировкой по дате, цикл, внутри.
запомнить кому в переменную
переход к следующей записи,
если переменная кому совпадает с полем от кого в записи
если поле кому - внутренний
помечаем на удаление эту запись
иначе
апдейтим запись соотв. переменной кому в поле кому прописываем значение поля кому текущей записи
конец если
конец если
переходим к следующей

примерно так
21 ноя 13, 09:34    [15166214]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Jaffar
Member

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

а sql код есть?
21 ноя 13, 09:41    [15166261]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
aleks2
Guest
Вот, страдалец, твой коллега
https://www.sql.ru/forum/1060429/pomoshh-s-zapros
21 ноя 13, 09:47    [15166294]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Draginsv
Member

Откуда:
Сообщений: 457
кажется можно попробовать так
если рассматривать отдельный товар
во временную таблицу добавить записи где от кого - внешние контрагенты
апдейтить во временной поле кому позапросом - найти первую запись в исходной таблице по данному товару с полем кому - внешние контрагенты с датой отправки кому больше даты в записи во временной таблице для поля от кого.
Но это мне кажется примитив и он может дать сбой при каких нибудь хитростях с внутренними контрагентами.
здесь нет анализа на неразрывность перемещения.
21 ноя 13, 10:06    [15166385]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Jaffar,
все пока в мыслях
21 ноя 13, 10:09    [15166398]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Draginsv,

В Вашей структуре нет точного определения какая операция первая, какая последняя. Т.е. если мы увидим такое:
declare @tbl table
(
	товар int,
	отКого int,
	кому int
)
insert @tbl values
 (1, 12, 10	 )
,(1, 10, 20	 )
,(1, 20, 101 )
,(2, 12, 10	 )
,(2, 10, 114 )
,(3, 12, 10	 )
,(3, 10, NULL)
,(4, 15, 16)
,(4, 16, 15)


И попытаемся определить от кого к кому пришел 4й товар, то можно интерпретировать что от 15->16->15 = 15, 15. А можно и 16-16. Не ясно какая первая какая последняя операция. Нужно вводить поле дата операции или порядковый номер операции.
21 ноя 13, 10:48    [15166653]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Draginsv,

Ну и если ввести дату операции товара, на первый взгляд без оптимизаций как вариант такой запрос:

declare @tbl table
(
	товар int,
	отКого int,
	кому int,
	дата datetime
)
insert @tbl values
 (1, 12, 10		,'2013-01-01')
,(1, 10, 20		,'2013-01-02')
,(1, 20, 101	,'2013-01-03')
,(2, 12, 10		,'2013-01-04')
,(2, 10, 114	,'2013-01-05')
,(3, 12, 10		,'2013-01-06')
,(3, 10, NULL	,'2013-01-07')
,(4, 15, 16		,'2013-01-08')
,(4, 16, 15		,'2013-01-09')
;
with c as
(
select *
	,row_number() over (partition by товар order by дата asc) as номер1
	,row_number() over (partition by товар order by дата desc) as номер2
from @tbl
)
select
	c1.товар,
	c1.отКого,
	c2.кому
from c as c1 join c c2
	on c1.товар = c2.товар
	and c1.номер1=1
	and c2.номер2=1
21 ноя 13, 10:54    [15166687]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Jaffar
Member

Откуда:
Сообщений: 633
Алексей Куренков
Draginsv,

В Вашей структуре нет точного определения какая операция первая, какая последняя. Т.е. если мы увидим такое:
declare @tbl table
(
	товар int,
	отКого int,
	кому int
)
insert @tbl values
 (1, 12, 10	 )
,(1, 10, 20	 )
,(1, 20, 101 )
,(2, 12, 10	 )
,(2, 10, 114 )
,(3, 12, 10	 )
,(3, 10, NULL)
,(4, 15, 16)
,(4, 16, 15)


И попытаемся определить от кого к кому пришел 4й товар, то можно интерпретировать что от 15->16->15 = 15, 15. А можно и 16-16. Не ясно какая первая какая последняя операция. Нужно вводить поле дата операции или порядковый номер операции.



и не только .
ибо с 1 --> 2 может прийти 5 кг муки
а с 2 --> 3 может прийти 2 кг муки или 7 кг муки.
как это понимать
21 ноя 13, 11:27    [15166923]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Draginsv
Member

Откуда:
Сообщений: 457
товар уникальный с серийным номером.
поле дата - по нему сортировка
а как я предложил - оно в подзапросе.
Алексей, буду пытаться понять Вашу мысль.
21 ноя 13, 11:48    [15167112]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Алексей
Для такого набора данных
(1, 12, 10 ,'2013-01-01')
,(1, 10, 20 ,'2013-01-02')
,(1, 20, 101 ,'2013-01-03')
,(1, 101, 10 ,'2013-01-03 1:00')
,(1, 10, NULL ,'2013-01-03 2:00')
,(2, 12, 10 ,'2013-01-04')
,(2, 10, 114 ,'2013-01-05')
,(3, 12, 10 ,'2013-01-06')
,(3, 10, NULL ,'2013-01-07')
,(4, 15, 16 ,'2013-01-08')
,(4, 16, 15 ,'2013-01-09')
не работает
должно получиться
1 12 101
1 101 NULL
2 12 114
3 12 NULL
21 ноя 13, 11:55    [15167152]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Draginsv
Алексей
Для такого набора данных
(1, 12, 10 ,'2013-01-01')
,(1, 10, 20 ,'2013-01-02')
,(1, 20, 101 ,'2013-01-03')
,(1, 101, 10 ,'2013-01-03 1:00')
,(1, 10, NULL ,'2013-01-03 2:00')
,(2, 12, 10 ,'2013-01-04')
,(2, 10, 114 ,'2013-01-05')
,(3, 12, 10 ,'2013-01-06')
,(3, 10, NULL ,'2013-01-07')
,(4, 15, 16 ,'2013-01-08')
,(4, 16, 15 ,'2013-01-09')
не работает
должно получиться
1 12 101
1 101 NULL
2 12 114
3 12 NULL


Немного странно... видимо я не все понял?
задачу я понимаю так, что если рассматривать 1й товар то цепочка идет: 12->10->20->101->10->null
Еще проще без проверки целостности всей цепочки - первый владелец товара и последний. что в запросе и сделано.

по запросу получается 12->null
каким образом у Вас дважды в результате товар с кодом 1 получается - это для меня или загадка или я не понял каким образом должно получиться..

каким образом должно получиться:
1 12 101
1 101 NULL
?
21 ноя 13, 12:05    [15167235]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Алексей Куренков
цепочка идет: 12->10->20->101->10->null

каким образом должно получиться:
1 12 101
1 101 NULL
?

ТС писал, что "нужно исключить 10 и 20 конрагентов". Исключив их из цепочки, получим такое: 12->101->null. Разложив звенья цепочки, получаем пары (12, 101) и (101, Null).
21 ноя 13, 12:09    [15167276]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Jaffar
Member

Откуда:
Сообщений: 633
ну вот как-то наверное так:

declare @__TEMP table(ID int identity(1, 1) primary key, IDProduct int, StoreFrom int, StoreTo int)

insert @__TEMP(IDProduct, StoreFrom, StoreTo)
          select 1,  12, 10
union all select 1,  10, 20
union all select 1,  20, 101
union all select 1, 101, 102
union all select 1, 102, 103
union all select 1, 103, 101
union all select 1, 101,  10
union all select 1,  10, NULL

union all select 2, 12, 10
union all select 2, 10, 114

union all select 3, 12, 10
union all select 3, 10, NULL

select
t3.IDProduct, 
row_number() over(partition by t3.IDProduct order by t3.NNX asc) NNX_2, 
NULLIF(t3.Stores, -1) StoresFrom,  
NULLIF(t3.StoresNext, -1)StoresTo
from   (select
	   t.IDProduct, 
	   t2.Stores,
	   row_number() over(partition by t.IDProduct order by t.ID asc, t2.NN asc) NNX,
	   LEAD(t2.Stores, 1) over(partition by t.IDProduct order by t.ID asc, t2.NN asc) StoresNext
	   from @__TEMP t
	   --- выстраиваем их в цепочку
	   cross apply(select 1 NN, IsNULL(t.StoreFrom, -1) Stores where IsNULL(t.StoreFrom, 0) not in (10, 20)
	   		    union all
			    select 2 NN, ISNULL(t.StoreTo,   -1)        where IsNULL(t.StoreTo,   0) not in (10, 20) /**/ ) t2 /**/ ) t3
where
		t3.Stores != t3.StoresNext -- убираем дубли
order by  t3.IDProduct, NNX_2
21 ноя 13, 14:40    [15168514]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Спасибо!
как сложно.
будем осмысливать.
21 ноя 13, 14:45    [15168562]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Zandr
Member

Откуда: Москва
Сообщений: 576
Можно как-то так попробовать:
WITH
Res (IDProduct, StoreFrom, StoreTo, _Level)
AS (SELECT t.IDProduct, t.StoreFrom, t.StoreTo, 1 _Level FROM TestTable t
	WHERE NOT EXISTS (SELECT 1 FROM TestTable WHERE IDProduct = t.IDProduct AND StoreTo = t.StoreFrom)
	UNION ALL
	SELECT t.IDProduct, t.StoreFrom, t.StoreTo, r._Level + 1 FROM Res r 
	JOIN TestTable t ON t.IDProduct = r.IDProduct AND t.StoreFrom = r.StoreTo
   )
SELECT r.IDProduct, r.StoreFrom, r1.StoreTo FROM Res r
JOIN Res r1 ON r1.IDProduct = r.IDProduct AND r1._Level = (SELECT MAX(_Level) FROM Res WHERE IDProduct = r.IDProduct)
WHERE r._Level = 1
ORDER BY r.IDProduct;

(Для второго случая, где был возврат к тому же айдишнику надо будет доделать, ориентируясь на дату или какие-то еще поля)
21 ноя 13, 18:29    [15170282]     Ответить | Цитировать Сообщить модератору
 Re: Алгоритм без курсора  [new]
Zandr
Member

Откуда: Москва
Сообщений: 576
Zandr,
А блин... про 10 и 20, как разделители, тоже не понял сразу...
21 ноя 13, 19:32    [15170499]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить