以下のようなテーブルがある
select * from accounts;
+------------+---------+
| prc_date | prc_amt |
+------------+---------+
| 2006-10-26 | 12000 |
| 2006-10-28 | 2500 |
| 2006-10-31 | -15000 |
| 2006-11-03 | 34000 |
| 2006-11-04 | -5000 |
| 2006-11-06 | 7200 |
| 2006-11-11 | 11000 |
+------------+---------+
各処理日の累計和を求める
select
prc_date
,prc_amt
,(
select
sum(prc_amt)
from
accounts a2
where
a1.prc_date >= a2.prc_date
) onhand_amt
from
accounts a1
+------------+---------+------------+
| prc_date | prc_amt | onhand_amt |
+------------+---------+------------+
| 2006-10-26 | 12000 | 12000 |
| 2006-10-28 | 2500 | 14500 |
| 2006-10-31 | -15000 | -500 |
| 2006-11-03 | 34000 | 33500 |
| 2006-11-04 | -5000 | 28500 |
| 2006-11-06 | 7200 | 35700 |
| 2006-11-11 | 11000 | 46700 |
+------------+---------+------------+
次は処理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
) mvg_sum
from accounts a1
+------------+---------+---------+
| prc_date | prc_amt | mvg_sum |
+------------+---------+---------+
| 2006-10-26 | 12000 | 12000 |
| 2006-10-28 | 2500 | 14500 |
| 2006-10-31 | -15000 | -500 |
| 2006-11-03 | 34000 | 21500 |
| 2006-11-04 | -5000 | 14000 |
| 2006-11-06 | 7200 | 36200 |
| 2006-11-11 | 11000 | 13200 |
+------------+---------+---------+
こちらを参考にさせていただきました。
達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