Hitachi

Hitachi Advanced Data Binder SQLリファレンス

7.22.14　LISTAGG

順序付けされた一連の値を連結し，値と値の間に区切り文字列を挿入した文字列を求めます。

メモ

「7.22.14　LISTAGG」の説明では，LISTAGG集合関数をLISTAGGと表記します。

〈この項の構成〉

(1)　指定形式

LISTAGG集合関数：：＝LISTAGG (〔｛ALL｜DISTINCT｝〕 値式
                              〔,LISTAGG区切り文字列〕
                              〔,LISTAGG結果の最大長〕
                              〔ON OVERFLOW 結果溢れ動作〕
                             ) WITHINグループ指定
 
  結果溢れ動作：：＝｛ERROR｜TRUNCATE 〔切り捨て末尾文字列〕 WITHOUT COUNT｝
 
  WITHINグループ指定：：＝WITHIN GROUP(ORDER BY ソート指定リスト)

ページの先頭へ

(2)　指定形式の説明

●〔｛ALL｜DISTINCT｝〕 値式
｛ALL｜DISTINCT｝：

値式から導出された値に重複した値がある場合，重複した値を排除するかどうかを指定します。

ALL：重複した値がある場合でも，重複した値を排除しません。

DISTINCT：重複した値がある場合，重複した値を排除して1つの値にします。

ALLおよびDISTINCTの指定を省略した場合，ALLが仮定されます。

値式：

LISTAGGの集計対象を求める値式を指定します。値式については，「7.20　値式」を参照してください。

値式には，数データ（INTEGER型，SMALLINT型，DECIMAL型，NUMERIC型，DOUBLE PRECISION型，FLOAT型），または文字データ（CHAR型，VARCHAR型）を指定してください。数データおよび文字データについては，「6.2.1　データ型の種類」を参照してください。

なお，値式から導出された値を，LISTAGGの集計値といいます。

メモ

指定した値式が数データの場合，値式から導出された数データの値は文字データの値に変換されます。その際のデータ変換規則は，スカラ関数CONVERTによって数データを文字データに変換する際の規則（数値書式の指定なしの場合）に従います。スカラ関数CONVERTによる数データを文字データに変換する際の規則については，「8.13.5　CONVERT」の「(5)　規則」の「(c)　文字データに変換する場合の規則」を参照してください。

●LISTAGG区切り文字列

LISTAGGの集計値と集計値の間に挿入する区切り文字列を，文字列定数の形式で指定します。文字列定数については，「6.3　定数」を参照してください。

LISTAGG区切り文字列の指定と，LISTAGGの結果の例を次に示します。例で使用されているBob，Mike，Nancy，Stephanie，Tomは，LISTAGGの集計値です。

（例）

  • LISTAGG区切り文字列に'|'を指定した場合

    LISTAGGの結果は次のようになります。

    Bob|Mike|Nancy|Stephanie|Tom

  • LISTAGG区切り文字列に','を指定した場合

    LISTAGGの結果は次のようになります。

    Bob,Mike,Nancy,Stephanie,Tom

  • LISTAGG区切り文字列に':::'を指定した場合

    LISTAGGの結果は次のようになります。

    Bob:::Mike:::Nancy:::Stephanie:::Tom

指定規則を次に示します。

  • LISTAGG区切り文字列の指定を省略した場合，LISTAGG区切り文字列には実長0バイトの文字データが仮定されます。LISTAGG区切り文字列の指定を省略した場合の，LISTAGGの結果の例を次に示します。

    （例）

    BobMikeNancyStephanieTom

  • LISTAGG区切り文字列の長さは，LISTAGG結果の最大長の指定値（バイト）以下にしてください。

●LISTAGG結果の最大長

LISTAGGの結果の最大長（VARCHAR型の定義長）をバイト数で指定します。

（例）
SELECT LISTAGG("C1",'|',20) WITHIN GROUP (ORDER BY "C1") FROM "T1"

下線部分が，LISTAGG結果の最大長の指定です。この場合，LISTAGG結果の最大長が20バイト（VARCHAR(20)）になります。

指定規則を次に示します。

  • LISTAGG結果の最大長には，3～32,000の符号なし整数定数を指定してください。

  • LISTAGG結果の最大長の指定を省略した場合，LISTAGG結果の最大長には1,024バイト（VARCHAR(1024)）が仮定されます。

