久しぶりにブログを書いています。暇になったわけではないのですが、モチベーションが若干上がってきたので、いい機会ってことで書いています。
んで、話題はOracleさんのユーザ定義集計関数についてです。集計関数や分析関数はOracleが用意しているものでそれなりに事足りてしまったりもするのですが、稀に使いたい場面も存在していたりします。（たまにだけどねｗ）
大まかな流れ
そんなこんなで稀に使いたいユーザー定義集計関数ですが、作成の大まかな流れは次みたいな感じになります。
- ユーザー定義集計関数インターフェースを実装するTypeとType Bodyを定義（オブジェクト型として定義）
- 1で作成したTypeと紐付けたFunction（集計関数）を定義
と、そんな、難しいものじゃありません。PL/SQL書けるぐらいの人ならお茶の子さいさいです。
ユーザー定義集計関数インターフェース
ユーザー定義集計関数インターフェースで実装する必要がある必須メソッドは次の四つ
- ODCIAggregateInitialize(actx IN OUT )
- ODCIAggregateIterate(self IN OUT , val )
- ODCIAggregateMerge(self IN OUT , ctx2 IN )
- ODCIAggregateTerminate(self IN , ReturnValue OUT , flags IN number)
上から順に、初期化メソッド・実際の集計処理のメソッド・マージのメソッド（パラレル処理した場合の結果のマージ）・終了処理となります。
返却値はすべてNumber型ですが、ODCIConstという定数を管理しているパッケージがありますので、利用しましょう。(SuccessとError以外は基本使いません）
agg_csv集計関数
文字列をカンマ区切りで集計する集計関数を作成します。agg_csvと命名します。C#なんかの命名に慣れていると、大文字小文字やアンスコの使い方が違うのでかなり微妙に思える方もいるかもしれませんが、我慢して下さい。
まずは、オブジェクト型の定義です。
|
-- CSV集計関数 Type定義
CREATE OR REPLACE TYPE t_agg_csv AS OBJECT
(
g_string VARCHAR2(32767),
STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT t_agg_csv)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_agg_csv,
val IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_agg_csv,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_agg_csv,
ctx2 IN t_agg_csv)
RETURN NUMBER
);
/ |
次に、オブジェクト型の実装部分の定義です。
|
-- CSV集計関数 Type Body定義
CREATE OR REPLACE TYPE BODY t_agg_csv IS
STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT t_agg_csv) RETURN NUMBER IS
BEGIN
-- 初期化
actx := t_agg_csv(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_agg_csv,
val IN VARCHAR2 ) RETURN NUMBER IS
BEGIN
-- カンマ区切り
self.g_string := self.g_string || ',' || val;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_agg_csv,
returnValue OUT VARCHAR2,
flags IN NUMBER) RETURN NUMBER IS
BEGIN
-- 最後のカンマはおさらば
returnValue := RTRIM(LTRIM(self.g_string, ','), ',');
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_agg_csv,
ctx2 IN t_agg_csv) RETURN NUMBER IS
BEGIN
-- パラレル実行されても、それぞれを単純に結合
self.g_string := self.g_string || ',' || ctx2.g_string;
RETURN ODCIConst.Success;
END;
END;
/ |
最後に、集計関数の定義です。これを定義しないといくら上のTypeを作成してもダメです。たくみに、パラレル実行可能と定義しています。
|
-- CSV集計関数
CREATE OR REPLACE FUNCTION agg_csv(p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_agg_csv;
/ |
NULLだった場合の判定などは全体的に外してあります。あまり余計なものを付けたくなかっただけなので、実際に使用する場合はその辺を調整して下さい。
使い方
使い方は通常の集計関数なんかと同じです。
|
-- 事業部(department)ごとにEmpIDを集計
select department_id, agg_csv(employee_id)
from hr.EMPLOYEES group by department_id |
参考文献
Oracle Databaseデータ・カートリッジ開発者ガイド - 11 ユーザー定義集計関数
Oracle Databaseデータ・カートリッジ開発者ガイド - 22 ユーザー定義集計関数インタフェース