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

Откуда:
Сообщений: 23
Господа, помогите найти адекватное по времени решение задачи перераспределения товаров между складами.
Должно работать «Многие-ко-многим» (т.е. "Количество" может делиться).
Есть 2 таблицы складов отправителей и получателей с количествами избытка и недостатка соответственно. На выходе нужно получить Таблицу Отправитель, Получатель, Кол-во.

Например, Исходные Данные для одного товара:
USE tempdb

-- отправители с рангами отправки (в первую очередь с минимальным рангом). num может делиться разным получателям!
CREATE TABLE #source(id_src int, num FLOAT, rang int)
INSERT into #source 
VALUES 
(140,400,1),
(77 ,150,2),
(110,160,3),
(120,300,4),
(115,250,5),
(148,200,6),
(76 ,210,7),
(136,100,8),
(52 ,150,9),
(7  ,110,10)
		
-- получатели отсортированы по num. num может приходить от разных отправителей
CREATE TABLE #destination(id_dest INT,	num	FLOAT)
INSERT into #destination
VALUES 
(22	,800),
(40	,150),	
(44	,100),	
(114 ,90),
(26	,80),
(112 ,50),
(59	,30),
(109 ,20),
(92	,10)
		

-- итог
CREATE TABLE #result (id_src int, id_dest int, num FLOAT)
INSERT into #result
VALUES 
(140,	22     ,400),
(77,          22	,150 ),
(110,	22	,160 ),
(120,	22	,90   ),
(120,	40	,150 ),
(120,	44	,60   ),
(115,	44	,40	),
(115,	114   ,90	),
(115,	26	,80	),
(115,	112	,40   ),
(148,	112	,10	),
(148,	59	,30	),
(148,	109	,20	),
(148,	92	,10	)
				
--DROP TABLE #source
--DROP TABLE #destination
--DROP TABLE #result
24 июл 17, 19:38    [20671059]     Ответить | Цитировать Сообщить модератору
 Re: Перераспределение товаров между складами  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 641
MS_t_SQL,
Т.к. Вы не озвучили версию сервера, то вот вариант для 2008 и выше
Да, да, знаю, курсоры - зло. Но мне кажется, что для данной задачи вполне себе адекватный инструмент, (кроме того, другого внятного решения придумать не удалось). На тысячах записей работает вполне сносно, а больше мне и не надо.
+
USE tempdb
GO

IF OBJECT_ID(N'tempdb..#source', N'U') IS NOT NULL DROP TABLE #source;
IF OBJECT_ID(N'tempdb..#destination', N'U') IS NOT NULL DROP TABLE #destination;
IF OBJECT_ID(N'tempdb..#result', N'U') IS NOT NULL DROP TABLE #result;
GO

-- отправители с рангами отправки (в первую очередь с минимальным рангом). num может делиться разным получателям!
CREATE TABLE #source(id_src int, num FLOAT, rang int, run_total FLOAT NULL)
INSERT into #source (id_src, num, rang)
VALUES 
(140,400,1),
(77 ,150,2),
(110,160,3),
(120,300,4),
(115,250,5),
(148,200,6),
(76 ,210,7),
(136,100,8),
(52 ,150,9),
(7  ,110,10)

-- получатели отсортированы по num. num может приходить от разных отправителей
CREATE TABLE #destination(id_dest INT,	num	FLOAT)
INSERT into #destination
VALUES 
(22	,800),
(40	,150),	
(44	,100),	
(114 ,90),
(26	,80),
(112 ,50),
(59	,30),
(109 ,20),
(92	,10)
		

-- итог
CREATE TABLE #result (id_src int, id_dest int, num FLOAT)