●ON OVERFLOW 結果溢れ動作
結果溢れ動作：：＝｛ERROR｜TRUNCATE〔切り捨て末尾文字列〕 WITHOUT COUNT｝

結果溢れ動作には，連結データのデータ長が，LISTAGG結果の最大長を超えた場合の動作を指定します。

メモ

連結データとは，LISTAGGによって集計されて連結された文字データ（LISTAGG区切り文字列を含む）のことです。

ERROR：

連結データのデータ長がLISTAGG結果の最大長を超えた場合，SQL文をエラーにします。SQL文がエラーになる例を次に示します。

（例）

  • 連結データ：'Bob|Mike|Nancy|Stephanie|Tom'　　←28バイト

  • LISTAGG結果の最大長の指定：15バイト

上記の場合，連結データのデータ長（28バイト）が，LISTAGG結果の最大長（15バイト）を超えるため，SQL文がエラーになります。

TRUNCATE〔切り捨て末尾文字列〕 WITHOUT COUNT：

連結データのデータ長がLISTAGG結果の最大長を超えた場合でも，SQL文をエラーにしません。連結データのデータ長が，LISTAGG結果の最大長を超えた場合，LISTAGGの結果には，連結データの一部と切り捨て末尾文字列が出力されます。例を次に示します。

（例）

  • 連結データ：'Bob|Mike|Nancy|Stephanie|Tom'　　←28バイト

  • LISTAGG結果の最大長の指定：15バイト

  • LISTAGGの結果：Bob|Mike|...　　←12バイト

    [図データ]

    ［説明］

    LISTAGG結果の最大長（15バイト）に収まるようにLISTAGGの結果が出力されます。

    なお，切り捨て末尾文字列は，'...'から任意の文字列に変更できます。変更する場合は，切り捨て末尾文字列を指定してください。

切り捨て末尾文字列：

連結データのデータ長がLISTAGG結果の最大長を超えた場合に，LISTAGGの結果の末尾に出力する文字列を指定します。切り捨て末尾文字列は文字列定数の形式で指定します。文字列定数については，「6.3　定数」を参照してください。

切り捨て末尾文字列の指定を省略した場合，'...'（ピリオド3つの実長3バイトの文字データ）が切り捨て末尾文字列に仮定されます。

切り捨て末尾文字列の長さは，LISTAGG結果の最大長の指定値（バイト）以下にしてください。

メモ

このオプションの指定を，LISTAGG結果溢れ動作指定といいます。

●WITHINグループ指定
WITHINグループ指定：：＝WITHIN GROUP(ORDER BY ソート指定リスト)

WITHINグループ指定には，LISTAGGの集計値の連結順序（昇順，降順）を指定します。ソート指定リストについては，「7.24　ソート指定リスト」を参照してください。

指定規則を次に示します。

  • WITHINグループ指定のソート指定リストには，ナル値ソート順指定は指定できません。

  • WITHINグループ指定のソート指定リストには，ソート指定を2つ以上指定できません。

ページの先頭へ

