Oracle函数之listagg函数

快来打我* 2022-03-30 04:08 545阅读 0赞

## 语法 ##

![在这里插入图片描述][201901041720359.png]  
有点难以看懂，个人理解listagg是list aggregate的缩写（错了勿喷），也就是列表总计，聚合的意思。  
官方文档解释为：

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

大概意思就是listagg函数先在每个组里用order by子句排序之后，再拼接每个分组里特指的列值。  
从上面的解释可以知道，listagg每个组进行操作并返回一个输出行。

> 注意：这里的组在没有group by子句的时候指的是整个结果集，partition by子句指的是结果集中分区。

## 参数解释 ##

上图的参数有：

*  ALL：ALL关键字是可选的。
 *  measure\_expr ：measure\_expr是被操作的列，可以是任何表达式。被操作的列中的空值将被忽略。
 *  delimiter：分隔符，用来分割被操作列的列值，就是拼接每个列值后面跟一个分隔符，最后一个列值除外，分隔符也是可选的，默认值为NULL。
 *  listagg\_overflow\_clause：这个子句控制当返回值超过返回数据类型的最大长度时函数的行为。也是可选的。
 *  order\_by\_clause：顾名思义，排序子句。决定被连接值的顺序，谁在前谁在后。
 *  query\_partition\_clause：listagg根据query\_partition\_clause中的一个或多个表达式将查询结果集划分为多个区(partition)。即分区之后，再进行列值拼接，注意partition的字段没有进行去重，仅仅是分区后进行处理，不会改变行数。Parttion by 关键字是Oracle中分析函数的一部分，它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的结果。

## 实例 ##

#### 聚合函数 ####

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

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

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

#### 分析函数 ####

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

先进行"Dept", “Date”, "Name"分组得到分组结果，再对结果集进行"department\_id"分区，最后整合列值。

参考官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/LISTAGG.html\#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466

[201901041720359.png]: /images/20220330/7772f7996cc145448222a6fa24037344.png