Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

ListAgg 集計ファンクションを Oacle 10g で実現する。

ListAgg ファンクションは Oralce 11g R2 から利用できる集計関数、分析関数である。
MySQL における group_concat 関数の Oracle 10g 版である。

ListAgg 相当の機能を Oracle 10g、11g R1 で実現するには

ListAgg 相当の機能を実現するには、いくつかの方法がある。

実現方式案

1. データ・カートリッジ機能によるユーザー定義の集計関数を作成する
2. Oracle 11g R1 ならば wmsys.wm_concat を使用する
3. COLLECT 集計関数 とコレクションのアンネストを行なう

(1) については グループ内 order by 機能の実装と使用方法をキレイにできなかったので…やめました
 　  だが、Oracle 9i でも使えるはず。

(2) については 一般公開によって仕様が確約されていないため商用開発では安心して使えない。

COLLECT 集計関数

COLLECT 関数は ListAgg 関数の前段ともいえる、入力された行セットから ネストした表 を生成する集計ファンクションである。
COLLECT 集計関数で取得したネストした表を単一文字列に変換する関数を作るだけでよい。

ネストした表を文字列に変換する関数 LIST_COLLECT

CREATE OR REPLACE TYPE VARCHAR2_ARRAY AS TABLE OF VARCHAR2(32767);
 
CREATE OR REPLACE FUNCTION LIST_COLLECT (
    P_ARRAY VARCHAR2_ARRAY,
    P_DELIMITER IN VARCHAR2 := NULL
)
RETURN VARCHAR2
IS
    vList      VARCHAR2(32767);
    vListCount PLS_INTEGER;
BEGIN
    vListCount := P_ARRAY.COUNT;
    IF (vListCount >= 1) THEN
        vList := P_ARRAY(1);
    END IF;
    --
    FOR i in 2..vListCount LOOP
        vList := vList || P_DELIMITER || P_ARRAY(i);
    END LOOP;
    RETURN vList;
END;
/

LIST_COLLECT 使用例

全員の name を並び順を気にせずに連結する。

SQL> select
  2    LIST_COLLECT(
  3       cast(collect(name) as varchar2_array)
  4    ) concat_name
  5  from listagg_sample;
 
CONCAT_NAME
------------------------------------------------------
すずきたかはしさとうこばやしやまもとあおき

ListAgg を使用した場合には WITHIN GROUP は省略できず暗黙ソートされている。

SQL> select LISTAGG(name) WITHIN GROUP (order by null) concat_name
  2    from listagg_sample;
 
CONCAT_NAME
--------------------------------------------------
あおきこばやしさとうすずきたかはしやまもと

部門毎に名前をカンマ区切りで連結する。名前の並び順は給料の高い順

SQL> select dept,
  2    LIST_COLLECT(
  3      cast(collect(name order by saraly desc nulls last) as varchar2_array)
  4      , ',') csv_name
  5  from listagg_sample
  6  group by dept;
 
DEPT     CSV_NAME
-------- ----------------------------------------
開発部   さとう,こばやし,やまもと,あおき
管理部   たかはし,すずき

同じく ListAgg を使った場合。saraly が同額の場合、名前でソートされているようである。

SQL> select dept, LISTAGG(name, ',') WITHIN GROUP (order by saraly desc nulls last) csv_name
  2   from listagg_sample
  3  group by dept;
 
DEPT     CSV_NAME
-------- -------------------------------
開発部   こばやし,さとう,やまもと,あおき
管理部   たかはし,すずき

数値データの連結処理

数値用の TYPE と文字列への展開関数は用意していないため TO_CHAR を使用する。
ListAgg と同様 NULL のデータは除外される。COLLECT の段階で除外されている。

SQL> select
  2    LIST_COLLECT(
  3       cast(collect(distinct to_char(saraly)) as varchar2_array),
  4       ','
  5    ) csv_distinct_saraly
  6  from listagg_sample;
 
CSV_DISTINCT_SARALY
---------------------------
200000,300000,400000,500000

SQL> select
  2    LIST_COLLECT(
  3       cast(collect(to_char(saraly) order by name) as varchar2_array),
  4       ','
  5    ) csv_saraly1,
  6    LISTAGG(saraly,',') WITHIN GROUP (order by name) csv_saraly2
  7  from listagg_sample;
 
CSV_SARALY1                              CSV_SARALY2
---------------------------------------- -----------------------------------
500000,500000,300000,400000,200000       500000,500000,300000,400000,200000

---