(3)　規則

  1. 問合せ指定中にLISTAGGを64個まで指定できます。

  2. LISTAGGの引数に指定した値式のデータ型によって，LISTAGGの結果のデータ型が次の表のとおりに決まります。

    表7‒28　LISTAGGの引数に指定した値式のデータ型とLISTAGGの結果のデータ型の関係

    値式のデータ型

    LISTAGGの結果のデータ型

    数データ

    INTEGER

    • LISTAGG結果の最大長（指定値をtとする）を指定した場合

      VARCHAR(t)

    • LISTAGG結果の最大長の指定を省略した場合

      VARCHAR(1024)

    SMALLINT

    DECIMAL

    NUMERIC

    DOUBLE PRECISION

    FLOAT

    文字データ

    CHAR

    VARCHAR

  3. LISTAGGの引数の値式から導出された値（LISTAGGの集計値）に対して次の処理を順に実行した結果が，LISTAGGの入力行になります。

    • 集計値にナル値がある場合，ナル値を排除する。

    • DISTINCTが指定されている場合，重複している集計値を排除して1つの値にする。

    • WITHINグループ指定のソート指定に従って，集計値のソート処理を行う。

  4. LISTAGGの入力行数が0の場合，実行結果はナル値になります。

  5. LISTAGGの実行結果を次に示します。

    例の説明では，連結データが'Bob|Mike|Nancy|Stephanie|Tom'（28バイト）であるとします。

    ■連結データのデータ長≦LISTAGG結果の最大長の場合

    LISTAGGの結果には，連結データがすべて出力されます。

    （例）

    Bob|Mike|Nancy|Stephanie|Tom

    ■連結データのデータ長＞LISTAGG結果の最大長の場合

    • 結果溢れ動作にERROR（省略値）を指定した場合

      実行したSQL文がエラーになります。

    • 結果溢れ動作にTRUNCATEを指定した場合

      ＜実行結果1＞

      LISTAGGの結果には，連結データの一部と切り捨て末尾文字列が出力されます。

      （例）

      SQL文の例

      SELECT LISTAGG("C1",'|',24 ON OVERFLOW TRUNCATE '/*Overflow*/' WITHOUT COUNT)
         WITHIN GROUP (ORDER BY "C1") FROM "T1"

      上記のSQL文では，LISTAGG結果の最大長に24バイトを指定し，切り捨て末尾文字列に'/*Overflow*/'を指定しています。

      LISTAGGの結果

      [図データ]

      ［説明］

      • 連結データの一部と切り捨て末尾文字列が，LISTAGG結果の最大長（24バイト）に収まるように出力されます。

      • 出力される連結データの一部の最後の文字列は，LISTAGG区切り文字列（'|'）になります。

      • 次のように出力する集計値が1つ多い場合，データ長が27バイトになるため，LISTAGG結果の最大長（24バイト）を超えてしまいます。そのため，この形式では出力されません。

        [図データ]

      ＜実行結果2＞

      LISTAGG結果の最大長の指定値によっては，LISTAGGの結果に集計値が1つも出力されないことがあります。

      （例）

      SQL文の例

      SELECT LISTAGG("C1",'|',15 ON OVERFLOW TRUNCATE '/*Overflow*/' WITHOUT COUNT)
         WITHIN GROUP (ORDER BY "C1") FROM "T1"

      上記のSQL文では，LISTAGG結果の最大長に15バイトを指定し，切り捨て末尾文字列に'/*Overflow*/'を指定しています。

      LISTAGGの結果

      [図データ]

      ［説明］

      • LISTAGG結果の最大長の指定値が小さ過ぎる場合，上記のようにLISTAGG区切り文字列（'|'）と切り捨て末尾文字列（'/*Overflow*/'）しか出力されません。

      • 次のように集計値を1つ出力した場合，データ長が16バイトになるため，LISTAGG結果の最大長（15バイト）を超えてしまいます。そのため，この形式では出力されません。

        [図データ]

      ＜実行結果3＞

      LISTAGG結果の最大長＜LISTAGG区切り文字列のデータ長＋切り捨て末尾文字列のデータ長の場合，LISTAGGの結果には'...'だけが出力されます。

      （例）

      SQL文の例

      SELECT LISTAGG("C1",'|',12 ON OVERFLOW TRUNCATE '/*Overflow*/' WITHOUT COUNT)
         WITHIN GROUP(ORDER BY "C1") FROM "T1"

      上記のSQL文では，LISTAGG結果の最大長に12バイトを指定し，切り捨て末尾文字列に'/*Overflow*/'を指定しています。

      LISTAGGの結果

      ...

      上記の例の場合，LISTAGG区切り文字列（'|'）が1バイトで，切り捨て末尾文字列（'/*Overflow*/'）が12バイトです。よって，LISTAGG区切り文字列のデータ長＋切り捨て末尾文字列のデータ長は13バイトになります。LISTAGG結果の最大長（12バイト）を超えるため，LISTAGGの結果には'...'だけが出力されます。

ページの先頭へ

(4)　例題

次に示す売上表（SALES）を検索対象とするSQL文の実行例を説明します。

[図データ]

例題1

顧客ID（CID）ごとに，購入した商品の商品ID（PID）を求めます。LISTAGGの結果（PID_LIST）には，次の条件で商品IDを出力します。

  • 単価（PRICE）順に商品ID（PID）を連結する。

  • 各商品IDを'|'で区切る。

SELECT "CID",
       LISTAGG("PID",'|') WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

