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

Откуда:
Сообщений: 102
День добрый, коллеги.

Есть следующая задачка:

Имеется таблица с набором полей:
Id - уникальный айдишник
Date - дата документа (день)
Amount - количество

Количество бывает положительным и отрицательным, приход/уход.

Необходимо связать попарно ближайшие по датам расходы и приходы с противоположными числами. При совпадении дня берется любая пара.

Естественно, хочется это сделать несколькими DML-операторами, без курсоров.

Я добавил столбец PairId, айдишник пары и написал вот такую штуку:

update td set
	PairId =
	(
		select top 1
			Id
		from
			Docs d2
		where
			d2.Amount = d1.Amount * -1	and
			d2.Date >= d1.Date		and
			-- условие, чтобы пропускать Id, которые уже были включены в пару какой-либо записи
			not exists
			(
				select *
				from Docs
				where PairId = d2.Id
			)
		order by Date
	)
	, PairId = @pid
from Docs d1

Логично, что условие на проверку уже вхождения в пару никогда не срабатывает, т.к. update не видит изменений, сделанных собой же.

Вот теперь думаю, похоже, что этот вариант вообще нежизнеспособен, а нового пока что в голову не идет.

М.б. у кого-то будут идеи?

Спасибо.
9 сен 09, 11:20    [7635848]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Riven
Необходимо связать попарно ближайшие по датам расходы и приходы с противоположными числами.
При совпадении дня берется любая пара.
Приведите, пожалуйста, пример тестовых данных и желаемого результата

Пример желательно с показом различных ситуаций (совпадение дня и т.д., Вам виднее)

Также желательно пример оформить в виде скрипта создания временной таблицы
со скриптом вставки в нее тестовых данных
9 сен 09, 11:26    [7635885]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Riven
Member

Откуда:
Сообщений: 102
Создание таблицы с данными:

set nocount on

if object_id('tempdb..#tmp') is not null
	drop table #tmp

create table #tmp
(
	Id			int identity(1, 1) primary key clustered
	, [Date]	datetime
	, Amount	int
	, PairId	int
)

insert into #tmp ([Date], Amount) values ('20080622', -11)
insert into #tmp ([Date], Amount) values ('20080704', -1)
insert into #tmp ([Date], Amount) values ('20080708', -1)
insert into #tmp ([Date], Amount) values ('20080708', 1)
insert into #tmp ([Date], Amount) values ('20080709', -1)
insert into #tmp ([Date], Amount) values ('20080710', 1)
insert into #tmp ([Date], Amount) values ('20080710', -1)
insert into #tmp ([Date], Amount) values ('20080712', 1)
insert into #tmp ([Date], Amount) values ('20080714', 1)
insert into #tmp ([Date], Amount) values ('20080716', 1)
insert into #tmp ([Date], Amount) values ('20080716', -1)
insert into #tmp ([Date], Amount) values ('20080717', -1)
insert into #tmp ([Date], Amount) values ('20080720', -1)
insert into #tmp ([Date], Amount) values ('20080725', -1)
insert into #tmp ([Date], Amount) values ('20080725', -1)
insert into #tmp ([Date], Amount) values ('20080727', -1)
insert into #tmp ([Date], Amount) values ('20080729', 1)
insert into #tmp ([Date], Amount) values ('20080730', 1)
insert into #tmp ([Date], Amount) values ('20080730', 1)
insert into #tmp ([Date], Amount) values ('20080730', 1)
insert into #tmp ([Date], Amount) values ('20080730', 1)
insert into #tmp ([Date], Amount) values ('20080805', -1)
insert into #tmp ([Date], Amount) values ('20080805', 1)
insert into #tmp ([Date], Amount) values ('20080810', -1)
insert into #tmp ([Date], Amount) values ('20080818', -1)

Код, который решает задачу, с курсором:

update #tmp set PairId = null

declare @id int, @date datetime, @amount int

declare cc2 cursor for
select Id, [Date], Amount from #tmp order by [Date]

open cc2

fetch next from cc2 into @id, @date, @amount

