LISTAGG

構文

図listagg.gifの説明

関連項目:

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

用途

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

単一セットの集計ファンクションとして使用すると、LISTAGGはすべての行に対して操作を行い、1つの出力行を戻します。
グループ・セットの集計に使用すると、このファンクションはGROUP BY句に定義されたグループごとに操作を行い、そのグループごとに1つの出力行を戻します。
分析ファンクションとして使用すると、LISTAGGはquery_partition_clauseに指定された1つ以上の式に基づくグループに、問合せの結果セットを分割します。

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

measure_exprには、任意の式を指定できます。メジャー列のNULL値は無視されます。
delimiter_exprには、メジャー値の区切りに使用する文字列を指定します。この句はオプションであり、デフォルトはNULLです。
order_by_clauseには、結合した値を戻す順序を指定します。このファンクションが決定的となるのは、ORDER BYの列リストの順序が一意になった場合のみです。

メジャー列がRAWの場合は、RAWデータ型が戻されます。それ以外の場合は、戻り値がVARCHAR2になります。

集計の例

次の単一セットの集計例は、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
. . .

分析の例

次の分析例は、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