【PL/SQL】カスタム集計関数(User Defined Aggregate)の作り方

【PL/SQL】カスタム集計関数(User Defined Aggregate)の作り方 PL/SQL

Oracle Databaseでは、標準のSUMやAVGなどに加えて、独自の集計ロジックを持つ「ユーザー定義集計関数(User Defined Aggregate, UDA)」を作成できます。PL/SQLオブジェクト型を利用することで、任意のアルゴリズムで集約処理を実装でき、分析レポートや統計処理など、標準関数では表現できない複雑な集計を柔軟に行うことが可能です。この記事では、UDAの仕組みと実装手順、実際の使用例を解説します。

ユーザー定義集計関数の仕組み

UDAは、内部的に「オブジェクト型」を使って以下の4つのメソッドを定義します。これらがSQLの`GROUP BY`集計処理と連携して呼び出されます。

1. **ODCIAggregateInitialize**:初期化処理(最初の行で呼ばれる)
2. **ODCIAggregateIterate**:行ごとのデータを集約
3. **ODCIAggregateMerge**:並列処理時に部分集計結果を統合
4. **ODCIAggregateTerminate**:最終結果を返す

Oracleはこれらのメソッドを自動的に呼び出すことで、標準集計関数と同様にUDAを動作させます。

例:文字列を連結するカスタム集計関数

ここでは例として、Oracle 10g以前には存在しなかった「文字列連結関数(LISTAGGに相当)」をUDAで自作してみます。

1. オブジェクト型の定義

まずは集約ロジックを持つオブジェクト型を定義します。

-- カスタム集計オブジェクト型
CREATE OR REPLACE TYPE string_agg_type AS OBJECT (
  total VARCHAR2(4000),

  -- 初期化処理
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    RETURN NUMBER,

  -- 各行の値を追加
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, value IN VARCHAR2)
    RETURN NUMBER,

  -- 並列実行時の部分集計統合
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
    RETURN NUMBER,

  -- 結果を返す
  MEMBER FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER)
    RETURN NUMBER
);
/

2. オブジェクト本体の実装

CREATE OR REPLACE TYPE BODY string_agg_type AS

  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    RETURN NUMBER IS
  BEGIN
    sctx := string_agg_type(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, value IN VARCHAR2)
    RETURN NUMBER IS
  BEGIN
    IF value IS NOT NULL THEN
      IF self.total IS NULL THEN
        self.total := value;
      ELSE
        self.total := self.total || ', ' || value;
      END IF;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
    RETURN NUMBER IS
  BEGIN
    IF ctx2.total IS NOT NULL THEN
      IF self.total IS NULL THEN
        self.total := ctx2.total;
      ELSE
        self.total := self.total || ', ' || ctx2.total;
      END IF;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := self.total;
    RETURN ODCIConst.Success;
  END;

END;
/

このオブジェクト型がUDAの本体です。Oracleが各行を処理するときに、`Iterate`が順に呼ばれ、最後に`Terminate`で集約結果を返します。

3. 関数定義(ファサード)

次に、この型をSQLから呼び出せるように関数として登録します。

CREATE OR REPLACE FUNCTION string_agg (input VARCHAR2)
  RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

これでUDAとして利用可能になります。

SQLでの使用例

UDAは標準集計関数と同じ構文で使用できます。

-- サンプルテーブル
CREATE TABLE sample_data (dept VARCHAR2(10), name VARCHAR2(50));

INSERT INTO sample_data VALUES ('A', '田中');
INSERT INTO sample_data VALUES ('A', '佐藤');
INSERT INTO sample_data VALUES ('B', '鈴木');
INSERT INTO sample_data VALUES ('B', '高橋');
COMMIT;

-- カスタム集計関数を使った文字列連結
SELECT dept, string_agg(name) AS members
  FROM sample_data
 GROUP BY dept;

出力結果:

DEPT | MEMBERS
-----|----------------
A    | 田中, 佐藤
B    | 鈴木, 高橋

並列実行(PARALLEL ENABLE)の意味

定義内の`PARALLEL_ENABLE`は、並列実行時に`ODCIAggregateMerge`メソッドを呼び出すための宣言です。これを指定しないと、並列クエリ環境でUDAが正しく動作しない場合があります。
大量データを扱う場合には、必ず`Merge`メソッドも実装しておくと安全です。

他の応用例

ユーザー定義集約関数は、単なる文字列連結だけでなく以下のような高度な処理にも応用できます。

– **中央値(MEDIAN)や分位点の算出**
ソートして中間値を返すUDAを定義可能。
– **JSON集約**
各行をJSON文字列に変換して配列化することで、REST API連携に最適。
– **集約ロギング**
複数エラーをまとめて出力したり、集計単位ごとにトランザクションメッセージを生成する用途にも。

実運用での注意点

– 集約対象のデータ型は固定となるため、柔軟性が必要なら動的SQLを併用する。
– 集約結果の長さがVARCHAR2(4000)を超える場合、CLOBを使うように変更する。
– 権限は`CREATE TYPE`と`CREATE FUNCTION`を持つユーザーで行う。
– 変更時は依存関係を考慮して`TYPE BODY`→`FUNCTION`の順に再コンパイルする。

まとめ

ユーザー定義集約関数(UDA)を使えば、PL/SQLでも柔軟な集約処理をSQL文中に組み込めます。
標準関数にない独自ロジックをSQLレベルで表現できるため、集計ロジックの一元化やパフォーマンス向上に有効です。
特に、文字列連結・JSON集約・カスタム統計などを扱う場合、UDAは強力な選択肢となるでしょう。