カテゴリ:

集計関数 (一般)、 ウィンドウ関数 (一般)

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

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

OVER()

OVER 句は、関数がウィンドウ関数として使用されていることを指定します。詳細については、 ウィンドウ関数 をご参照ください。

オプション:

DISTINCT

リストから重複する値を削除します。

delimiter

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

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

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

WITHIN GROUP orderby_clause

リスト内の各グループの値の順序を決定する1つ以上の式(通常は列名)を含む句。

WITHIN GROUP(ORDER BY)構文は、 SELECT ステートメントのメインの ORDER BY 句と同じパラメーターをサポートしています。 ORDER BY をご参照ください。

PARTITION BY expr2

式(通常は列名)を指定するウィンドウ数のサブ句。この式は、関数が適用される前に入力行をグループ化するパーティションを定義します。詳細については、 ウィンドウ関数 をご参照ください。

戻り値

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

この関数は「リスト」や配列を返さないことに注意してください。 NULL 以外のすべての入力値を含む単一の文字列を返します。

使用上の注意

  • WITHIN GROUP(ORDER BY)を指定しない場合、各リスト内の要素の順序は予測できません。(WITHIN GROUP 句以外の ORDERBY 句は、行内のリスト要素の順序ではなく、出力行の順序に適用されます。)

  • WITHIN GROUP(ORDER BY)の式に数値を指定した場合、この数値は SELECT リストの列の序列としてではなく、数値定数として解析されます。このため、 WITHIN GROUP(ORDER BY) 式として数値を指定しないでください。

  • DISTINCT と WITHINGROUP を指定する場合は、両方が同じ列を参照する必要があります。例:

    SELECT LISTAGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
    
    Copy

    DISTINCT と WITHINGROUP に異なる列を指定すると、エラーが発生します。

    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
    

    DISTINCT と WITHINGROUP に同じ列を指定するか、 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つの ORDERBY 句が含まれています。最初の照合順序内で照合順序を指定しても、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