Contents


実行環境、サンプルデータ（RのUKgas）
Windows7 32bit + Excel2007、PostgreSQL Portable 9.4.1。サンプルデータとしてR 3.1.2に付属のUKgasを使用。この詳細はR Documentation : UKgas{datasets} : UK Quarterly Gas Consumptionを参照。時系列の値が108個あり、これを一行ずつにしてクリップボードに入れます。

write(paste(collapse='\n', as.vector(UKgas)), file='clipboard')


Excelシートの適当な所に貼り付け。この後はExcelとPostgreSQLだけの作業です。


Excelの分析ツールの移動平均
ウェブや書籍で解説が多々ありますが、一応経過まで。事前にアドイン設定で分析ツールが有効になっている前提。最初に、貼り付けたUKgasデータの左側に番号を振ります。後でPostgreSQLの結果と照合しやすくするため。次に「データ」メニューを開き、右端にある「データ分析」をクリック。分析ツール一覧のダイアログが出るので、移動平均を選択。


移動平均のダイアログが出るので、入力範囲（元データ）、区間（過去何期分までのデータで平均するか）、出力先、グラフ作成するか、標準誤差を表示するかを設定します。入力範囲の指定が面倒で、「列全体のクリック → データ末端まで自動選択」とかしてくれません（Excel2007より後のバージョンは不明）。区間は現在行を含み、例えば「12」なら「11期前～現在」の平均。現在を中心とする前・後の平均はできず、そのへん柔軟にするならPostgreSQLのウィンドウ関数の方が得策。平均以外の計算にも使えます。当然ながらグラフはPostgreSQL以外で作ることになるけど。


とりあえず実行すると ↓ こんな感じ。元データ（B列）に対しC列が移動平均、D列が標準誤差。後者は過去～現在の12期分の元データと移動平均から計算するので、最初の22期分（11+11）はNAになります。二つ目の画像に出ている計算式を参照。


PostgreSQLでは
本来はサンプルデータをテーブルにすべきですが、今日は簡単なデータなのでクエリ中にコピペし、アドホックに2列にしました（ID、値）。unnest…WITH ORDINALITYを使い、PostgreSQL 9.4以降で動きます。この値に対してExcelと同じ過去12期分の移動平均および標準誤差を出すクエリは下のとおり。WITH句のbブロックまではデータの準備、cブロックで移動平均を算出、最後のブロックで標準誤差を付けています。
WITH a AS (
    SELECT text '

-- ここに UKgas データをコピペ

    '
), b AS (
    SELECT id, val
    FROM a, unnest(string_to_array(
        regexp_replace(text, '((^\s+)|(\s+$))', '', 'g'), E'\n') :: float[])
        WITH ORDINALITY AS t (val, id)
), c AS (
    SELECT id, val,
        CASE WHEN count(val) over w = 12 THEN
            avg(val) over w
        END AS avg12
    FROM b
    WINDOW w AS (ORDER BY id ROWS 11 PRECEDING)
)
SELECT id, val, avg12,
    CASE WHEN count(avg12) over w = 12 THEN
        sqrt(sum(pow(val - avg12, 2)) over w / 12)
    END AS se
FROM c
WINDOW w AS (ORDER BY id ROWS 11 PRECEDING);


↓pgAdminのクエリツールで実行した様子。


↓ 実行結果。Excelでの結果（下に再掲）と同じ。移動平均・標準誤差が算出されない（というか算出しても意味ない）先頭11期ないし22期分は、Excelでは#N/A、こちらではNULLです。


ダウンロード用ファイル（Excel, SQL）、まとめ

Excelの方は、上記の状態そのまま。SQLは先ほどのクエリ中にデータをコピペしてあり、PostgreSQL 9.4以降なら単独で実行できます（ウィンドウ関数は9.4以前も動きますが、今回unnest…WITH ORDINALITYを使っているので）。

対象とするデータが既存ならクエリはもっと簡単で、次のような2ブロックで済みます。
WITH c AS (
    SELECT id, val,
        CASE WHEN count(val) over w = 12 THEN
            avg(val) over w
        END AS avg12
    FROM your_table_or_view
    WINDOW w AS (ORDER BY id ROWS 11 PRECEDING)
)
SELECT id, val, avg12,
    CASE WHEN count(avg12) over w = 12 THEN
        sqrt(sum(pow(val - avg12, 2)) over w / 12)
    END AS se
FROM c
WINDOW w AS (ORDER BY id ROWS 11 PRECEDING);

Excelメニューでの「区間」の設定に相当するのが、クエリ中の12という数値と、WINDOW句内のROWS 11 PRECEDING。後者が「11期分～現在行」という計算対象（ウィンドウ、フレーム）の指定。これらを変えれば移動平均の対象を柔軟に変えられ、Excelの「過去～現在」だけでなく中心化移動平均（現在を中心とする前・後の値の平均）もできます。また平均以外も、上記クエリでの標準誤差のように自由に算出可能。WINDOW句に書く構文の詳細は下記を参照して下さい。


できれば「区間」の数値指定を一ヶ所にまとめたいけど、今のところWINDOW句内に変数を使えないので直接ROWS 11 PRECEDINGとか書く必要があり。これが、今回のように複数の集計をする場合は面倒で、後で変更が必要になった時、修正漏れが起きかねません。

WINDOW句を文字列化しクエリ全体を動的に作る手もありますが、他のクエリと組み合わせにくくなるので避けたい。別の方法で何かストアド関数を作って対処できないか、今後調べます。