LISTAGG

構文

listagg.epsの説明が続きます
図listagg.epsの説明

(listagg_overflow_clause::=、order_by_clause::=、query_partition_clause::=)

listagg_overflow_clause::=

listagg_overflow_clause.epsの説明が続きます
図listagg_overflow_clause.epsの説明

関連項目:

ORDER BY句およびOVER句の構文、セマンティクスおよび制限事項の詳細は、「分析ファンクション」を参照してください。

目的

LISTAGGは、指定されたメジャーについて、ORDER BY句に指定された各グループ内でデータを順序付け、メジャー列の値を結合します。

  • 単一セットの集計ファンクションとして使用すると、LISTAGGはすべての行に対して操作を行い、1つの出力行を戻します。

  • グループ・セットの集計に使用すると、このファンクションはGROUP BY句に定義されたグループごとに操作を行い、そのグループごとに1つの出力行を戻します。

  • 分析ファンクションとして使用すると、LISTAGGはquery_partition_clauseに指定された1つ以上の式に基づくグループに、問合せの結果セットを分割します。

ファンクションの引数には、次の規則があります。

  • ALLキーワードはオプションで、セマンティクスを明確にするために使用されます。

  • measure_exprはメジャー列であり、任意の式を指定できます。メジャー列のNULL値は無視されます。

  • delimiterには、メジャー列の値の区切りに使用する文字列を指定します。この句はオプションであり、デフォルトはNULLです。

    measure_exprがRAW型である場合、デリミタはRAW型である必要があります。そのためには、暗黙的にRAWに変換できる文字列としてデリミタを指定するか、UTL_RAW.CAST_TO_RAW関数を使用するなどして、デリミタを明示的にRAWに変換します。

  • order_by_clauseには、結合した値を戻す順序を指定します。このファンクションが決定的となるのは、ORDER BYの列リストの順序が一意になった場合のみです。

  • order_by_clauseを指定する場合は、WITHIN GROUPも指定する必要があります。その逆も同様です。これらの2つの句は、まとめて指定するか、まったく指定しないでください。

DISTINCTキーワードを使用すると、重複する値がリストから削除されます。

メジャー列がRAW型である場合、戻り値のデータ型はRAWです。そうでない場合、戻りデータ型はVARCHAR2です。

戻りデータ型の最大長は、MAX_STRING_SIZE初期化パラメータの値に応じて異なります。MAX_STRING_SIZE = EXTENDEDの場合、VARCHAR2データ型およびRAWデータ型の最大長は32767バイトです。MAX_STRING_SIZE = STANDARDの場合、VARCHAR2データ型の最大長は4000バイトで、RAWデータ型の最大長は2000バイトです。戻り値が戻りデータ型に収まるかどうかを確認する場合、最終デリミタは含まれません。

関連項目:

listagg_overflow_clause

この句は、戻り値が戻りデータ型の最大長を超える場合の、ファンクションの動作を制御します。

ON OVERFLOW ERROR この句を指定すると、ORA-01489エラーが戻されます。これはデフォルトです。

ON OVERFLOW TRUNCATE この句を指定すると、メジャー値の切り捨てられたリストが戻されます。

  • truncation_indicatorには、メジャー値の切り捨てられたリストに追加する文字列を指定します。この句を省略すると、切り捨てインジケータは省略記号(...)になります。

    measure_exprがRAW型である場合、切捨てインジケータはRAW型である必要があります。そのためには、暗黙的にRAWに変換できる文字列として切捨てインジケータを指定するか、UTL_RAW.CAST_TO_RAW関数を使用するなどして、切捨てインジケータを明示的にRAWに変換します。

  • WITH COUNTを指定した場合、切捨てインジケータの後に、切り捨てられた値の数がカッコで囲まれて追加されます。この場合、戻り値に最終デリミタ、切り捨てインジケータおよびカッコで囲まれた24文字の数値のスペースを確保するのに十分なメジャー値が切り捨てられます。

  • WITHOUT COUNTを指定した場合、戻り値から切り捨てられた値の数が省略されます。この場合、戻り値に最終デリミタおよび切り捨てインジケータのスペースを確保するのに十分なメジャー値が切り捨てられます。

WITH COUNTまたはWITHOUT COUNTを指定しない場合は、デフォルトでWITH COUNTが設定されます。

集計の例

次の単一セットの集計例は、hr.employees表に含まれる部門30の全従業員を、雇用開始日と姓の順にリストします。 

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

次のグループ・セットの集計例は、hr.employees表の部門IDごとに、各部門の従業員を雇用開始日の順にリストします。 

SELECT department_id "Dept.",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;

Dept. Employees
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
       el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
        Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
       an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
    60 Austin; Hunold; Pataballa; Lorentz; Ernst
    70 Baer
. . .

次の例は、ON OVERFLOW TRUNCATE句が含まれることを除き、前の例と同じです。この例の目的のために、戻り値の最大長を、意図的に200バイトという小さい数にしています。部門50の従業員のリストは200バイトを超えるため、リストは切り捨てられ、最終デリミタ'; '、指定された切り捨てインジケータ'...'および切り捨てられた値の数'(23)'が追加されます。 

SELECT department_id "Dept.",
       LISTAGG(last_name, '; ' ON OVERFLOW TRUNCATE '...')
               WITHIN GROUP (ORDER BY hire_date) "Employees"
  FROM employees
  GROUP BY department_id
  ORDER BY department_id;

Dept. Employees
------ ------------------------------------------------------------
    10 Whalen
    20 Hartstein; Fay
    30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
    40 Mavris
    50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
       s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
       ; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; ... (23)
    70 Baer
. . .

分析の例

次の分析例は、2003年9月1日より前に雇用された各従業員について、所属部門、雇用開始日、および同じ部門で2003年9月1日より前に雇用された他の全従業員を表示します。

SELECT department_id "Dept", hire_date "Date", last_name "Name",
       LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
         OVER (PARTITION BY department_id) as "Emp_list"
  FROM employees
  WHERE hire_date < '01-SEP-2003'
  ORDER BY "Dept", "Date", "Name";

 Dept Date      Name            Emp_list
----- --------- --------------- ---------------------------------------------
   30 07-DEC-02 Raphaely        Raphaely; Khoo
   30 18-MAY-03 Khoo            Raphaely; Khoo
   40 07-JUN-02 Mavris          Mavris
   50 01-MAY-03 Kaufling        Kaufling; Ladwig
   50 14-JUL-03 Ladwig          Kaufling; Ladwig
   70 07-JUN-02 Baer            Baer
   90 13-JAN-01 De Haan         De Haan; King
   90 17-JUN-03 King            De Haan; King
  100 16-AUG-02 Faviet          Faviet; Greenberg
  100 17-AUG-02 Greenberg       Faviet; Greenberg
  110 07-JUN-02 Gietz           Gietz; Higgins
  110 07-JUN-02 Higgins         Gietz; Higgins