LISTAGG
構文
listagg_overflow_clause::=
関連項目:
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
の列リストの順序が一意になった場合のみです。
メジャー列がRAW
型である場合、戻り値のデータ型はRAW
です。そうでない場合、戻りデータ型はVARCHAR2
です。
戻りデータ型の最大長は、MAX_STRING_SIZE
初期化パラメータの値に応じて異なります。MAX_STRING_SIZE
=
EXTENDED
の場合、VARCHAR2
データ型およびRAW
データ型の最大長は32767バイトです。MAX_STRING_SIZE
=
STANDARD
の場合、VARCHAR2
データ型の最大長は4000バイトで、RAW
データ型の最大長は2000バイトです。戻り値が戻りデータ型に収まるかどうかを確認する場合、最終デリミタは含まれません。
関連項目:
-
MAX_STRING_SIZE
初期化パラメータの詳細は、「拡張データ型」を参照してください。 -
LISTAGG
の文字の戻り値に割り当てる照合を定義する照合導出ルールは、『Oracle Databaseグローバリゼーション・サポート・ガイド』の付録Cを参照してください。
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