1. データ・カートリッジ開発者ガイド
  2. データ・カートリッジの作成
  3. ユーザー定義集計関数の使用

12 ユーザー定義集計関数の使用

ユーザー定義集計関数は、単独でもパラレルでも使用できます。ここでは、大規模な集約コンテキストおよびマテリアライズド・ビューについて考えます。

関連項目:

ODCIAggregateインタフェースの詳細は、「ユーザー定義集計関数インタフェース」を参照してください。

12.1 ユーザー定義集計関数の概要

Oracleには、一連の行に対する操作を実行できるように、MAX、MIN、SUMなど、いくつかの事前定義済集計関数が用意されています。これらの事前定義済集計関数を使用できるのはスカラー・データの場合のみで、オブジェクト型、不透明型およびLOBを使用して格納されるマルチメディア・データのような複合データ型には使用できません。ただし、これらの関数のカスタム実装を複合データ型に対して定義することはできます。また、複合データで使用するために新規の集計関数を定義することも可能です。ユーザー定義集計関数は、Oracleの組込み集計と同様にSQL DML文に使用できます。関数がサーバーに登録されていると、ネイティブのルーチンではなく、指定したユーザー定義集計ルーチンをコールするだけで済みます。ユーザー定義集計は、科学アプリケーションに必要な複雑な統計データのようなスカラー・データにも使用できます。

ユーザー定義集計は拡張フレームワークの機能であり、ODCIAggregateインタフェース・ルーチンを使用して実装できます。

ユーザー定義集計関数を作成するには、総称的にODCIAggregateルーチンと呼ばれる一連のルーチンを実装します。これらのルーチンは、オブジェクト型のメソッドとして実装できるため、実装にはOracleでサポートされる言語(PL/SQL、C、C++またはJava)を使用できます。オブジェクト型を定義し、型本体にルーチンを実装したら、CREATE FUNCTION文を使用して集計関数を作成します。

各ユーザー定義集計関数では、最大4つのODCIAggregateルーチン(ステップ)を使用して、任意の集計関数で実行する初期化、反復、マージおよび終了などの内部操作を定義します。

  • 初期化はODCIAggregateInitialize()ルーチンによって行われます。このルーチンは、ユーザー定義集計の計算を初期化するためにOracleによって呼び出されます。初期化された集計コンテキストは、オブジェクト型インスタンスとしてOracleに返されます。

  • 反復は、Oracleにより繰り返し起動されるODCIAggregateIterate()ルーチンを介して実行されます。起動されるたびに、新規の値または値セットと現行の集計コンテキストが渡されます。このルーチンは、新規の値を処理して更新後の集計コンテキストを戻します。このルーチンは、基礎となるグループ内のNULLでない値ごとに起動されます。NULL値は、集計時には無視され、ルーチンには渡されません。

  • 統合はODCIAggregateMerge()によって行われます。これは2つの集計コンテキストを組み合せるためにOracleによって呼び出されるルーチンです。このルーチンは、2つのコンテキストを入力として受け取り、それらを結合して、単一の集計コンテキストを戻します。

  • 終了は、Oracleにより集計の最終ステップとしてODCIAggregateTerminate()ルーチンが起動されるときに発生します。このルーチンは入力として集計コンテキストを取り、結果の集計値を戻します。

このプロセスについては「ユーザー定義集計関数の使用」の項で説明します。

12.1.1 ユーザー定義集計関数の使用

次の文の集計関数AVG()を考えてみます。 

SELECT AVG(T.Sales)
FROM AnnualSales T
GROUP BY T.State;

この計算を実行するために、集計関数AVG()は次のようなステップで実行されます。

  1. 初期化。集計コンテキスト(集計を実行する行)を初期化して計算を初期化します。
    runningSum = 0; runningCount = 0;
  2. 反復。連続する入力値をそれぞれ反復的に処理してコンテキストを更新します。
    runningSum += inputval; runningCount++;
  3. [オプション]マージ。2つの集計コンテキストを組み合せてマージし、単一のコンテキストを戻します。この操作は、サブセットに対する集計の結果を結合して、セット全体の集計を取得します。この追加のステップは、集計をシリアルに評価する場合とパラレルに評価する場合のどちらでも必要となることがあります。必要に応じて、ステップ4の前に実行されます。
    runningSum = runningSum1 + runningSum2;
