昨日との違い、実行環境、参考サイト

昨日は「自作の集約関数で、結果を複合型で返す」場合でした。今回は、集計途中の「遷移状態」で複数の値を持たせたい時の方法を3通り、3回に分けて書きます。実行環境はWindows7 32bit + PostgreSQL Portable 9.4.1。

遷移状態を平たく言うと、集計対象のレコードを一つずつスキャンする際に受け渡されるデータ。例えば単純な総和（sum）なら、1レコードずつ値を足したものになります。この最終状態を集約関数の結果にするか、別の関数（最終計算関数）で処理したものを結果にします。昨日は専ら後者についてでした。

いろいろな集約で、遷移状態に複数の値が必要なケースがあります。例えば単純な相加平均（avg）は全レコードの総和と個数、分散なら二乗和と総和と個数、など。ここで複数の値の持ち方に3通りあるので、それぞれ簡単な例をやってテンプレ化&比較しておこうというのが、今日の趣旨です。

下記が、昨日と重複しますが参考サイト。複合型を使う例が三つ目に、配列を使う例が四つ目にあります。ただし三つ目は複合型定義や状態遷移関数の中身が書かれてないため、分かりづらいかも。

複合型

-- 複合型を定義
CREATE TYPE sum2_sum_cnt AS (sum2 float, sum float, cnt int);

-- 状態遷移関数
CREATE OR REPLACE FUNCTION sfunc2_rec(sum2_sum_cnt, float)
RETURNS sum2_sum_cnt LANGUAGE plpgsql IMMUTABLE AS $_$
BEGIN
    IF $2 IS NULL THEN
        RETURN $1;
    ELSE
        RETURN ($1.sum2 + pow($2, 2), $1.sum + $2, $1.cnt + 1);
    END IF;
END;
$_$;

-- 集約関数
CREATE AGGREGATE agg2_rec(float) (
    sfunc = sfunc2_rec,
    stype = sum2_sum_cnt,
    initcond = '(0, 0, 0)'
);

例として「二乗和、総和、データ個数」を遷移させ、そのまま最終出力しました。最初に複合型を定義し、次に状態遷移関数を、最後に集約関数を定義します。

状態遷移関数では、NULLが来た場合の考慮が必要。また遷移状態の複合型から値を取り出す際 $1.列名と記述しますが、この例がウェブ上に余りなかった気がします。集約関数の初期値initcondは '(列1,列2,列3, ...)' とクォートして丸括弧で並べ、配列の '{要素,要素, ...}' と混同しないよう注意。

↓ 使用例。数値は「統計計算の方法：個別データの分散、標準偏差など」（明星大学船津好明先生）から拝借し、途中にNULLを加えました。複合型なので、計算に使う際は各列にばらす所が少し面倒。

-- 使用例
SELECT agg2_rec(val)
FROM unnest('{28,7,11,22,9,NULL,16,15,19}' :: float[]) AS val;

   agg2_rec
--------------
 (2361,127,8)
(1 行)

-- 使用例、列をばらす
SELECT (agg2_rec(val)).*
FROM unnest('{28,7,11,22,9,NULL,16,15,19}' :: float[]) AS val;

 sum2 | sum | cnt
------+-----+-----
 2361 | 127 |   8
(1 行)

-- 結果を使った計算例（相加平均と母分散）
WITH a AS (
    SELECT (agg2_rec(val)).*
    FROM unnest('{28,7,11,22,9,NULL,16,15,19}' :: float[]) AS val
)
SELECT *, sum / cnt AS my_avg,
    sum2 / cnt - pow(sum / cnt, 2) AS my_var_pop
FROM a;

 sum2 | sum | cnt | my_avg | my_var_pop
------+-----+-----+--------+------------
 2361 | 127 |   8 | 15.875 |  43.109375
(1 行)

-- 上の結果を、PostgreSQLに元からある集約関数で確認
SELECT avg(val), var_pop(val)
FROM unnest('{28,7,11,22,9,NULL,16,15,19}' :: float[]) AS val;

  avg   |  var_pop
--------+-----------
 15.875 | 43.109375
(1 行)

複合型からの列の取り出しが少し面倒だけど、その後は普通の列と同様なので扱いやすいです。明日の配列は添字で取り出すしかなく、実用性が低い気がしますが一応、一つの方法として書きます。