UPDATE [s]
    SET [run_total] = [run].[total]
    FROM #source [s]
         CROSS APPLY (SELECT SUM([ss].[num]) FROM #source [ss] WHERE [ss].[rang] <= [s].[rang]) [run]([total])
;

DECLARE  @id_dest   INT
       , @num       FLOAT
;
DECLARE cur CURSOR STATIC FOR
SELECT  [d].[id_dest]
      , [d].[num]
    FROM #destination [d]
    ORDER BY  [d].[num] DESC
;
OPEN cur;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cur INTO @id_dest, @num;
    IF @@FETCH_STATUS <> 0
        BREAK;
    
    UPDATE [s]
        SET  [num]  =(CASE WHEN [s].[run_total] - @num > 0 THEN [s].[run_total] - @num ELSE 0 END)
        OUTPUT       [inserted].[id_src], @id_dest , [deleted].[num] - [inserted].[num]
        INTO #result([id_src]           , [id_dest], [num])
        FROM #source [s]
        WHERE [s].[run_total] > 0
          AND @num - [s].[run_total] + [s].[num] > 0
    ;
    UPDATE [s]
        SET  [run_total]    = [s].[run_total] - @num
        FROM #source [s]
    ;
END;
CLOSE cur;
DEALLOCATE cur;

SELECT * FROM #result
25 июл 17, 06:39    [20671659]     Ответить | Цитировать Сообщить модератору
 Re: Перераспределение товаров между складами  [new]
MS_t_SQL
Member

Откуда:
Сообщений: 23
Guf, да спасибо, хотелось бы без курсоров, если такое вообще возможно...
версия 2012.
чтобы получить только исходные данные для этой задачи уходит много времени.
прогоню на реальных данных отпишусь о результатах.
25 июл 17, 08:39    [20671781]     Ответить | Цитировать Сообщить модератору
 Re: Перераспределение товаров между складами  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
MS_t_SQL, 2012 - это очень хорошо.

Вот мой вариант, за 100%-ю валидность не отвечаю, слегка с подбором игрался, так что все варианты мог не учесть - желательно еще поразбираться с крайними вариантами - возможно, я где-то косякнул.

select	s.id_src, d.id_dest
	,	case when s.sum_num <= d.sum_num and s.lag_sum >= d.lag_sum then s.num
			 when s.sum_num <= d.sum_num and s.lag_sum < d.lag_sum then s.sum_num - d.lag_sum
			 when s.sum_num > d.sum_num and s.lag_sum >= d.lag_sum then d.sum_num - s.lag_sum
			 when s.sum_num > d.sum_num and s.lag_sum < d.lag_sum then d.num
			else null end as num 
from	(
			select s.id_src, s.num, s.sum_num, isnull(lag(s.sum_num, 1) over (order by s.rang), 0) as lag_sum
			from (	select s.id_src, num, s.rang, sum(num) over (order by s.rang) as sum_num
					from	#source as s
				 ) as s
		) as s
		inner join (
						select d.id_dest, d.num, d.sum_num, isnull(lag(d.sum_num, 1) over (order by d.num desc), 0) as lag_sum
						from (	select d.id_dest, d.num, sum(d.num) over (order by d.num desc) as sum_num
								from #destination as d
							 ) as d
					) as d
				on d.sum_num >= s.lag_sum
				and d.lag_sum <= s.sum_num
25 июл 17, 10:44    [20672183]     Ответить | Цитировать Сообщить модератору
 Re: Перераспределение товаров между складами  [new]
MS_t_SQL
Member

Откуда:
Сообщений: 23
Minamoto,
спасибо, красиво и прозрачно, отпишусь по результатам тестов!
25 июл 17, 12:52    [20672759]     Ответить | Цитировать Сообщить модератору
 Re: Перераспределение товаров между складами  [new]
MS_t_SQL
Member

Откуда:
Сообщений: 23
Minamoto,
выражаю искреннюю благодарность!
алгоритм работает отлично на 100 тысячных данных, единственное в where нужно убрать =, чтобы избавиться от пустых строк.
но выяснились детали, что Избытки Отправителей нельзя дробить, т.е. набираем из Отправителей по приоритету пока не превышает недостаток, а затем "добиваем" недостаток, тем что первое влезет в остатки, плюя на ранг!

есть идеи модифицировать без курсоров?
USE tempdb

-- отправители с рангами отправки (в первую очередь с минимальным рангом). 
--num НЕ ДОЛЖЕН делиться разным получателями!
CREATE TABLE #source(id_src int, num FLOAT, rang int)
INSERT into #source 
VALUES 
(140,400,1),
(77 ,150,2),
(110,160,3),
(120,300,4),
(115,250,5),
(148,200,6),
(76 ,210,7),
(136,100,8),
(52 ,150,9),
(7  ,110,10),
(8  ,80, 11),
(9  ,50, 11)		

-- получатели отсортированы по num. num может приходить от разных отправителей
CREATE TABLE #destination(id_dest INT,	num	FLOAT)
INSERT into #destination
VALUES 
(22	,800),
(40	,150),	
(44	,100),	
(114    ,90),
(26	,80),
(112    ,50),
(59	,30),
(109    ,20),
(92	,10)

-- итог
CREATE TABLE #result (id_src int, id_dest int, num FLOAT)
INSERT into #result
VALUES 
(140,  22,400 ),
(77,   22,150 ),
(110,  22,160 ),
(8,    22,80), -- добрали ниже по рейтингу
-- нужно ещё <=10, но нет (для 22)
(136,  40,100 ),
(9,    40,50 ) -- добрали ниже
-- <=100 для 44 нет
-- далее для 26, 112, 59, 109, 92 тоже ничего нет в #source
2 авг 17, 22:31    [20696392]     Ответить | Цитировать Сообщить модератору
 Re: Перераспределение товаров между складами  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
MS_t_SQL
есть идеи модифицировать без курсоров?

Теоретически, наверное, можно, но будет очень сложно и, скорее всего, даже дольше чем курсоры.

Кстати, при таком ограничении логичнее другую постановку сделать - набрать таких отправителей (без учета приоритета), чтобы у получателей набрать максимально близкое к необходимому значение.

Примерно так:

-- итог
CREATE TABLE #result (id_src int, id_dest int, num FLOAT)
INSERT into #result
VALUES 
(140,  22,400 ),
(120,  22,300 ),
(136,  22,100 ),
--ну и так далее

Ну это, конечно, от задач бизнеса зависит.
3 авг 17, 10:00    [20697009]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить