CREATE AGGREGATE (SQL)

Synopsis

CREATE [OR REPLACE] AGGREGATE name(parameter_list) [ RETURNS datatype ]
   [ INITIALIZE WITH function-name ]
     ITERATE WITH function-name
   [ MERGE WITH function-name ]
   [ FINALIZE WITH function-name ]

説明

CREATE AGGREGATE コマンドは、ユーザ定義の集約関数 (UDAF) を作成します。呼び出されると、このユーザ定義集約関数は、行の値を反復処理し、1 つ以上のユーザ定義関数を呼び出して集約値を計算します。CREATE AGGREGATE を使用すると、標準の InterSystems IRIS SQL 集約関数では提供されない集約演算を提供できます。

CREATE AGGREGATE を呼び出して既に存在する UDAF を作成すると、SQL は SQLCODE -428 エラーを返します。生成される %msg は "ユーザ定義集約関数 SQLUser.MyUDAF は既に存在しています" のようになります。オプションの OR REPLACE キーワード節 (CREATE OR REPLACE AGGREGATE) を指定する場合、既存の UDAF の名前を指定してもエラーは生成されません。既存の UDAF は指定された定義により更新されます。

ユーザ定義の集約関数を削除するには、DROP AGGREGATE コマンドを使用します。

INITIALIZE WITH 節

オプションの INITIALIZE WITH 節は、指定したユーザ定義関数またはクラス・メソッドを呼び出し、初期状態オブジェクトを構成します。状態オブジェクトの値を使用して、中間集約値または最後の計算を実行するために必要なその他の変数を渡します。この節が指定されていない場合、ITERATE WITH 節で指定された関数に初期状態オブジェクトとして NULL オブジェクトが渡されます。

指定されたユーザ定義の function-name は、CREATE AGGREGATE が呼び出されたときに存在している必要があります。存在しない場合、SQLCODE -428 エラーが生成され、%msg により UDAF 関数、節、および存在しない関数名が指定されます。

以下に、初期状態オブジェクトを定義するユーザ定義関数を示します。

CREATE FUNCTION MyAggregateInit() returns varchar language ObjectScript { RETURN "^" }

ユーザ定義集約関数の呼び出し

ユーザ定義集約関数は、標準の集約関数と同じ使用規定に従います。

UDAF は、SELECT リストで呼び出されます。リストされた select-item としてか、サブクエリの select-item 内で使用できます。これは、列のエイリアスを指定できます。列のエイリアスが指定されない場合は、既定で Aggregate_n となります。以下に例を示します。

SELECT Home_State,AVG(Age) AS AvgAge,MAX(Age) AS MaxAge,SecondHighest(Age) AS SecondMaxAge 
FROM Sample.Person GROUP BY Home_State 

UDAF は ORDER BY 節内で直接使用することはできません。これを実行しようとすると、SQLCODE -73 エラーが生成されます。ただし、対応する列エイリアスや select-item のシーケンス番号を指定することにより、ORDER BY 節でユーザ定義集約関数を使用できます。

UDAF は、HAVING 節で直接使用できます。ただし、HAVING 節は、明示的にユーザ定義集約関数を指定する必要があります。対応する select-item 列エイリアスや select-item シーケンス番号を使用して UDAF を指定することはできません。

集約関数は、以下のような箇所で直接使用することはできません。

• WHERE 節。これを実行しようとすると、SQLCODE -19 エラーが生成されます。

• GROUP BY 節。これを実行しようとすると、SQLCODE -19 エラーが生成されます。

• TOP 節。これを実行しようとすると、SQLCODE -1 エラーが生成されます。

• JOIN。ON 節で集約を指定しようとすると、SQLCODE -19 エラーが生成されます。USING 節で集約を指定しようとすると、SQLCODE -1 エラーが生成されます。

標準の集約関数とは異なり、ユーザ定義の集約関数は、DISTINCT、%FOREACH、または %AFTERHAVING 節を指定できません。

並列処理

オプションの MERGE WITH 節が指定されると、その MERGE WITH 関数は 2 つ以上の並列サブクエリの ITERATE WITH 関数から提供される状態オブジェクトをマージし、集約された状態を表す単一のマージ値を返します。MERGE WITH 関数は、並列処理の回数だけ自動的に呼び出されます。これらのマージの結果は、FINALIZE WITH 節に提供されます。

MERGE WITH 関数を宣言すると、状態オブジェクトは暗黙的なシリアル化をサポートすると見なされます (ObjectScript に %SerialObject インタフェースを実装するなど)。

MERGE WITH 関数が提供されない場合、%PARALLEL またはシャーディングが指定されていないと、ユーザ定義集約関数は並列スレッドにより処理されません。これは単一のスレッドとして処理されます。

ユーザ定義集約関数のリスト

INFORMATION.SCHEMA.USERDEFINEDAGGREGATESOpens in a new tab 永続クラスは、現在のネームスペース内のすべてのユーザ定義集約関数に関する情報を表示します。これは、その節で指定されるユーザ定義関数の名前を含む、多くのプロパティを提供します。

以下の例では、現在のネームスペース内のユーザ定義のすべての集約関数のスキーマ名、ユーザ定義集約名、ITERATE 節関数名、および返されるデータ型を返します。

SELECT AGGREGATE_SCHEMA,AGGREGATE_NAME,ITERATE_FUNCTION,RETURN_TYPE 
FROM INFORMATION_SCHEMA.USER_DEFINED_AGGREGATES

RETURNS 節が指定されていなければ、RETURN_TYPE の値は NULL になります。

例

以下の例では、大きな値 (5 以上) はすべて加算し、小さい値 (5 未満) の場合はすべて 5 を減算することにより、ユーザ定義の集約関数を作成します。すべての値はデータ型 NUMERIC(4,1) です。最初に、状態変数 (tot) と入力変数 (num) を指定して、反復関数を作成します。

CREATE FUNCTION Sample.AddSub(tot NUMERIC(4,1),IN num NUMERIC(4,1)) RETURNS NUMERIC(4,1) 
LANGUAGE OBJECTSCRIPT {IF num>=5 {SET tot=tot+num} ELSE {SET tot=tot-5} QUIT tot}

次に、ユーザ定義の集約関数を定義します。

CREATE AGGREGATE Sample.SumAddSub(arg NUMERIC(4,1)) 
   ITERATE WITH Sample.AddSub

以下のように、Score フィールドでユーザ定義集約関数を呼び出します。

SELECT TestSubject,Score,SUM(Score) AS ScoreSum,Sample.SumAddSub(Score) AS ScoreAddHighSubtractLow

負の値を回避するには、FINALIZE WITH 関数を追加します。

CREATE FUNCTION Sample.NoNeg(tot NUMERIC(4,1)) RETURNS NUMERIC(4,1) 
LANGUAGE OBJECTSCRIPT {IF num>0 {QUIT tot} ELSE {SET tot=0 QUIT tot}}

CREATE OR REPLACE AGGREGATE Sample.SumAddSub(arg NUMERIC(4,1))
   ITERATE WITH Sample.AddSub
   FINALIZE WITH Sample.NoNeg

関連項目

• CREATE FUNCTION コマンド

• DROP AGGREGATE コマンド

• 集約関数の概要

• "InterSystems IRIS エラー・リファレンス" にリストされた SQLCODE エラー・メッセージ