runningCount = runningCount1 + runningCount2

    この手順の詳細は、「ユーザー定義集計のパラレル評価」を参照してください。

  4. 終了。結果を計算し、コンテキストを使用して結果の集計値を戻します。
    return (runningSum/runningCount);

AVG()がユーザー定義関数の場合、この関数を含むオブジェクト型では前述のステップごとに対応するODCIAggregateルーチンのメソッドが実装されます。変数runningSumおよびrunningCount(この例では集計の状態を判別)は、そのオブジェクト型の属性です。

12.2 ユーザー定義集計の作成

ユーザー定義集計関数の作成プロセスは、例12-1および例12-2に示す2つのステップで構成されます。どちらの例でも、Oracle Spatialで定義されたSpatialUnion()集計関数を使用しています。この関数は、一連の入力ジオメトリのバインド・ジオメトリを計算します。

例12-1 ODCIAggregateインタフェースの実装

ODCIAggregateルーチンは、オブジェクト型SpatialUnionRoutines内のメソッドとして実装されます。実際の実装には、PL/SQL、C、C++またはJavaなど、型のメソッドについてOracleでサポートしている任意の言語を使用できます。 

CREATE TYPE SpatialUnionRoutines(
   STATIC FUNCTION ODCIAggregateInitialize( ... ) ...,
   MEMBER FUNCTION ODCIAggregateIterate(...) ... ,
   MEMBER FUNCTION ODCIAggregateMerge(...) ...,
   MEMBER FUNCTION ODCIAggregateTerminate(...)
);

CREATE TYPE BODY SpatialUnionRoutines IS 
...
END;

例12-2 ユーザー定義集計関数の定義

この関数定義では、関数のシグネチャとODCIAggregateインタフェースを実装するオブジェクト型を指定してSpatialUnion()集計関数が作成されます。 

CREATE FUNCTION SpatialUnion(x Geometry) RETURN Geometry 
AGGREGATE USING SpatialUnionRoutines;

12.3 ユーザー定義集計の使用

ユーザー定義集計は、SQL DML文および問合せ文の組込み集計関数と同様に使用できます。これらの関数は、SELECT構文のリストやORDER BY句に使用したり、HAVING句の条件の一部として使用できます。次の例12-3、例12-4および例12-5でいくつかのオプションを示します。

関連項目:

ROLLUP、CUBEおよびグルーピング・セットなどのGROUP BY拡張の詳細は、『Oracleデータ・ウェアハウス・ガイド』を参照してください。

12.3.1 SELECT文とユーザー定義集計関数の使用

例12-3 SELECT文とユーザー定義集計関数の使用

次の問合せを使用すると、同じ州に属しているすべての郡のジオメトリを集計して州の境界を計算できます。 

SELECT SpatialUnion(geometry)
FROM counties
GROUP BY state

12.3.2 HAVING句とユーザー定義集計関数の使用

例12-4 HAVING句とユーザー定義集計関数の使用

ユーザー定義集計をHAVING句に使用すると、集計関数の結果に基づいて出力からグループを除外できます。この例では、MyUDAG()はユーザー定義集計です。 

SELECT groupcol, MyUDAG(col)
FROM tab
GROUP BY groupcol
HAVING MyUDAG(col) > 100
ORDER BY MyUDAG(col);

12.3.3 問合せオプションとユーザー定義集計関数の使用

例12-5 他の問合せオプションとユーザー定義集計関数の使用

ユーザー定義集計は、入力パラメータでDISTINCTまたはALL(デフォルト)オプションを取ることができます。DISTINCTを指定すると、集計の計算時に重複値が無視されます。ユーザー定義集計を含むSELECT文には、ROLLUP、CUBEおよびグルーピング・セットなどのGROUP BY拡張も含めることができます。 

SELECT ..., MyUDAG(col)
FROM tab
GROUP BY ROLLUP(gcol1, gcol2);

