欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

用计算列实现移动加权平均算法

程序员文章站 2022-09-04 08:32:13
复制代码 代码如下: if object_id('tb') is not null drop table tb if object_id('temp') is not nu...
复制代码 代码如下:

if object_id('tb') is not null drop table tb
if object_id('temp') is not null drop table temp
if object_id('fun_nowprice') is not null drop function fun_nowprice
if object_id('fun_nowqty') is not null drop function fun_nowqty
go

create table tb(
id int
,date1 datetime
,ctype varchar(10)
,qnt float
,pri float
)

--qnt 数量
--pri 单价
insert tb
select 0,'2009-1-1', '进货', 10, 100 union all
select 1,'2009-1-1', '进货', 50, 120 union all
select 2,'2009-1-2', '出货', 30, 150 union all
select 3,'2009-1-3', '进货', 40, 130 union all
select 4,'2009-1-3', '出货', 25, 160
go
-- 我要算成本价,按移动加权平均

/*
1进货以后的成本价c1=(10*100+50*120)/(10+50)
2出货以后的成本价c2=((10+50)*c1-30*c1)/((10+50)-30)=c2
--也就是说出货的时候价格不变
3进货以后的成本价c3=(((10+50)-30)*c2+40*130)/((10+50)-30+40)
--也就是说进货的时候单价更新为(当前库存的总价值+库总价值)/入库后总数量


以此类推...
*/

--想了半天,觉得只能用循环、递归、游标实现,因为出库时的价格是根据之前的记录算出来的。
--也许有经典的算法,谁知道的麻烦教教我或者发个链接。

--这个function就是变相实现递归的
create function fun_nowprice(@id int)
returns numeric(19,6)
as
begin
return (select isnull(nowprice,0) from
(select max(nowprice) 'nowprice' from temp t1 where id<@id and
not exists(select 1 from temp where id>t1.id and id<@id))
t)
end
go
--这个function是为了计算方便
create function fun_nowqty(@id int)
returns numeric(19,6)
as
begin
return (select isnull(sum(case ctype when '进货' then qnt else 0-qnt end),0) from temp where id<@id)
end
go


--建一个临时表,包含原表参与运算的全部字段
create table temp(
id int
,date1 datetime
,ctype varchar(10)
,qnt float
,pri float
,nowprice as
case ctype
when '出货' then dbo.fun_nowprice(id)
else (dbo.fun_nowprice(id)*dbo.fun_nowqty(id)+qnt*pri)/(dbo.fun_nowqty(id)+qnt)
end)


insert into temp
select * from tb
order by date1 asc,id asc

select * from temp

/*
0 2009-01-01 00:00:00.000 进货 10 100 100
1 2009-01-01 00:00:00.000 进货 50 120 116.666666666667
2 2009-01-02 00:00:00.000 出货 30 150 116.666667
3 2009-01-03 00:00:00.000 进货 40 130 124.285714428571
4 2009-01-03 00:00:00.000 出货 25 160 124.285714
*/

这个写法的不完善处在于它是根据id和日期对记录进行排序的,对于同一天的出入库情况没有处理。实际运用中可以根据createdate等时间标志性字段来进行排序。
--------------------------------------------------------------------------------

第一次写技术性博客,希望这是一个好的开始,欢迎大家对我的算法进行指正^_^