LoginSignup
5
4

More than 5 years have passed since last update.

@tlokweng

ユーザ定義「集計」関数でDISTINCTや分析関数のウインドウが使えるLISTAGGを作る

Posted at

はじめに

LISTAGGは非常に便利な集計関数ですが、引数内でDISTINCTが使えないという欠点があります（19cでは使用可能になるようです。参考：LISTAGG関数の新機能を試す）。またLISTAGGは分析関数としても使えますが、WITHIN GROUPという特殊な句が存在するためOVER句内でORDER BYが使えず結果ウインドウ指定ができないとういう分析関数としては中途半端な機能になっています。そこでここでは、ユーザ定義集計関数という機能をつかって新たな集計・分析関数LISTAGG2を作りそれらに対応してみます。

ちなみに「ユーザ定義集合関数」はGROUP-BYで使用される集合関数やOVER句の使える分析関数が簡単に自作できる非常に古くから存在している素晴らしいい機能です。しかし、実際のところこれで実現できるものは既存のSQLをこねくり回せばだいたい置き換えの効くものなのであまり利用はされていないのが現状でしょう。

ユーザ定義集計関数の定義

マニュアルは以下のとおりです。たぶん9iから18cまで変わってないんじゃないかと思います。

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

要点をまとめると2つ。

  1. 以下の4つのメンバ関数をもつオブジェクト型のタイプを定義する
CREATE TYPE <タイプ名> IS OBJECT (
    STATIC FUNCTION ODCIAggregateInitialize  -- 初期化の処理
    MEMBER FUNCTION ODCIAggregateIterate     -- 集合化での繰り返し時の処理
    MEMBER FUNCTION ODCIAggregateMerge       -- タイプ変数の結合時の処理
    MEMBER FUNCTION ODCIAggregateTerminate   -- 集合化結果の作成
);
  1. 上記タイプを特殊な形で呼び出す関数を定義する
CREATE FUNCTION <関数名>
    RETURN <返り値の型>
    AGGREGATE USING <上記タイプ名>;

基本はこれだけですね。意外と簡単。

LISTAGG2の作成

では早速LISTAGG2のベースとなるタイプオブジェクトLISTAGG2_TYPを作ります。

まず定義部分。連結文字列をためていくVARCHAR2の変数をひとつだけ持っています。それぞれのメンバ関数の引数は固定ですが、定義によって型は変わります。