上記のSELECT文では，次の指定（上記の下線部分）をしています。

  • LISTAGG区切り文字列に'|'を指定しています。

  • WITHINグループ指定のソート指定リストに，商品IDを単価（PRICE）順に連結する指定をしています。

実行結果の例

[図データ]

例題2（LISTAGGの集計値から重複した値を排除する場合）

顧客ID（CID）ごとに，購入した商品の商品ID（PID）を求めます。LISTAGGの結果（PID_LIST）には，次の条件で商品IDを出力します。

  • 単価（PRICE）順に商品ID（PID）を連結する。

  • 各商品IDを'|'で区切る。

  • 商品IDの重複を排除する。※

注※　例題1と差異がある条件です。

SELECT "CID",
       LISTAGG(DISTINCT "PID",'|') WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

上記のSELECT文では，LISTAGGの集計値から重複した値を排除するDISTINCTが指定（上記の下線部分）されています。

実行結果の例

[図データ]

例題3（切り捨て末尾文字列に'...'を出力する場合）

顧客ID（CID）ごとに，購入した商品の商品ID（PID）を求めます。LISTAGGの結果（PID_LIST）には，次の条件で商品IDを出力します。

  • 単価（PRICE）順に商品ID（PID）を連結する。

  • 各商品IDを'|'で区切る。

  • LISTAGG結果の最大長を20バイト（VARCHAR(20)）とする。※

  • 連結データのデータ長が，LISTAGG結果の最大長を超えた場合，切り捨て末尾文字列（'...'）を出力する。※

注※　例題1と差異がある条件です。

SELECT "CID",
       LISTAGG("PID",'|',20 ON OVERFLOW TRUNCATE WITHOUT COUNT)
         WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

上記のSELECT文では，次の指定（上記の下線部分）をしています。

  • LISTAGG結果の最大長に20バイトを指定しています。

  • 結果溢れ動作には，連結データのデータ長が，LISTAGG結果の最大長を超えた場合，切り捨て末尾文字列に'...'（省略値）を出力する指定をしています。

実行結果の例

[図データ]

［説明］

  • CID（顧客ID）が'C0001'の行のPID_LIST（LISTAGGの結果）には，連結データのデータ長（17バイト）が，LISTAGG結果の最大長（20バイト）以下のため，連結データがすべて出力されます。

  • CID（顧客ID）が'C0002'の行のPID_LIST（LISTAGGの結果）には，連結データのデータ長（23バイト）が，LISTAGG結果の最大長（20バイト）を超えるため，連結データの一部と，末尾に切り捨て末尾文字列（'...'）が出力されます。

例題4（切り捨て末尾文字列に任意の文字列を出力する場合）

顧客ID（CID）ごとに，購入した商品の商品ID（PID）を求めます。LISTAGGの結果（PID_LIST）には，次の条件で商品IDを出力します。

  • 単価（PRICE）順に商品ID（PID）を連結する。

  • 各商品IDを'|'で区切る。

  • LISTAGG結果の最大長を20バイト（VARCHAR(20)）とする。

  • 連結データのデータ長が，LISTAGG結果の最大長を超えた場合，切り捨て末尾文字列（'/*Overflow*/'）を出力する。※

注※　例題3と差異がある条件です。

SELECT "CID",
       LISTAGG("PID",'|',20 ON OVERFLOW TRUNCATE '/*Overflow*/' WITHOUT COUNT)
         WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

上記のSELECT文では，次の指定（上記の下線部分）をしています。

  • LISTAGG結果の最大長に20バイトを指定しています。

  • 結果溢れ動作には，連結データのデータ長が，LISTAGG結果の最大長を超えた場合，切り捨て末尾文字列に'/*Overflow*/'を出力する指定をしています。

実行結果の例

[図データ]

［説明］

  • CID（顧客ID）が'C0001'の行のPID_LIST（LISTAGGの結果）には，連結データのデータ長（17バイト）が，LISTAGG結果の最大長（20バイト）以下のため，連結データがすべて出力されます。

  • CID（顧客ID）が'C0002'の行のPID_LIST（LISTAGGの結果）には，連結データのデータ長（23バイト）が，LISTAGG結果の最大長（20バイト）を超えるため，連結データの一部と，末尾に切り捨て末尾文字列（'/*Overflow*/'）が出力されます。

例題5（連結データのデータ長がLISTAGG結果の最大長を超えたときはSQL文をエラーにする場合）

