はじめに
LISTAGG自体は11gで追加された集合ファンクションですが、結果文字列がVARCHAR2の最大長である4000バイト1を超過するとエラー2となる欠点がありました。しかし12cR2ではLISTAGGファンクションにオーバーフローオプションが追加され、4000バイトを超過する場合もエラーとならないようそこで集合化を打ち切り、さらに溢れた残り行をカウントして表示することができるようになりました。これを単一SQLステートメントで書き換えて他のバージョンのオラクルでも同様のことをしてみようというのがこの記事の趣旨です。
使用するデータ
結果がわかりやすいように以下のデータを用意しました。各行には、0001 から 2000 までの４バイトの文字列が入っており、グループ１には「0001 ～ 1000」、グループ２には「1001 ～ 2000」が割り当てられています。
また、最初に基準を超えるデータが有った場合やNULLに対する挙動をみるためにいくつかデータを加えています。
CREATE TABLE test_t2000 AS
SELECT DECODE(SIGN(LEVEL - 1000), 1, 2, 1) grp,
CAST(LPAD(LEVEL, 4, '0') as VARCHAR2(4000)) s
FROM dual
CONNECT BY LEVEL <= 2000;
SELECT grp, MIN(s), MAX(s) FROM test_t2000 GROUP BY grp;
GRP MIN(S) MAX(S)
---------- ---------------- ----------------
1 0001 1000
2 1001 2000
INSERT INTO test_t2000 VALUES (2, NULL);
INSERT INTO test_t2000 VALUES (3, RPAD ('X', 4000, 'X'));
INSERT INTO test_t2000 VALUES (3, NULL);
INSERT INTO test_t2000 VALUES (4, NULL);
INSERT INTO test_t2000 VALUES (4, 'A');
INSERT INTO test_t2000 VALUES (5, NULL);
INSERT INTO test_t2000 VALUES (5, NULL);
COMMIT;
LISTAGGオーバーフローコントロール例
実際にLISTAGGオーバーフローコントロールを使ってみます。ここでは確認のため最初と最後の30文字ずつのみ表示しています。ON OVERFLOW TRUNCATEのデフォルトでは、ノーテーションが "..." で残り行数表示有効となります3。以下の例で、グループ１は 0001 ～ 0794 まで集合化できたため、1000行あるうちの残り206行はカットされたということになります。ノーテーションを除いた最大文字長は、指定されたノーテーションの文字数で変化しますが、デフォルトでは3970文字あたりの様なのでこれを基準として用います。
注意点としては、LISTAGGはNULL値を単純に無視することと、逆にNULLであっても残り行数としてはカウントすることですね。これ、頭からループして取捨選択するプログラムでは普通の動きですが、SQLで記述する場合、事前にNULLの除去ができないのでやっかいです。
The measure_expr can be any expression. Null values in the measure column are ignored.
SELECT grp,
SUBSTR(v, 1, 30) first,
SUBSTR(v, -30, 30) last
FROM (
SELECT grp,
listagg(s, '-' on overflow TRUNCATE) within GROUP (ORDER BY s) v
FROM test_t2000
GROUP BY grp
);
GRP FIRST LAST
---------- ------------------------------ ------------------------------
1 0001-0002-0003-0004-0005-0006- 0-0791-0792-0793-0794-...(206)
2 1001-1002-1003-1004-1005-1006- 0-1791-1792-1793-1794-...(207)
3 -...(2)
4 A
5
NULLを先に処理した場合、NULL値は無視されるので残り行数が変わってきます。
...
listagg(s, '-' on overflow TRUNCATE) within GROUP (ORDER BY s NULLS FIRST) v
...
GRP FIRST LAST
---------- ------------------------------ ------------------------------
1 0001-0002-0003-0004-0005-0006- 0-0791-0792-0793-0794-...(206)
2 1001-1002-1003-1004-1005-1006- 0-1791-1792-1793-1794-...(206) <--これ
3 -...(1) <--これも
4 A
5
単一SQLクエリへの書き換え
上記の動作を単一SQLステートメントで模倣してみます。おそらくまず思いつくのが昔からよくつかわれている階層クエリでしょう。他に再帰やモデルでも記述してみます。また、それぞれのクエリがセレクトリストでの相関スカラーサブクエリとして適宜処理に対応出来るかどうかも考察していきます。
- 階層
- 再帰
- モデル
目標とするLISTAGGの挙動
- 集合化する文字列の最大長は3970文字とし、超える場合はこれにノーテーションと残り行数を加える。
- NULL値はスキップする。デリミタ文字も加えない。ただし、残り行数としてはカウントする。
- NULLを除いたグループ最初の文字列長が閾値を超える場合、ノーテーションと残り行数のみの出力となる。
- グループすべての文字列がNULLである場合、結果がNULLであるグループを返す。
階層
階層クエリは、LISTAGGが存在しないオラクルバージョンで集合文字列をつくるために常用されてきました。ここでは、オーバーフロー時に階層ループを終了するために、インラインビュー内で集合時に必要となる文字列長をランニングSUMで事前に計算しています。
LISTAGGの挙動である「NULLは無視するが残り行としてはカウントはする」を実現するために、インラインビューがすこし煩雑になっています。各グループで「NULLを含むすべての行」および「NULLでない行のみ」、それぞれの行番号と行数を事前計算したのち、「NULLでない行のみ」を階層化します。オーバーフロー時には、「NULLを含むすべての行」の行番号と行数を使うことでNULLを含む残り行数を返しています。もしかしたらもっといい方法があるかもしれません。
これをセレクトリストでスカラークエリとして使用することはできますが、階層クエリの単純条件の適用は階層実行後なので、おそらく毎回全件対象になります。ただし、12cは相関クエリ内での２段飛び参照が可能であり4、インラインビュー内のテーブルに直接相関させることができます。
SELECT grp,
CASE
WHEN rc = 0 THEN NULL -- all nulls
WHEN len > 3970 THEN '-...(' || to_char(fc - fn + 1) || ')' -- overflow at the first row
ELSE SUBSTR (SYS_CONNECT_BY_PATH (s, '-'), 2)
|| DECODE (rn, rc, NULL, '-...(' || TO_CHAR (fc - fn) || ')')
END v
FROM (SELECT grp,
SUBSTR (s, 1, 3999) s, -- To avoid an error on SYS_CONNECT_BY_PATH
SUM (LENGTH (s) + SIGN (LENGTH (s))) OVER (PARTITION BY grp ORDER BY s) len,
-- full row numbering and count
ROW_NUMBER () OVER (PARTITION BY grp ORDER BY s) fn,
COUNT (*) OVER (PARTITION BY grp) fc,
-- non-null row numbering and count
NVL2 (s, COUNT (s) over (PARTITION BY grp ORDER BY s), NULL) rn,
COUNT (s) OVER (PARTITION BY grp) rc
FROM test_t2000)
WHERE CONNECT_BY_ISLEAF = 1
START WITH rn = 1 OR (rc = 0 AND fn = 1)
CONNECT BY PRIOR rn + 1 = rn AND PRIOR grp = grp AND len <= 3970;
再帰
再帰 WITHを用いた例です。階層と同様に必要となる文字列長を事前に計算しています。またグループ最初の文字列の基準値超えに対処するため、NULLからスタートしています。
再帰はこの例題の解としてもっとも適した方法でしょう。ただ、再帰によるファクタリングは階層と同様に相関による適宜処理と相性がよくありません。まぁ、ファクタリングするなら対象行のみ処理してそのまま結合すれば良いので問題は少ないと思います。（相関サブクエリがNESTでもUNNESTでも遅い場合は、メインクエリを先にファクタリングしてサブクエリへの入力行を抽出した後、サブクエリをバルク処理するのはSQLチューニング手法の一つですね5）
WITH m
AS (SELECT grp, s,
ROW_NUMBER () over (PARTITION BY grp ORDER BY s) rn,
SUM(LENGTH(s) + SIGN(LENGTH(s)))
over (PARTITION BY grp ORDER BY s) len,
COUNT(*) over (PARTITION BY grp) c
FROM test_t2000),
recur (grp, n, v, flg)
AS (SELECT grp, 0, CAST(NULL AS VARCHAR2(4000)), CAST(NULL AS NUMBER) FROM m WHERE rn = 1
UNION ALL
SELECT r.grp, r.n + 1,
r.v
|| CASE
WHEN s IS NULL THEN NULL
WHEN m.len > 3970 THEN '-...(' || TO_CHAR(c - r.n) || ')'
ELSE nvl2(v, '-', null) || s
END,
CASE
WHEN len > 3970 OR c = rn THEN rn END
FROM recur r, m
WHERE r.grp = m.grp AND r.n + 1 = rn AND r.flg IS NULL
)
SELECT grp, v FROM recur WHERE n = flg;
MODEL
モデルを用いた場合、ルールをループさせるITERATE句を利用するとシンプルに記述することができます。ただし、ITERATEは最大ループ回数を実値で指定しなければならないことに留意する必要があります。LISTAGGの挙動にならえば、事前にNULLを除去することができず、対象グループの最大ループ回数は事前に確定しません。といってもITERATEは相当大きな数6を受け付けるので、実用的には問題にならないでしょう。
このSQLは、ほぼそのままスカラーサブクエリにしてSELECTリストで使用することができます（PARTITION BYは不要）。状況によってはもっとも高速で適した解となります。
SELECT grp, v || ov v
FROM test_t2000
MODEL
RETURN UPDATED ROWS
PARTITION BY (grp)
DIMENSION BY (ROW_NUMBER () OVER (PARTITION BY grp ORDER BY s) rn)
MEASURES (CAST(s AS VARCHAR2(4000)) v, CAST('-' AS VARCHAR2(20)) ov)
RULES
ITERATE (10000) UNTIL (ov[0] IS NOT NULL OR ov[ITERATION_NUMBER + 2] IS NULL)
(
ov [0] = CASE
WHEN NVL(LENGTH(v[0]), 0) + NVL(LENGTH (v[ITERATION_NUMBER + 1]), 0) + 1 > 3970
THEN '-...(' || TO_CHAR(COUNT(*) [ rn > 0 ] - ITERATION_NUMBER) || ')'
END,
v [0] = CASE
WHEN v[ITERATION_NUMBER + 1] is NULL THEN v[0]
WHEN ov[0] IS NOT NULL THEN v[0] -- at the overflow
ELSE v[0] || nvl2(v[0], '-', null) || v[ITERATION_NUMBER + 1]
END
);
モデルは、ITERATEを使用しないでも記述することもできます。これにより最大ループ回数の制限から逃れることができますが、探索を途中で打ち切ることができません。また、ここではスカラークエリとして利用できるようインラインビューを使わずにグループごとの全行に同一の結果集合文字列を代入した後DISTINCTしているため、ITERATEに比べパフォーマンス的に多少不利となっています。
相関にしない場合は、最後の「v [ANY] = v[1]」を削除し、インラインビューの外側で「rn = 1」を抽出すれば高速化されます。
SELECT DISTINCT grp, v
FROM test_t2000
MODEL
PARTITION BY (grp)
DIMENSION BY (ROW_NUMBER () OVER (PARTITION BY grp ORDER BY s) rn)
MEASURES (s, CAST(NULL AS VARCHAR2(4000)) v, CAST(NULL AS VARCHAR2(30)) ov,
SUM(LENGTH(s) + SIGN(LENGTH(s))) over (PARTITION BY grp ORDER BY s) len)
RULES UPDATE
(
v [ANY] ORDER BY rn = CASE
WHEN len[CV()] > 3970 THEN NULL
WHEN s[CV()] IS NULL THEN v[CV() - 1]
ELSE v[CV() - 1] || nvl2(v[CV() - 1], '-', null) || s[CV()]
END,
ov [ANY] ORDER BY rn = CASE
WHEN ov[CV() - 1] IS NOT NULL
THEN ov[CV() - 1]
WHEN len[CV()] > 3970
THEN '-...(' || TO_CHAR(COUNT(*)[ANY] - cv(rn) + 1) || ')'
ELSE NULL
END,
v [1] = max(v)[ANY] || max(ov)[ANY], v [ANY] = v[1]
);
おまけ(XML)
オーバーフローしないように文字列を作り上げていくのではなく、LOBをつかって4000文字超過の集合文字列を一度作った後、VARCHAR2に切り詰めて落とすという逆説的な方法もなきにしもあらずです。
以下では、XMLAGGでLOBを作った後SUBSTRで切り落としています。デリミタ文字のカウントを持って集合化したアイテム数としているため、データで使われないことが確定している文字が必要となります。ここでは事前にNULLを除去しており結果LISTAGGとは少し挙動が異なっています。まぁおまけなので。
SELECT grp,
CASE WHEN sc = 0
THEN '-...(' || vc || ')'
ELSE REGEXP_REPLACE (s, '-[^-]*$', '') || DECODE (vc, sc, NULL, '-...(' || TO_CHAR (vc - sc) || ')')
END v
FROM (SELECT grp,
REGEXP_COUNT (v, '-') vc,
REGEXP_COUNT (SUBSTR (v, 1, 3970), '-') sc,
CAST (SUBSTR (v, 1, 3970) AS VARCHAR2 (4000)) s
FROM (SELECT grp, XMLAGG (XMLELEMENT (item, SUBSTR (s, 1, 3999) || '-') ORDER BY s).EXTRACT ('//text()').getclobval () v
FROM test_t2000
WHERE s IS NOT NULL
GROUP BY grp));
おわりに
LISTAGGオーバーフローコントロールの置き換えクエリを作ってみましたが、クエリに手を加えることでLISTAGGにない機能を付加することができます。例えば、集合文字列内での同一アイテムの除去(DISTINCT)、アイテムごとのナンバリング付加(1-AAA,2-BBB,..)、VARCHAR2を２つ使って8000文字までの対応なんかですね。特にMODELは自由度が高いのでいろいろやってみると面白いとおもいます。
以上。