我正在处理一个数据仓库项目,客户提供每日销售数据。大多数行中都提供了现有数量,但经常会遗漏。我需要帮助如何根据之前的OH和销售信息来填补这些缺失的值
这是一个样本数据:
Line# Store Item OnHand SalesUnits DateKey
-----------------------------------------------
1 001 A 100 20 1
2 001 A 80 10 2
3 001 A null 30 3 --[OH updated with 70 (80-10)]
4 001 A null 5 4 --[OH updated with 40 (70-30)]
5 001 A 150 10 5 --[OH untouched]
6 001 B null 4 1 --[OH untouched - new item]
7 001 B 80 12 2
8 001 B null 10 3 --[OH updated with 68 (80-12]
第1行和第2行不更新,因为存在现有数量
第3行和第4行将根据它们前面的行进行更新
由于提供了OnHand,第5行将保持原样
第6行将保持不变,因为它是项目B的第一行
有没有一种方法可以在集合运算中做到这一点?我知道使用fast_forward光标可以很容易地完成,但这需要很长时间(15M+行)。
谢谢你的帮助!
测试数据:
declare @t table(
Line# int, Store char(3), Item char, OnHand int, SalesUnits int, DateKey int
)
insert @t values
(1, '001', 'A', 100, 20, 1),
(2, '001', 'A', 80 , 10, 2),
(3, '001', 'A', null, 30, 3),
(4, '001', 'A', null, 5, 4),
(5, '001', 'A', 150, 10, 5),
(6, '001', 'B', null, 4, 1),
(7, '001', 'B', null, 4, 2),
(8, '001', 'B', 80, 12, 3),
(9, '001', 'B', null, 10, 4)
不使用光标填充的脚本:
;with a as
(
select Line#, Store, Item, OnHand, SalesUnits, DateKey, 1 correctdata from @t where DateKey = 1
union all
select t.Line#, t.Store, t.Item, coalesce(t.OnHand, a.onhand - a.salesunits), t.SalesUnits, t.DateKey, t.OnHand from @t t
join a on a.DateKey = t.datekey - 1 and a.item = t.item and a.store = t.store
)
update t
set OnHand = a.onhand
from @t t join a on a.line# = t.line#
where a.correctdata is null
使用光标填充的脚本:
declare @datekey int, @store int, @item char, @Onhand int,
@calculatedonhand int, @salesunits int, @laststore int, @lastitem char
DECLARE sales_cursor
CURSOR FOR
SELECT datekey+1, store, item, OnHand -SalesUnits, salesunits
FROM @t sales
order by store, item, datekey
OPEN sales_cursor;
FETCH NEXT FROM sales_cursor
INTO @datekey, @store, @item, @Onhand, @salesunits
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @calculatedonhand = case when @laststore = @store and @lastitem = @item
then coalesce(@onhand, @calculatedonhand - @salesunits) else null end
,@laststore = @store, @lastitem = @item
UPDATE s
SET onhand=@calculatedonhand
FROM @t s
WHERE datekey = @datekey and @store = store and @item = item
and onhand is null and @calculatedonhand is not null
FETCH NEXT FROM sales_cursor
INTO @datekey, @store, @item, @Onhand, @salesunits
END
CLOSE sales_cursor;
DEALLOCATE sales_cursor;
我建议您使用游标版本,我怀疑您是否可以使用递归查询获得良好的性能。我知道这里的人讨厌光标,但当你的桌子有那么大的时候,它可能是唯一的解决方案。