sql應用案例：計算累計值、移動累計值/平均值

1. 計算每個時間點的累計值

方法1：視窗函數

SELECT prc_date, 
	SUM(prc_amt) OVER (ORDER BY prc_date) as acc_amt 
FROM accounts a 
           

方法2：馮·諾伊曼遞歸

SELECT prc_date, a1.prc_amt 
	(SELECT sum(prc_amt)
FROM accounts a2
WHERE a1.prc_date >= a2.prc_date ) as acc_amt 
from accounts a1 
order by prc_date;
           

2. 以3次為機關求累計值，即求移動累計值

方法1: 視窗函數

SELECT prc_date,
	SUM(prc_amt) OVER(ORDER BY prc_date ROWS 2 PRECENDING) as acc_amt 
FROM accounts 
           

方法2: 标量子查詢

SELECT prc_date, a1.prc_date
	(SELECT SUM(prc_amt) 
	FROM accounts a2 
	WHERE a1.prc_date >= a2.prc_date
	AND 
	(SELECT count(*) FROM accounts a3 
	WHERE a3.prc_date BETWEEN a2.prc_date AND a1.prc_date) <=3)
	AS acc_amt	
FROM accounts a1
ORDER BY prc_date; 
           

2. 移動累計值，不滿3行按無效處理

SELECT prc_date, a1.prc_amt,
	(SELECT SUM(prc_amt)
	FROM accounts a2 
	WHERE a1.prc_date >= a2.prc_date
	AND 
	(SELECT COUNT(*) FROM accounts a3
	WHERE a3.prc_date BETWEEN a2.prc_date AND a1.prc_date ) <= 3
	HAVING COUNT(*) = 3 ) AS acc_amt --不到3行資料的不顯示
FROM accounts a1
ORDER BY prc_date;