この種のロールアップ操作で超集計値を計算するために、ODCIAggregateMerge()インタフェースが起動されます。

12.4 ユーザー定義集計のパラレル評価

組込み集計関数と同様に、ユーザー定義集計もパラレルで評価できます。

パラレル・スレーブ内の行のサブセットを集計することで生成される集計コンテキストは、次のパラレル・ステップ(問合せコーディネータまたは次のスレーブ・セット)に送られます。次に集計コンテキストがマージされ、終了ルーチンが起動されて集計値が取得されます。この動作を図12-1に示します。

図12-1 ユーザー定義集計のパラレル評価のためのコール順序

図12-1の説明が続きます
「図12-1 ユーザー定義集計のパラレル評価のためのコール順序」の説明

集計関数は、例12-6のようにパラレル対応として宣言する必要があることに注意してください。

例12-6 ユーザー定義集計関数のパラレル対応化

CREATE FUNCTION MyUDAG(...) RETURN ...
PARALLEL_ENABLE AGGREGATE USING MyAggrRoutines;

12.5 大規模な集計コンテキストの処理

実装タイプのメソッドがC++やJavaのような外部言語で実装される場合は、実装タイプのメソッドがコールされるたびに、Oracleサーバー・プロセスと外部関数の言語環境の間で集計コンテキストがやりとりされる必要があります。これにより集計コンテキストのサイズが大きくなるため、パフォーマンスが低下する可能性があります。

パフォーマンスを強化するために、集計コンテキストを外部関数の実行環境で割り当てられた外部メモリーに格納できます。これにより、Oracleサーバーと外部関数の間で参照またはキーをやりとりできます。キー自体は、実装タイプのインスタンスselfに格納する必要があります。この方法では、実装タイプのインスタンスが小型に保たれるため、迅速に転送できます。この方法のもう1つのメリットは、集計コンテキストの保持に使用されるメモリーが、Oracleサーバーではなく関数の実行環境(extprocなど)で割り当てられることです。

通常、集計コンテキストを保持するメモリーを割り当てて、その参照を実装タイプのインスタンスに格納するには、ODCIAggregateInitialize()を使用する必要があります。以降のコールでは、外部メモリーとそこに格納された集計コンテキストに参照を使用してアクセスできます。通常、外部メモリーはODCIAggregateTerminate()で解放する必要があります。ODCIAggregateMerge()では、マージの終了後にマージ済コンテキスト(ODCIAggregateMerge()の第2引数)の格納に使用された外部メモリーを解放します。

関連項目:

12.5.1 外部コンテキストとパラレル集計

ユーザー定義集計を使用した問合せのパラレル実行では、スレーブ・プロセスにより計算されたすべての部分集計で構成される集計コンテキスト全体を、別のスレーブまたはマスター・プロセスに転送する処理が必要になる場合があります。オプションのルーチンODCIAggregateWrapContext()を実装すると、すべての部分集計を収集できます。ユーザー定義集計がパラレルで評価される場合に、ODCIAggregateWrapContext()が定義されていれば、Oracleはルーチンを起動して外部コンテキスト参照をすべて実装タイプのインスタンスにコピーし、外部メモリーを解放します。ODCIAggregateWrapContext()をサポートするには、集計コンテキストを保持する属性と、外部メモリーの識別キーを保持する別の属性を、実装タイプに含める必要があります。

集計コンテキストが外部に格納される場合、実装タイプのキー属性には外部メモリーを識別する参照を含み、また実装タイプの他の属性をNULLに設定する必要があります。ODCIAggregateWrapContext()コールが正常に実行された後、キー属性はNULLになり、他の属性には実際の集計コンテキストが保持されます。

実装タイプの各メンバー・メソッドは、最初にコンテキストがインライン(実装タイプのインスタンスに含まれる)か、または外部メモリーにあるかをチェックする必要があります。他のパラレル・スレーブから送信された場合のように、コンテキストがインラインの場合は、参照渡しできるように外部メモリーにコピーします。

