カテゴリ：: 集計関数（一般）、ウィンドウ関数（一般）

LISTAGG¶

delimiter 文字列で区切られた、連結された入力値を返します。

構文¶

集計関数

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] ) [ WITHIN GROUP ( <orderby_clause> ) ]

Copy

ウィンドウ関数

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

Copy

引数¶

expr1

リストに入れる値を決定する式（通常は列名）です。式は、文字列、または文字列にキャストできるデータ型に評価される必要があります。

delimiter

文字列、または文字列に評価される式です。実際には、これは通常1文字の文字列です。以下の例に示すように、文字列は一重引用符で囲む必要があります。

delimiter 文字列が指定されていない場合は、空の文字列が delimiter として使用されます。

delimiter は定数でなければなりません。

expr2

この式は、パーティション内の行をグループ化するために使用されます。

orderby_clause

リスト内の値の順序を決定する式（通常は列名）。

戻り値¶

delimiter で区切られた、NULL 以外のすべての入力値を含む文字列を返します。

（これは「リスト」を返しません（例えば、 ARRAY は返されません。NULL 以外のすべての入力値を含む単一の文字列を返します）

使用上の注意¶

DISTINCT は、この関数でサポートされています。
WITHIN GROUP (<orderby句>) を指定しない場合、各リスト内の要素の順序は予測できません。（WITHIN GROUP 句以外の ORDER BY 句は、行内のリスト要素の順序ではなく、出力行の順序に適用されます）
DISTINCT と WITHIN GROUP を指定する場合は、両方が同じ列を参照する必要があります。例:
```
SELECT listagg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
```
Copy
DISTINCT と WITHIN GROUP に異なる列を指定すると、エラーが発生します。
```
SELECT listagg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
```
Copy
```
SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression
```
Copy
DISTINCT と WITHIN GROUP に同じ列を指定するか、 DISTINCT を省略する必要があります。
NULL または空の入力値に関しては、
- 入力が空の場合、空の文字列が返されます。
- すべての入力式が NULLに評価される場合、出力は空の文字列です。
- 一部ではあるがすべてではない入力式が NULL に評価される場合、出力にはすべての非NULL 値が含まれ、 NULL 値は除外されます。
この関数は、ウィンドウ関数として使用する場合に以下をサポートしません。
- OVER()句の ORDER BY サブ句。
- ウィンドウフレーム。

照合の詳細¶

The collation of the result is the same as the collation of the input.
リスト内の要素は、 ORDER BY サブ句が照合で式を指定した場合、照合に従って順序付けられます。
delimiter は照合仕様を使用できません。
ORDER BY 内で照合を指定しても、結果の照合には影響しません。例えば、次のステートメントには、 LISTAGG 用とクエリ結果用の2つの ORDER BY 句が含まれています。最初の照合順序内で照合順序を指定しても、2番目の照合順序には影響しません。両方の ORDER BY 句で出力を照合する必要がある場合は、両方の句で照合を明示的に指定する必要があります。
```
select listagg(x, ', ') within group (ORDER BY last_name collate 'sp')
    from table1
    ORDER BY last_name;
```
Copy

例¶

SELECT listagg(O_ORDERKEY, ' ')
    FROM orders WHERE O_TOTALPRICE > 450000;

---------------------------------------------+
          LISTAGG(O_ORDERKEY, ' ')           |
---------------------------------------------+
 41445 55937 67781 80550 95808 101700 103136 |
---------------------------------------------+

Copy

SELECT listagg(DISTINCT O_ORDERSTATUS, '|')
    FROM orders WHERE O_TOTALPRICE > 450000;

--------------------------------------+
 LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
--------------------------------------+
 F|O                                  |
--------------------------------------+

Copy

SELECT O_ORDERSTATUS, listagg(O_CLERK, ', ') WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)
    FROM orders WHERE O_TOTALPRICE > 450000 GROUP BY O_ORDERSTATUS;

---------------+--------------------------------------------------------------------+
 O_ORDERSTATUS |  LISTAGG(O_CLERK, ', ') WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)  |
---------------+--------------------------------------------------------------------+
 O             | Clerk#000000220, Clerk#000000411, Clerk#000000114                  |
 F             | Clerk#000000508, Clerk#000000136, Clerk#000000521, Clerk#000000386 |
---------------+--------------------------------------------------------------------+

Copy

次の例は、 LISTAGG との照合を示しています。異なる照合仕様の出力順序の違いに注意してください。

-- Collation
SELECT LISTAGG(spanish_phrase, '|') 
        WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'sp'))
    FROM collation_demo
    GROUP BY english_phrase;
+---------------------------------------------------------------+
| LISTAGG(SPANISH_PHRASE, '|')                                  |
|         WITHIN GROUP (ORDER BY COLLATE(SPANISH_PHRASE, 'SP')) |
|---------------------------------------------------------------|
| piña colada|Pinatubo (Mount)|pint|Pinta                       |
+---------------------------------------------------------------+
-- Different collation.
SELECT LISTAGG(spanish_phrase, '|') 
        WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8'))
    FROM collation_demo
    GROUP BY english_phrase;
+-----------------------------------------------------------------+
| LISTAGG(SPANISH_PHRASE, '|')                                    |
|         WITHIN GROUP (ORDER BY COLLATE(SPANISH_PHRASE, 'UTF8')) |
|-----------------------------------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada                         |
+-----------------------------------------------------------------+

Copy