カテゴリ：

集計関数 （一般）、 ウィンドウ関数の構文と使用法 （一般）

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 句と同じパラメーターをサポートします。

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 'es')
  FROM table1
  ORDER BY last_name;
    Copy

例

これらの例では、 LISTAGG 関数を使用しています。

LISTAGG 関数を使用したクエリ結果の値の連結

以下の例では、 LISTAGG 関数を使用して、注文データに対するクエリの結果の値を連結しています。

注釈

これらの例では、 TPC-Hサンプルデータ をクエリしています。クエリを実行する前に、以下の SQL ステートメントを実行します。

USE SCHEMA snowflake_sample_data.tpch_sf1;
Copy

この例では、 o_totalprice が 520000 より大きい注文について、 o_orderkey の値をリスト表示し、 delimiter には空の文字列を使用しています。

SELECT LISTAGG(DISTINCT o_orderkey, ' ')
  FROM orders
  WHERE o_totalprice > 520000;
Copy
+-------------------------------------------------+
| LISTAGG(DISTINCT O_ORDERKEY, ' ')               |
|-------------------------------------------------|
| 2232932 1750466 3043270 4576548 4722021 3586919 |
+-------------------------------------------------+

この例では、 o_totalprice が 520000 より大きい注文について、 o_orderstatus の値をリスト表示し、 delimiter に垂直バーを使用しています。

SELECT LISTAGG(DISTINCT o_orderstatus, '|')
  FROM orders
  WHERE o_totalprice > 520000;
Copy
+--------------------------------------+
| LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
|--------------------------------------|
| O|F                                  |
+--------------------------------------+

この例では、 o_totalprice が 520000 より大きい各注文の o_orderstatus と o_clerk の値を、 o_orderstatus でグループ化してリスト表示しています。このクエリでは、 delimiter にコンマを使用します。

SELECT o_orderstatus,
   LISTAGG(o_clerk, ', ')
     WITHIN GROUP (ORDER BY o_totalprice DESC)
  FROM orders
  WHERE o_totalprice > 520000
  GROUP BY o_orderstatus;
Copy
+---------------+---------------------------------------------------+
| O_ORDERSTATUS | LISTAGG(O_CLERK, ', ')                            |
|               |      WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)    |
|---------------+---------------------------------------------------|
| O             | Clerk#000000699, Clerk#000000336, Clerk#000000245 |
| F             | Clerk#000000040, Clerk#000000230, Clerk#000000924 |
+---------------+---------------------------------------------------+

LISTAGG 関数を使用した照合の使用

次の例は、 LISTAGG を使用した 照合 を示しています。例では、次のデータを使用します。

CREATE OR REPLACE TABLE collation_demo (
  spanish_phrase VARCHAR COLLATE 'es');
Copy
INSERT INTO collation_demo (spanish_phrase) VALUES
  ('piña colada'),
  ('Pinatubo (Mount)'),
  ('pint'),
  ('Pinta');
Copy

異なる照合仕様の出力順序の違いに注意してください。このクエリは、 es 照合仕様を使用しています。

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'es')) AS es_collation
  FROM collation_demo;
Copy
+-----------------------------------------+
| ES_COLLATION                            |
|-----------------------------------------|
| Pinatubo (Mount)|pint|Pinta|piña colada |
+-----------------------------------------+

このクエリは、 utf8 照合仕様を使用しています。

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8')) AS utf8_collation
  FROM collation_demo;
Copy
+-----------------------------------------+
| UTF8_COLLATION                          |
|-----------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada |
+-----------------------------------------+
言語: 日本語