ODCIAggregateWrapContext()ルーチンの実装はオプションです。これは、外部メモリーに集計コンテキストが保持され、ユーザー定義集計がパラレルで評価される場合にのみ必要です。ユーザー定義集計がパラレルで評価されない場合、ODCIAggregateWrapContext()は不要です。ODCIAggregateWrapContext()メソッドが定義されていなければ、集計コンテキストは外部に格納されないとみなされ、このメソッドはコールされません。

注意:

ODCIAggregateWrapContext()

12.5.1.1 外部メモリーを使用した集約コンテキストの格納

この例は、外部メモリーへの参照を含む集計コンテキスト・タイプに、必要に応じてコンテキスト全体も格納する方法を示しています。

4バイトkeyパラメータは、外部コンテキストの検索に使用されます。NULLは、コンテキスト値全体がオブジェクト内の他の属性に保持されていることを示します。GeometrySetなど、他の属性は実際の集計コンテキストに対応します。key値がNULLでない場合は、これらの属性をNULL値にする必要があります。ただし、ODCIAggregateWrapContext()のコール後のようにコンテキスト・オブジェクトが自己完結型の場合、これらの属性では現行のコンテキスト値が保持されます。 

CREATE TYPE MyAggrRoutines AS OBJECT
(
key RAW(4),
ctxval GeometrySet,
ctxval2 ...
);

12.5.2 ユーザー定義集計と分析関数

分析関数を使用すると、ウィンドウと呼ばれる行セットに対して各種の累積集計、移動集計およびセンター集計を計算できます。分析関数では、表の行ごとに特定の行のウィンドウに含まれる他の行で計算された値が戻されます。これらの関数を使用すると、自己結合なしで表の複数の行にアクセスできます。ユーザー定義集計は分析関数として使用できます。

12.5.2.1 ユーザー定義集計と分析関数の使用 
SELECT Account_number, Trans_date, Trans_amount,
   MyAVG (Trans_amount) OVER(
      PARTITION BY Account_number ORDER BY Trans_date
      RANGE INTERVAL '7' DAY PRECEDING) AS mavg_7day
FROM Ledger;

12.5.3 分析関数への集計コンテキストの再利用

ユーザー定義集計を分析関数として使用すると、集計は各行の対応するウィンドウについて計算されます。通常、連続する各ウィンドウには、新規集計コンテキスト、新規ウィンドウなど、古い集計コンテキストや前のウィンドウとは数行が異なるのみの、ほぼ同じ行セットが含まれます。集計コンテキストを再利用するには、古いコンテキストになかった新規の行を反復して追加し、新規コンテキストに属さない行を古いコンテキストから削除する必要があります。集計コンテキストを再利用できない場合は、そこに含まれる行をすべて再度反復して再作成する必要があります。

オプションのルーチンODCIAggregateDelete()を実装すると、Oracleで集計コンテキストを効率的に再利用できます。ODCIAggregateDelete()により、新規(現行)のウィンドウにない集計コンテキスト行が前のコンテキストから削除されます。このルーチンは、削除する必要のある行ごとにコールされます。追加する必要のある行ごとに、ODCIAggregateIterate()がコールされます。

新規集計コンテキストが古い集計コンテキストのスーパーセットである場合は、古いコンテキストの行がすべて含まれており、行を削除する必要はありません。この場合は、ODCIAggregateDelete()が実装されていなくても古いコンテキストが再利用されます。

関連項目:

12.5.4 外部コンテキストとユーザー定義分析関数

