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

Откуда:
Сообщений: 461
на входе два набора разных данных с одинаковой структурой:
+ старые планы производства

Id Amount
15
210
315
sum(Amount) = 30

+ новые планы производства

Id Amount
108
204
3010
406
502
sum(Amount) = 30


+ На выходе надо получить распределение старых планов производства на новые нарастающими итогами:
SourceId TargetId Amount
1105
2103
2204
2303
3307
3406
3502
sum(Amount) = 30


+ скрипт, чтобы быстро начать думать

declare @Source table
  ( Id int not null
  , Amount int not null
  )
;
insert into @Source ( Id, Amount )
select 1, 5 union all
select 2, 10 union all
select 3, 15
;
declare @Target table
  ( Id int not null
  , Amount int not null
  )
;
insert into @Target ( Id, Amount )
select 10, 8 union all
select 20, 4 union all
select 30, 10 union all
select 40, 6 union all
select 50, 2
;


Надо быстро придумать код, который выдаст требуемый результат. Наверняка такие задачки уже не раз решались в реальных проектах.. может поделитесь готовым решением?
25 июн 12, 16:55    [12772065]     Ответить | Цитировать Сообщить модератору
 Re: распределение нарастающими итогами  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
with d as
(
 select d from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) t(d)
),
numbers as
(
 select
  row_number() over (order by (select 1)) as n
 from
  d d0 cross join
  d d1 cross join
  d d2
),
src as
(
 select
  s.*, row_number() over (order by s.id, n) as n
 from
  @Source s join
  numbers n on n.n <= s.Amount
),
tgt as
(
 select
  t.*, row_number() over (order by t.id, n) as n
 from
  @Target t join
  numbers n on n.n <= t.Amount
)
select
 s.id, t.id, count(*)
from
 src s join
 tgt t on t.n = s.n
group by
 s.id, t.id
order by
 s.id, t.id;
25 июн 12, 17:14    [12772192]     Ответить | Цитировать Сообщить модератору
 Re: распределение нарастающими итогами  [new]
RubinDm
Member

Откуда:
Сообщений: 461
invm, спасибо, сгодится.
дело закрыто.
25 июн 12, 17:41    [12772419]     Ответить | Цитировать Сообщить модератору
 Re: распределение нарастающими итогами  [new]
hallabud
Member

Откуда: Киев
Сообщений: 245
А мне кажется, что в цикле побыстрее будет судя по предварительному плану запроса и понятней. или я не прав?
+
DECLARE @Result TABLE (SourceId INT, TargetId INT, Amount INT); -- таблица для результата
DECLARE @curSourceId INT; -- текущий сорс_айди, который распределяем
DECLARE @curTargetId INT; -- текущий таргет_айди
DECLARE @curSourceAmount INT; -- кол-во из сорс, которое распределяем
DECLARE @curTargetAmount INT; -- кол-во из таргет, которое используется для распределения текущего сорса
DECLARE @AmountToDistribute INT; -- общий остаток кол-ва для распределения

SELECT @AmountToDistribute = SUM(Amount) FROM @Source;
WHILE @AmountToDistribute > 0
	BEGIN
		SELECT @curSourceId = MIN(Id) FROM @Source WHERE Amount > 0;
		SELECT @curTargetId = MIN(Id) FROM @Target WHERE Amount > 0;
		SELECT @curSourceAmount = Amount FROM @Source WHERE Id = @curSourceId;
		SELECT @curTargetAmount = Amount FROM @Target WHERE Id = @curTargetId;
	
		IF @curSourceAmount <= @curTargetAmount
		BEGIN
			INSERT INTO @Result SELECT @curSourceId, @curTargetId, @curSourceAmount;
			UPDATE @Source SET Amount = 0  WHERE Id = @curSourceId;
			UPDATE @Target SET Amount = @curTargetAmount - @curSourceAmount WHERE Id = @curTargetId;
		END
		ELSE
		BEGIN	
			INSERT INTO @Result SELECT @curSourceId, @curTargetId, @curTargetAmount;
			UPDATE @Source SET Amount = @curSourceAmount - @curTargetAmount WHERE Id = @curSourceId;
			UPDATE @Target SET Amount = 0 WHERE Id = @curTargetId;
		END;
	SELECT @AmountToDistribute = SUM(Amount) FROM @Source;
	END;

SELECT * FROM @Result;
25 июн 12, 23:41    [12773701]     Ответить | Цитировать Сообщить модератору
 Re: распределение нарастающими итогами  [new]
invm
Member

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

1. В реальной системе CTE для получения ряда натуральных чисел заменяется на постоянную таблицу.
2. Планы надо анализировать реальные, а не предварительные.
3. Посчитайте число просмотров и чтений вашего варианта, я уже молчу о количестве модификаций исходной таблицы. Кстати, кто разрешал ее модифицировать?
4. Если вам понятнее вариант с циклом, это лишь говорит о том, что вы скорее императивщик, чем декларативщик.
26 июн 12, 00:30    [12773838]     Ответить | Цитировать Сообщить модератору
 Re: распределение нарастающими итогами  [new]
hallabud
Member

Откуда: Киев
Сообщений: 245
invm,

так я как раз и спросил, чтобы знать как в реальной жизни
26 июн 12, 01:10    [12773937]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить