【SQL】移動平均の算出について

Question

■環境

SQL Server2016(MicrosoftのSQL)

■やりたいこと

日付	金額
2019/1/1	500
2019/2/1	1000
2019/3/1	1500

例えば上記のようなテーブルがあった場合に、
２行目は
(500 + 1000) / 2 = 750
３行目は
(750 + 1500) / 2 = 1125
といったように、１行前の計算結果を基に移動平均を算出したいです。(1125を取得したいです)

最適な方法が見つからず、質問をさせて頂きました。
お手数ですが、何か良い方法があれば教えて頂ければと思います。

■補足(これはNG)

よく移動平均と聞くとWindow関数を使った解法が見つかりますが、
今回は「１行前の計算結果を基に移動平均を算出したい」のでNGです。

SELECT TOP 1
	AVG(金額) OVER(ORDER BY 日付 ASC) AS 結果
FROM 仕入
ORDER BY RANK() OVER(ORDER BY 日付 ASC) DESC

※結果は1000

■現状考えている案

○案１(カーソルを使って回す)

DECLARE crTriggerList CURSOR FOR
	SELECT 
		金額,
		RANK() OVER(ORDER BY 日付 ASC) AS RK
	FROM 仕入
	ORDER BY RK ASC

DECLARE @金額 decimal
DECLARE @RK bigint
DECLARE @結果 decimal

OPEN crTriggerList;
FETCH NEXT FROM crTriggerList
INTO @金額, @RK

WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@RK = 1)
		SET @結果 = @金額
	ELSE
		SET @結果 = (@金額 + @結果) / 2

	FETCH NEXT FROM crTriggerList
    INTO @金額, @RK;
END
CLOSE crTriggerList;
DEALLOCATE crTriggerList;

SELECT @結果 AS 結果

○案２(RANK付けした内容を一次テーブルに書き込んだうえ、再帰クエリで回す)

SELECT 
	日付,
	金額,
	RANK() OVER(ORDER BY 日付 ASC) AS RK
	INTO #HOGE
FROM 仕入;

with [CTE] ([日付], [金額],[RK]) as (
	SELECT
		日付, 金額, RK
	FROM #HOGE
	WHERE RK = 1

	UNION ALL  

	SELECT 
		日付, 金額, RK
	FROM
	(
		SELECT
		HOGE.日付,
		CONVERT(decimal, (HOGE.金額 + [CTE].金額) / 2) AS 金額,
		HOGE.RK
		FROM #HOGE AS HOGE
		INNER JOIN 
		[CTE]
		ON HOGE.RK = [CTE].[RK] + 1
	)AS HUGA 
)
SELECT 金額 AS 結果 FROM [CTE]
WHERE RK IN(SELECT MAX(RK) FROM [CTE])

Comments

[ikedas]

そもそも、おっしゃるような「１行前の計算結果を基に」算出するものは「移動平均」ではないと思うのですが……。最初の列からあとのすべての列の値をもとに算出していることになるので、平均値を取る範囲が移動していませんよね。

[hoshiimohunda]

コメントを有難うございます。
便宜上計算はかなり簡略化させていただきましたが、
法人税法の棚卸資産の評価方法における移動平均法が記載させていただいたような考え方になります。

http://xn--7mq406l.net/?p=3971

https://www.zeiken.co.jp/yougo/%E6%B3%95%E4%BA%BA%E7%A8%8E/%E5%90%84%E4%BA%8B%E6%A5%AD%E5%B9%B4%E5%BA%A6%E3%81%AE%E8%AA%B2%E7%A8%8E%E6%89%80%E5%BE%97%E3%81%AE%E8%A8%88%E7%AE%97/%E6%B3%95%E4%BA%BA%E3%81%AE%E6%90%8D%E9%87%91%E3%80%80%E5%95%86%E5%93%81%E3%83%BB%E8%A3%BD%E5%93%81%E7%AD%89%E3%81%AE%E5%A3%B2%E4%B8%8A%E5%8E%9F%E4%BE%A1/%E5%8E%9F%E4%BE%A1%E6%B3%95.html

[ikedas]

簡単なほうの解説をさわりだけ読みました。平均原価をここで説明している移動平均法で算出したいのですね。
・ご質問の説明だと入庫 (受入というのかな?) だけを考慮して出庫 (払出?) を考慮していないことになりませんか。
・各行ごとの入出庫数量がわからないと平均原価はだせないのでは。

[hoshiimohunda]

コメントを有難うございます。

厳密には記載いただいた受払数量も考慮致します。

ここでは二行目の計算結果を三行目で使うような手法について知りたかったので、計算式は簡略化させていただきました。
紛らわしくてすみません。

[ikedas]

前回の平均原価だけあっても在庫総額と在庫数はわからないですよね。つまり、ご質問の計算方法自体がまちがっているので、結果の「1125」という数値は無意味です。まちがった方法を前提に質問されても、正確な回答は得られないとおもいます。