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

昨天有人让我帮忙写个算移动加权平均的SQL语句，我想了半天终于写出来正确的了。现在发出来供大家参考、讨论。

 

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等时间标志性字段来进行排序。

---

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