顧客ID（CID）ごとに，購入した商品の商品ID（PID）を求めます。LISTAGGの結果（PID_LIST）には，次の条件で商品IDを出力します。

  • 単価（PRICE）順に商品ID（PID）を連結する。

  • 各商品IDを'|'で区切る。

  • LISTAGG結果の最大長を20バイト（VARCHAR(20)）とする。

  • 連結データのデータ長が，LISTAGG結果の最大長を超えた場合，SQL文をエラーにする。※

注※　例題3および例題4と差異がある条件です。

SELECT "CID",
       LISTAGG("PID",'|',20 ON OVERFLOW ERROR)
         WITHIN GROUP (ORDER BY "PRICE" ASC) AS "PID_LIST"
  FROM "SALES"
    GROUP BY "CID"

上記のSELECT文では，次の指定（上記の下線部分）をしています。

  • LISTAGG結果の最大長に20バイトを指定しています。

  • 結果溢れ動作には，連結データのデータ長が，LISTAGG結果の最大長を超えた場合，SQL文をエラーとする指定をしています。

実行結果の例

連結データが'P0013|P0008|P0010|P0016'（23バイト）となる行があり，連結データのデータ長がLISTAGG結果の最大長を超えるため，実行したSQL文はエラーになります。

ページの先頭へ

(5)　留意事項

  1. WITHINグループ指定に指定したソートキーに同じ値がある場合，同じSQL文を実行してもLISTAGGの集計値の連結順序が異なることがあります。例を次に示します。

    （例）

    検索対象の表

    [図データ]

    実行するSQL文

    SELECT LISTAGG("C1",'|') WITHIN GROUP(ORDER BY "C2") AS "Name" FROM "T1"

    実行結果の例1

    Tom|Mike|Stephanie|Flora|Nancy

    実行結果の例2

    Tom|Stephanie|Mike|Flora|Nancy

    MikeとStephanieは，ソートキーの値が同じであるため，同じSQL文を実行しても上記の例のように実行結果（LISTAGGの集計値の連結順序）が異なることがあります。

  2. LISTAGG結果の最大長の指定を省略した場合，LISTAGG結果の最大長には1,024バイトが仮定されます。連結データのデータ長が1,024バイトよりも著しく小さいと，LISTAGGの処理の際にリソースを必要以上に使用してしまい，その結果，作業表の行長の最大値を超えてしまうなどのエラーが発生するおそれがあります。このようなエラーが発生した場合，LISTAGG結果の最大長には，次に示す計算式から求めた値を指定することを推奨します。

    （data_len＋LISTAGG区切り文字列の長さ）×LISTAGGの集計値の数

    data_len：LISTAGGの引数に指定した値式のデータ型に従って次の値を代入してください。

    表7‒29　data_lenに代入する値

    値式のデータ型

    data_lenに代入する値

    例

    CHAR(n)

    n

    なし

    VARCHAR

    LISTAGGの集計値の実長の最大値を代入します。

    LISTAGGの集計値が，'AB'，'ABC'，'ABCD'の場合，data_lenには4を代入します。

    INTEGER

    またはSMALLINT

    LISTAGGの集計値の数データを文字データに変換した結果※の文字列の長さの最大値を代入します。

    INTEGER型またはSMALLINT型のデータを文字データに変換した結果が，'123'，'1234'，'-1234'の場合，data_lenには5を代入します。

    DECIMAL

    またはNUMERIC

    DECIMAL型またはNUMERIC型のデータを文字データに変換した結果が，'1.20'，'12.34'，'-12.34'の場合，data_lenには6を代入します。

    DOUBLE PRECISION

    またはFLOAT

    DOUBLE PRECISION型またはFLOAT型のデータを文字データに変換した結果が，'1.23E45'，'-1.23E45'，'1.23E-45'の場合，data_lenには8を代入します。
    注※

    数データを文字データに変換する際の規則は，スカラ関数CONVERTによって数データを文字データに変換する際の規則（数値書式の指定なしの場合）に従います。スカラ関数CONVERTによる数データを文字データに変換する際の規則については，「8.13.5　CONVERT」の「(5)　規則」の「(c)　文字データに変換する場合の規則」を参照してください。

ページの先頭へ

All Rights Reserved. Copyright (C) 2012, 2021, Hitachi, Ltd.