while @@fetch_status = 0
begin
	if not exists(select * from #tmp where PairId = @id)
	begin
		update #tmp set PairId =
			(
				select top 1
					Id
				from
					#tmp t22 (nolock)
				where
					t22.Amount = @amount * -1	and
					t22.[Date] >= @date		and
					not exists
					(
						select *
						from #tmp where PairId = t22.Id
					)
				order by [Date]
			)
		where Id = @id
	end

	fetch next from cc2 into @id, @date, @amount
end

close cc2
deallocate cc2

Он выдает следующее (связь между парами односторонняя):

Id	Date				Amount		PairId
1	2008-06-22 00:00:00.000		-11		NULL
2	2008-07-04 00:00:00.000		-1		4
3	2008-07-08 00:00:00.000		-1		6
4	2008-07-08 00:00:00.000		1		NULL
5	2008-07-09 00:00:00.000		-1		8
6	2008-07-10 00:00:00.000		1		NULL
7	2008-07-10 00:00:00.000		-1		9
8	2008-07-12 00:00:00.000		1		NULL
9	2008-07-14 00:00:00.000		1		NULL
10	2008-07-16 00:00:00.000		1		11
11	2008-07-16 00:00:00.000		-1		NULL
12	2008-07-17 00:00:00.000		-1		17
13	2008-07-20 00:00:00.000		-1		18
14	2008-07-25 00:00:00.000		-1		19
15	2008-07-25 00:00:00.000		-1		20
16	2008-07-27 00:00:00.000		-1		21
17	2008-07-29 00:00:00.000		1		NULL
18	2008-07-30 00:00:00.000		1		NULL
19	2008-07-30 00:00:00.000		1		NULL
20	2008-07-30 00:00:00.000		1		NULL
21	2008-07-30 00:00:00.000		1		NULL
22	2008-08-05 00:00:00.000		-1		23
23	2008-08-05 00:00:00.000		1		NULL
24	2008-08-10 00:00:00.000		-1		NULL
25	2008-08-18 00:00:00.000		-1		NULL
9 сен 09, 11:51    [7636063]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Riven
Он выдает следующее
Это и есть желаемый результат?
9 сен 09, 11:53    [7636087]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Riven
Member

Откуда:
Сообщений: 102
Да, решение с курсором верное, видны пары.
9 сен 09, 12:00    [7636149]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
PairId - это идентификатор пары записей?
То есть он должен быть одинаковым у двух записей?
Я правильно понял?
9 сен 09, 12:03    [7636169]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Riven
Member

Откуда:
Сообщений: 102
"Я добавил столбец PairId, айдишник пары и написал вот такую штуку:"

Тут ошибся, а возможности редактирования поста не нашел.

PairId - это Id парной записи.

В таблице ожидаемых результатов можно увидеть пары с протиповоложными количествами и ближайшие по дате:
2, 4
3, 6
5, 6
и т.п.

С курсором я сделал связь одностороннюю, к примеру, если уже была пара с Id = 2, PairId = 4, то у записи с Id = 4 в поле PairId ничего не проставляется, хотя можно сделать и двустороннюю.
9 сен 09, 12:10    [7636215]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Riven
Member

Откуда:
Сообщений: 102
Извиняюсь, последняя пара не 5, 6, а 5, 8...
9 сен 09, 12:12    [7636224]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
версию сервера еще укажите пожалуйста
9 сен 09, 12:16    [7636257]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Riven
Member

Откуда:
Сообщений: 102
Microsoft SQL Server 2000 SP4+Q936232
Build 8.00.2249

В ближайшие полгода планируется переезд на десятку. Если есть решение, использующее фичи последних версий сервера, было бы тоже очень полезно.
9 сен 09, 12:33    [7636391]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
На 2000 я, к сожалению, не знаю как это сделать без курсоров и циклов
На 2005/2008 у могу предложить для ознакомления вариант с CTE, который решает Вашу задачу как я ее понял
Скорее всего он будет медленнее чем курсор (проверять, впрочем, предоставляю Вам самому)
;with numbered as (
   select Id, Date, Amount
         ,row_number() over(order by Date, Id) as Rn
     from #tmp
), cte as (
   select t1.Id, t1.Date, t1.Amount, t1.Rn
         ,t2.Id as PairId
         ,isnull('{' + cast(t2.Id as varchar(max)) + '}', '') as ids
     from numbered as t1
    outer apply (select top 1 t2.Id
                   from numbered as t2
                  where t2.Rn > t1.Rn
                    and t2.Amount = - t1.Amount
                  order by t2.Rn) as t2
    where t1.Rn = 1
   UNION ALL
   select t1.Id, t1.Date, t1.Amount, t1.Rn
         ,t3.Id as PairId
         ,c.ids + isnull('{' + cast(t3.Id as varchar(max)) + '}', '') as ids
     from numbered as t1
     join cte as c on c.Rn + 1 = t1.Rn
    outer apply (select t2.Id
                   from (select t2.Id
                               ,row_number() over(order by t2.Rn) as Rt
                           from numbered as t2
                          where t2.Rn > t1.Rn
                            and t2.Amount = - t1.Amount
                            and c.ids not like '%{' + cast(t2.Id as varchar(10)) + '}%') as t2
                  where t2.Rt = 1) as t2
    cross apply (select case when c.ids not like '%{' + cast(t1.Id as varchar(10)) + '}%'
                             then t2.Id
                             else null
                         end as Id) as t3
)
select Id, Date, Amount, PairId from cte
9 сен 09, 13:19    [7636784]     Ответить | Цитировать Сообщить модератору
 Re: Приход/уход товара - алгоритм  [new]
Riven
Member

Откуда:
Сообщений: 102
Спасибо, делает то, что надо, разбираюсь.
10 сен 09, 11:12    [7640712]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить