SQLアタマアカデミー

最終回 OLAP関数で強力な統計処理を実現!―手続き型から理解するSQL (3)OLAP専用関数

この記事を読むのに必要な時間:およそ 0.5 分

移動平均:対象行数を制限したOLAP関数

さて,最初の例で見た,カレントの日付までの累積的な平均は,特にレコード数を指定せず,過去いくらでもさかのぼって平均の計算式に追加していました。そのため今のサンプルデータでは,最大5日分の平均が計算されることになります。

今度はこれを少し改変して,常に直近3日間の平均を算出してみましょう。こういう算出対象のレコード数を一定に保って,期間をずらしていく平均値を移動平均(moving average)といって,統計でここ最近の実績を見たい場合によく使います。

OLAP関数には,このために対応するオプションがちゃんと備わっています。OVER句に次のように1行追加するだけですリスト4,図5⁠。

リスト4 直近3日間の平均を算出(MySQLの場合[OLAP関数を使わない場合]は後述の演習問題参照)

--移動平均(3レコードごと):My SQL以外
SELECT account_id,
       record_date,
       prc_amt,
       AVG(prc_amt) OVER (PARTITION BY account_id
                              ORDER BY record_date
                               ROWS 2 PRECEDING) AS cumulative_avg_3
  FROM Accounts;

図5 リスト4の実行結果

account_id record_date  prc_amt  cumulative_avg_3
---------- -----------  -------  ----------------
A          2010-01-16     1,000              1000
A          2010-01-17    20,000             10500
A          2010-01-18    -3,000              6000
A          2010-01-19  -500,000           -161000
A          2010-01-20    23,000           -160000
B          2010-01-16    48,000             48000
B          2010-01-17    98,000             73000
B          2010-01-18    -5,000             47000
B          2010-01-19    30,000             41000
B          2010-01-20  -100,000            -25000
C          2010-01-16     5,000              5000
C          2010-01-17    12,000              8500
C          2010-01-18    30,000             15667
C          2010-01-19     1,000             14333
C          2010-01-20       500             10500

ROWS 2 PRECEDINGは,⁠計算対象を過去2 行までに制限する」という意味です(precedingは「過去の,先行する」という意味です⁠⁠。これと反対に,カレント行より未来の日付を計算に含めたい場合は,PRECEDINGの代わりにFOLLOWING(後続の)というキーワードを使ってROWS 2 FOLLOWINGとすることもできます。このように,ウィンドウの中でさらに制限された計算対象行の集合を「フレーム」と呼びます。

OLAP関数のルール 4
計算対象行の制限は,ウィンドウ(パーティション)の中でさらにフレームで行うことができる

さて,図5で口座Aについての結果を見てみると,リスト5のようにちゃんと直近3日分の平均が出ていることがわかります(最初の2日間は,3行に満たないためデータ数が不足していますが⁠⁠。

リスト5 口座Aの直近3日間の平均計算式

1/16:   1,000 = 1,000 / 1
1/17:  10,500 = (1,000 + 20,000) / 2
1/18:   6,000 = (1,000 + 20,000 + (-3,000)) / 3
1/19:-161,000 = (20,000 + (-3,000) + (-500,000)) / 3
1/20:-160,000 = ((-3,000) + (-500,000) + 23,000) / 3

この移動平均をOLAP関数を使わずに求めるのは,かなり難しい問題です。これは今回の演習問題としましょう。最終回ということで,難易度はA+です注6⁠。

注6)
回答と解説は,筆者のWebページ内にある「SQLアタマアカデミー サポートページに掲載しています。

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書:『達人に学ぶ SQL徹底指南書』(翔泳社、2008)訳書:J.セルコ『SQLパズル 第2版』(翔泳社、2007)

SQLアタマアカデミー:サポートページ