タイプの定義
CREATE OR REPLACE TYPE listagg2_typ IS OBJECT
(
s VARCHAR2(4000),

STATIC FUNCTION ODCIAggregateInitialize (init IN OUT listagg2_typ) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate    (SELF IN OUT listagg2_typ, value IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate  (SELF IN     listagg2_typ, result OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge      (SELF IN OUT listagg2_typ, another IN listagg2_typ) RETURN NUMBER
);
/

そしてそのBODYです。4つのメンバ関数の処理を定義します。

タイプBODYの定義
CREATE OR REPLACE TYPE BODY listagg2_typ
IS

-- 初期化の処理（一番最初に実行される）
STATIC FUNCTION ODCIAggregateInitialize (init IN OUT listagg2_typ) 
RETURN NUMBER IS
BEGIN
    init := listagg2_typ(''); -- タイプを初期化して返す
    return ODCIConst.Success;
END;

-- 集合化での繰り返し時の処理（新しい値と共に呼び出される）
MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT listagg2_typ, value IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
    SELF.s := NULLIF(SELF.s || ',', ',') || value; -- カンマ区切りで文字列を追加していく
    RETURN ODCIConst.Success;
END;

-- タイプの結合時の処理(多分パラレル後の結合とかで使われる)
MEMBER FUNCTION ODCIAggregateMerge (SELF IN OUT listagg2_typ, another IN listagg2_typ) RETURN NUMBER
IS
BEGIN
    SELF.s := NULLIF(SELF.s || ',', ',') || another.s; -- 2つのタイプの結合
    RETURN ODCIConst.Success;
END;

-- 集合化結果の作成（最後に呼び出される）
MEMBER FUNCTION ODCIAggregateTerminate (SELF IN listagg2_typ, result OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER
IS
BEGIN
    result := SELF.s;  -- カンマ区切り文字列は既に完成しているので値を返すだけ
    RETURN ODCIConst.Success;
END;

END;
/

最後にタイプを呼び出して連結文字列を返す関数LISTAGG2を作ります。CURRENT_USERはパフォーマンス向上のため、PARALLEL_ENABLEは関数がパラレルクエリで使われることを想定して入れています。

関数の定義
CREATE OR REPLACE FUNCTION listagg2 (s VARCHAR2)
    RETURN VARCHAR2
    AUTHID CURRENT_USER
    PARALLEL_ENABLE
    AGGREGATE USING listagg2_typ;
/

以上で完成です。

LISTAGG2を使う

LISTAGG2の動作を確認するためにテストテーブルを作ります。

テストテーブルの作成
CREATE TABLE agg_test (grp NUMBER, item VARCHAR2(10));
INSERT INTO agg_test VALUES (1, 'apple');
INSERT INTO agg_test VALUES (2, 'apple');
INSERT INTO agg_test VALUES (2, 'orange');
INSERT INTO agg_test VALUES (3, 'orange');
INSERT INTO agg_test VALUES (3, 'orange');
INSERT INTO agg_test VALUES (3, 'kiwi');
INSERT INTO agg_test VALUES (4, 'kiwi');
COMMIT;

SELECT * FROM agg_test;

       GRP ITEM
---------- ----------
         1 apple
         2 apple
         2 orange
         3 orange
         3 orange
         3 kiwi
         4 kiwi

集計関数のテスト

まずは、単純な集計から。

集計関数として使用
SELECT grp, listagg2(item) list
FROM   agg_test
GROUP BY grp;

       GRP LIST
---------- --------------------------------------------------
         1 apple
         2 apple,orange
         3 orange,kiwi,orange
         4 kiwi

グループ3でorangeが重複しているのでDISTINCTを併用して重複を除去します。

DISTINCTの併用
SELECT grp, listagg2(DISTINCT item) list
FROM   agg_test
GROUP BY grp;

       GRP LIST
---------- --------------------------------------------------
         1 apple
         2 apple,orange
         3 kiwi,orange
         4 kiwi

うまく行ってますね。

分析関数のテスト

OVER句内でRANGEを使ってウインドウ指定をテストします。RANGE 1 PRECEDINGなので、現在行のGRPの一つ前の値をもつ行のITEMがちゃんと含まれていますね。

分析関数としての使用
SELECT grp, item, 
       listagg2(item) OVER (ORDER BY grp RANGE 1 PRECEDING) list
FROM   agg_test;

       GRP ITEM       LIST
---------- ---------- --------------------------------------------------
         1 apple      apple
         2 apple      apple,apple,orange
         2 orange     apple,apple,orange
         3 orange     apple,orange,orange,orange,kiwi
         3 orange     apple,orange,orange,orange,kiwi
         3 kiwi       apple,orange,orange,orange,kiwi
         4 kiwi       orange,orange,kiwi,kiwi

そして、DISTINCT。分析関数でもDISTINCTは可能ですが、ORDER BYやそれに続くウインドウ指定をした場合はエラーになります。

分析関数でDISTINCT
SELECT listagg2(DISTINCT item) OVER (PARTITION BY grp) list
FROM   agg_test;

LIST
--------------------------------------------
apple
apple,orange
apple,orange
kiwi,orange
kiwi,orange
kiwi,orange
kiwi

SELECT listagg2(DISTINCT item) OVER (ORDER BY grp) list
FROM   agg_test;

ORA-30487: ORDER BY not allowed here

これはこの関数に限ったことではなく分析関数の文法なのでしょうがないのですが、LISTAGG2は連結文字列を作るという性質上やはりどのような場合でもDISTINCTが欲しくなります。
もうひとついえば、集計関数として使った場合や分析関数でDISTINCTを使った場合にORDER BYが使えないため連結文字列内のアイテムの順番を指定することができません。これもなんとかしたいところです。

LISTAGG3の作成

ということでLISTAGG2では以下が問題となりました。

  1. 分析関数のウインドウ指定と共にはDISTINCTが使えない
  2. 集計関数としてや分析関数のウインドウ指定では連結文字列内のアイテムの順番が指定できない。

これらを改良したいところですが、オラクルの提供するユーザ定義集計関数のインターフェイスを使用している限りクエリ側からコントロールするスベがありません。したがってここでは、常にDISTINCTとソートを行う新しいを関数LISTAGG3を作成して使い分けることにします。

LISTAGG3では、内部でソートを行わなければならないため連結文字列ではなくコレクションでデータを保持します。ここではODCI絡みの文字列コレクションタイプsys.ODCIVARCHAR2LISTを流用しています。

LISTAGG3の作成
CREATE OR REPLACE TYPE listagg3_typ IS OBJECT
(
s sys.ODCIVARCHAR2LIST, -- VARCHAR2のVARRAYコレクション

STATIC FUNCTION ODCIAggregateInitialize (init IN OUT listagg3_typ) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate    (SELF IN OUT listagg3_typ, value IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate  (SELF IN     listagg3_typ, result OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge      (SELF IN OUT listagg3_typ, another IN listagg3_typ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY listagg3_typ
IS

STATIC FUNCTION ODCIAggregateInitialize (init IN OUT listagg3_typ)
RETURN NUMBER IS
BEGIN
    init := listagg3_typ(sys.ODCIVARCHAR2LIST('')); -- コレクションの初期化
    return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate (SELF IN OUT listagg3_typ, value IN VARCHAR2)
RETURN NUMBER IS
BEGIN
    SELF.s.extend();               -- コレクションの拡張
    SELF.s(SELF.s.COUNT) := value; -- コレクションへ追加
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge (SELF IN OUT listagg3_typ, another IN listagg3_typ) RETURN NUMBER
IS
BEGIN
    -- 連結。VARRRYではMULTISET UNIONが使えないのでTABLEしてUNION
    SELECT CAST(COLLECT(column_value) AS sys.ODCIVARCHAR2LIST) INTO SELF.s
    FROM  (SELECT column_value FROM TABLE(SELF.s)
           UNION ALL
           SELECT column_value FROM TABLE(another.s));
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate (SELF IN listagg3_typ, result OUT VARCHAR2, flags IN NUMBER)
RETURN NUMBER IS
BEGIN
    -- 重複を除去してLISTAGGで連結文字列を作成
    SELECT LISTAGG(column_value, ',') WITHIN GROUP (ORDER BY column_value)
    INTO   result
    FROM  (SELECT DISTINCT column_value FROM TABLE(SELF.s));
    RETURN ODCIConst.Success;
END;

END;
/

CREATE OR REPLACE FUNCTION listagg3 (s VARCHAR2)
    RETURN VARCHAR2
    AUTHID CURRENT_USER
    PARALLEL_ENABLE
    AGGREGATE USING listagg3_typ;
/

以下の通り、重複の除去とソートが行われています。

LISTAGG3の使用
SELECT grp, item, 
       listagg3(item) OVER (ORDER BY grp RANGE 1 PRECEDING) list
FROM   agg_test;

       GRP ITEM       LIST
---------- ---------- --------------------------------------------------
         1 apple      apple
         2 apple      apple,orange
         2 orange     apple,orange
         3 orange     apple,kiwi,orange
         3 orange     apple,kiwi,orange
         3 kiwi       apple,kiwi,orange
         4 kiwi       kiwi,orange

おわりに

いまさらながらのユーザ定義集計関数ですが、使い方によってはまだまだ役に立つのかもしれません。

ちなみに、ユーザ定義集計関数を使わずにSQLだけでオーバーフロー制御付きのLISTAGGを実現する方法は、「12cR2の新機能LISTAGGオーバーフローコントロールを他のバージョンでも使える単一SQLクエリで書いてみる」を参考にしてください。

以上です。

5
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
4