12cR2の新機能LISTAGGオーバーフローコントロールを他のバージョンでも使える単一SQLクエリで書いてみる

# はじめに

LISTAGG自体は11gで追加された集合ファンクションですが、結果文字列がVARCHAR2の最大長である4000バイト[^1]を超過するとエラー[^2]となる欠点がありました。しかし12cR2ではLISTAGGファンクションにオーバーフローオプションが追加され、4000バイトを超過する場合もエラーとならないようそこで集合化を打ち切り、さらに溢れた残り行をカウントして表示することができるようになりました。これを単一SQLステートメントで書き換えて他のバージョンのオラクルでも同様のことをしてみようというのがこの記事の趣旨です。

[^1]: 12cR1からMAX_STRING_SIZEでVARCHAR2の最大長の拡張も可能
[^2]: ORA-01489: result of string concatenation is too long

# 使用するデータ

結果がわかりやすいように以下のデータを用意しました。各行には、0001 から 2000 までの４バイトの文字列が入っており、グループ１には「0001 ～ 1000」、グループ２には「1001 ～ 2000」が割り当てられています。
また、最初に基準を超えるデータが有った場合やNULLに対する挙動をみるためにいくつかデータを加えています。

```sql:データ作成
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.

[^3]: ON OVERFLOW TRUNCATE '...' WITH COUNT と同等

```sql:LISTAGGオーバーフローコントロール例

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値は無視されるので残り行数が変わってきます。

```sql:
...
                   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の挙動**

1. 集合化する文字列の最大長は3970文字とし、超える場合はこれにノーテーションと残り行数を加える。
2. NULL値はスキップする。デリミタ文字も加えない。ただし、残り行数としてはカウントする。
3. NULLを除いたグループ最初の文字列長が閾値を超える場合、ノーテーションと残り行数のみの出力となる。
4. グループすべての文字列がNULLである場合、結果がNULLであるグループを返す。


## 階層

階層クエリは、LISTAGGが存在しないオラクルバージョンで集合文字列をつくるために常用されてきました。ここでは、オーバーフロー時に階層ループを終了するために、インラインビュー内で集合時に必要となる文字列長をランニングSUMで事前に計算しています。

LISTAGGの挙動である「NULLは無視するが残り行としてはカウントはする」を実現するために、インラインビューがすこし煩雑になっています。各グループで「NULLを含むすべての行」および「NULLでない行のみ」、それぞれの行番号と行数を事前計算したのち、「NULLでない行のみ」を階層化します。オーバーフロー時には、「NULLを含むすべての行」の行番号と行数を使うことでNULLを含む残り行数を返しています。もしかしたらもっといい方法があるかもしれません。

これをセレクトリストでスカラークエリとして使用することはできますが、階層クエリの単純条件の適用は階層実行後なので、おそらく毎回全件対象になります。ただし、12cは相関クエリ内での２段飛び参照が可能であり[^4]、インラインビュー内のテーブルに直接相関させることができます。

[^4]: ただしマニュアル未記載

```sql:階層
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]）

[^5]: ちなみにセレクトリストスカラークエリのUNNEST化は、12cからクエリトランスフォーメーションに追加されています

```sql:再帰
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は不要）。状況によってはもっとも高速で適した解となります。

[^6]: ちなみに12gR2で試したところ 2^32-1（約42億）までOKでした。

```sql:モデル（ITERATE）
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」を抽出すれば高速化されます。

```sql:モデル（ITERATE不使用）
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とは少し挙動が異なっています。まぁおまけなので。

```sql:XMLAGG（NULL除去）
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は自由度が高いのでいろいろやってみると面白いとおもいます。

以上。