用計算資料行實現移動加權平均演算法

複製代碼 代碼如下:
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等時間標誌性欄位來進行排序。
--------------------------------------------------------------------------------

第一次寫技術性部落格，希望這是一個好的開始，歡迎大家對我的演算法進行指正^_^