移動累計と移動平均
累計、または累積和とは、経時的に記録された数値を、ある時点まで足しあげたもののことです。例えば、銀行口座の入出金を記録するテーブルから、累計を求めることを考えます。
| 処理日（prc_date） | 処理金額（prc_amt） |
| 2006/10/26 | 12,000 |
| 2006/10/28 | 2,500 |
| 2006/10/31 | -15,000 |
| 2006/11/03 | 34,000 |
| 2006/11/04 | -5,000 |
| 2006/11/06 | 7,200 |
| 2006/11/11 | 11,000 |
正数の日は入金、負数の日は出金を意味します。すると各処理日までの処理金額の累計を求めるとは、その時点での口座残高を求めるということです。これを求めるには、OLAP関数を使う方法もありますが、まだ実装に依存します。SQL-92では次のように書きます。
SELECT prc_date, A1.prc_amt, (SELECT SUM(prc_amt) FROM Accounts A2 WHERE A1.prc_date >= A2.prc_date ) AS onhand_amt FROM Accounts A1 ORDER BY prc_date;
prc_date prc_amt onhand_amt ---------- --------- ------------ 2006/10/26 12,000 12,000 -- 12,000 2006/10/28 2,500 14,500 -- 12,000 + 2,500 2006/10/31 -15,000 -500 -- 12,000 + 2,500 + (-15,000) 2006/11/03 34,000 33,500 -- 12,000 + 2,500 + (-15,000) + 34,000 2006/11/04 -5,000 28,500 -- 以下同様 2006/11/06 7,200 35,700 -- ： 2006/11/11 11,000 46,700 -- ：
説明なしでいきなり答えを書いてしまいしたが、このクエリ、どこかで見た覚えがないでしょうか？ 実はこれ、『自己結合の使い方』の回で取り上げたランキングの算出方法とまったく同型のクエリなのです。ただ、COUNTをSUMに置き換えただけです。累計もまた、ノイマン型の再帰集合を利用して求められるわけです。気付きました？
さて、ここまでは復習。本題はここからです。今、累計ということで、特に期間の指定なしに1番古いデータから足しこみました。今度は、「処理3回単位の累計」、つまり移動累計を求める方法を考えてみましょう。「移動」という語が表すように、累計対象を3行に限定して、1行ずつずらしていきます。
考え方としては、さっきの累計を求めるクエリの条件に、「A2の処理日とA1の処理日の間に含まれるレコードが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 ) AS mvg_sum FROM Accounts A1 ORDER BY prc_date;
prc_date prc_amt mvg_sum ---------- --------- ---------- 2006/10/26 12,000 12,000 --12,000 2006/10/28 2,500 14,500 --12,000 + 2,500 2006/10/31 -15,000 -500 --12,000 + 2,500 + (-15,000) 2006/11/03 34,000 21,500 --2,500 + (-15,000) + 34,000 2006/11/04 -5,000 14,000 --以下同様 2006/11/06 7,200 36,200 -- ： 2006/11/11 11,000 13,200 -- ：
A3.prc_dateが始点（A2.prc_date）と終点（A1.prc_date）の間を動くと考えてください。「<= 3」の数を変えることで、4行単位でも5行単位でも、好きな幅で集計対象の期間を移動させることができます。このクエリでは最初の2行についても、一応使える限りでのデータを足して値を表示していますが、3行に満たない間は無効扱いとすることもできます。次のように、HAVING句で要素数がちょうど3の集合を見つけます（「<=3」の条件を「=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 mvg_sum --3行未満は非表示 FROM Accounts A1 ORDER BY prc_date;
prc_date prc_amt mvg_sum ---------- --------- ---------- 2006/10/26 12,000 --3行未満なので非表示 2006/10/28 2,500 --3行未満なので非表示 2006/10/31 -15,000 -500 --3行そろったので出力 2006/11/03 34,000 21,500 2006/11/04 -5,000 14,000 2006/11/06 7,200 36,200 2006/11/11 11,000 13,200
もしこのクエリの動作が分かりにくかったら、一度、非グループ化した結果を表示して、中身を見てみると理解しやすいでしょう。
SELECT A1.prc_date AS A1_date, A2.prc_date A2_date, A2.prc_amt AS amt FROM Accounts A1, 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 ORDER BY A1_date, A2_date;
A1_date A2_date amt ------------ ------------ --------- 2006/10/26 2006/10/26 12,000 2006/10/28 2006/10/26 12,000 2006/10/28 2006/10/28 2,500 2006/10/31 2006/10/26 12,000 2006/10/31 2006/10/28 2,500 …S1：-500 2006/10/31 2006/10/31 -15,000 2006/11/03 2006/10/28 2,500 2006/11/03 2006/10/31 -15,000 …S2：21,500 2006/11/03 2006/11/03 34,000 2006/11/04 2006/10/31 -15,000 2006/11/04 2006/11/03 34,000 …S3：14,000 2006/11/04 2006/11/04 -5,000 2006/11/06 2006/11/03 34,000 2006/11/06 2006/11/04 -5,000 …S4：36,200 2006/11/06 2006/11/06 7,200 2006/11/11 2006/11/04 -5,000 2006/11/11 2006/11/06 7,200 …S5：13,200 2006/11/11 2006/11/11 11,000
こうして展開すると、基本的な考え方はノイマン型の再帰集合と同じですが、入れ子ではなく、部分的に重なりあいつつ「ずれて」いく、幾つもの集合を作っていることが分かります。S3などすべての集合と共通部分を持っています。
この集合群をノイマン型の同心円的な入れ子集合と比べてみると、集合の作り方にもいろいろなヴァリエーションがあることが分かって、なかなか興味深いものがあります。自己結合の回のキーワードが「入れ子（再帰）」だったとすれば、今回のキーワードは、差し詰め「ずらし」です。
また、今回は累計を考えたため、SUM関数を使いましたが、移動平均（moving average）を求めたいなら、SUMをAVGで置き換えるだけでOKです。