ユーザー定義集計を分析関数として使用すると、あるウィンドウの集計コンテキストを次のウィンドウに再利用できます。このような場合、ODCIAggregateTerminate()ファンクションのフラグ引数のODCI_AGGREGATE_REUSE_CTXビットは、集計コンテキストを保持する外部メモリーを解放しないことを示すように設定されます。また、ODCIAggregateInitialize()メソッドには、メモリーが再び割り当てられるかわりに前のウィンドウの実装タイプ・インスタンスが渡されるため、前に割り当てた外部メモリーにアクセスして再び初期化できます。ユーザー定義分析関数について外部コンテキストをサポートする手順は、次のとおりです。

  1. ODCIAggregateInitialize() - 渡される実装タイプ・インスタンスがNULLでない場合は、新規の外部メモリーを割り当てるかわりに割当済の外部メモリーを使用し、集計コンテキストを再び初期化します。

  2. ODCIAggregateTerminate() - フラグ引数のビットODCI_AGGREGATE_REUSE_CTXが設定されていない場合にのみ、外部メモリーを解放します。

  3. ODCIAggregateMerge() - マージ済集計コンテキストに関連付けられていた外部メモリーを解放します。

  4. ODCIAggregateTerminate() - 外部メモリーから実装タイプ・インスタンスに集計コンテキストをコピーして、外部メモリーを解放します。

  5. すべてのメンバー・メソッド - 最初に、コンテキストが外部に格納されるかインラインであるかを判別します。コンテキストがインラインの場合は、外部メモリーを割り当てて、そこにコンテキストをコピーします。

関連項目:

12.6 マテリアライズド・ビューとユーザー定義集計の使用

マテリアライズド・ビュー定義に、例12-7で示すようにユーザー定義集計と組込み集計演算子を含めることができます。

マテリアライズド・ビューでクエリー・リライトを使用可能にするには、例12-8に示すようにマテリアライズド・ビュー内のユーザー定義集計をDETERMINISTICとして宣言する必要があります。

ユーザー定義集計が削除または再作成されると、その依存マテリアライズド・ビューすべてに無効マークが付きます。

例12-7 マテリアライズド・ビューの作成

CREATE MATERIALIZED VIEW MyMV AS 
SELECT gcols, MyUDAG(c1) FROM tab GROUP BY (gcols);

例12-8 マテリアライズド・ビューでのクエリー・リライトの使用可能化

CREATE FUNCTION MyUDAG(x NUMBER) RETURN NUMBER
DETERMINISTIC
AGGREGATE USING MyImplType;

CREATE MATERIALIZED VIEW MyMV
ENABLE QUERY REWRITE AS
SELECT gcols, MyUDAG(c1) FROM tab GROUP BY (gcols);

関連項目:

マテリアライズド・ビューの詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。

12.7 ユーザー定義集計関数の作成と使用

例12-9に単純なユーザー定義集計関数SecondMax()を作成して使用する方法を示します。

例12-9 ユーザー定義集計関数の作成と使用

SecondMax()は、一連の数値のうち2番目に大きい値を戻します。

  1. ODCIAggregateルーチンを含むSecondMaxImpl型を実装します。 

    create type SecondMaxImpl as object
(
  max NUMBER, -- highest value seen so far 
  secmax NUMBER, -- second highest value seen so far
  static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) 
    return number,
  member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, 
    value IN number) return number,
  member function ODCIAggregateTerminate(self IN SecondMaxImpl, 
    returnValue OUT number, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, 
    ctx2 IN SecondMaxImpl) return number
);
/

  2. SecondMaxImplの型本体を実装します。 

    create or replace type body SecondMaxImpl is 
static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) 
return number is 
begin
  sctx := SecondMaxImpl(0, 0);
  return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, value IN number) return number is
begin
  if value > self.max then
    self.secmax := self.max;
    self.max := value;
  elsif value > self.secmax then
    self.secmax := value;
  end if;
  return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN SecondMaxImpl, 
    returnValue OUT number, flags IN number) return number is
begin
  returnValue := self.secmax;
  return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, ctx2 IN SecondMaxImpl) return number is
begin
  if ctx2.max > self.max then
    if ctx2.secmax > self.secmax then 
      self.secmax := ctx2.secmax;
    else
      self.secmax := self.max;
    end if;
    self.max := ctx2.max;
  elsif ctx2.max > self.secmax then
    self.secmax := ctx2.max;
  end if;
  return ODCIConst.Success;
end;
end;
/

  3. ユーザー定義集計を作成します。

    CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;

  4. SecondMax()を使用します。 

    SELECT SecondMax(salary), department_id
   FROM MyEmployees
   GROUP BY department_id
   HAVING SecondMax(salary) > 9000;