listagg 函数 学习笔记

LISTAGG

Syntax

Purp ose

For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.

• As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.

• As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.

• As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause.

The arguments to the function are subject to the following rules:

• The measure_expr can be any expression. Null values in the measure column are ignored.

• The delimiter_expr designates the string that is to separate the measure values. This clause is optional and defaults to NULL.

• The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.

The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2.

listagg的作用是将分组范围内的所有行特定列的记录加以合并成行。函数签名中的measure_expr为分组中每个列的表达式，而delimiter为合并分割符。
如果delimiter不设置的话，就表示无分割符。

中间within group后面的order_by_clause表示的是进行合并中要遵守的排序顺序。而后面的over子句表明listagg是具有分析函数analyze funcation特性的。

Aggregate Examples

一.单集合
The following single-set aggregate example lists all of the employees in Department 30 in the hr.employees table, ordered by hire date and last name:

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

二.组集合
The following group-set aggregate example lists, for each department ID in the hr.employees table, the employees in that department in order of their hire date:

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
. . .

添加over分析函数。
统计所有雇佣日期在03年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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29074224/viewspace-2123628/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29074224/viewspace-2123628/