Oracle LISTAGG 函数与开窗函数详解

我们来详细讲解一下 Oracle 中的 LISTAGG 函数及其开窗函数（分析函数）的用法。

一、LISTAGG 函数基础

LISTAGG 是 Oracle 11g Release 2 中引入的一个非常实用的字符串聚合函数。它的主要作用是将多行数据合并（或连接）成一个单一的字符串，并在每个值之间添加指定的分隔符。

1. 基本语法

LISTAGG(measure_column, 'delimiter') WITHIN GROUP (ORDER BY order_column)

• measure_column: 你想要合并的那些行的列名。
• 'delimiter': 用于分隔各个值的字符串，比如 ,、;、- 等。
• WITHIN GROUP (ORDER BY order_column): 必需子句。指定合并这些值时的排序方式。

2. 简单示例

假设我们有一个 employees 表，数据如下：

EMPLOYEE_ID	FIRST_NAME	DEPARTMENT_ID
101	Alice	10
102	Bob	10
103	Charlie	20
104	David	10
105	Eve	20

需求： 列出每个部门的所有员工姓名，用逗号分隔，并按姓名排序。

SELECT
    DEPARTMENT_ID,
    LISTAGG(FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) AS EMPLOYEES
FROM
    employees
GROUP BY
    DEPARTMENT_ID;

查询结果：

DEPARTMENT_ID	EMPLOYEES
10	Alice, Bob, David
20	Charlie, Eve

这就是 LISTAGG 最核心的用法：与 GROUP BY 一起使用，实现组内的字符串聚合。

---

二、LISTAGG 作为开窗函数（分析函数）

LISTAGG 的强大之处在于它也可以作为分析函数（Analytic Function）使用。这意味着你可以在不进行分组（GROUP BY）的情况下，为每一行都返回其所在窗口内的聚合结果。

1. 开窗函数语法

LISTAGG(measure_column, 'delimiter') 
WITHIN GROUP (ORDER BY order_column)
OVER (PARTITION BY partition_column [ORDER BY window_order_column ROWS/RANGE ...])

• OVER: 关键字，表示这是一个分析函数。
• PARTITION BY partition_column: 将数据分成不同的区/窗口（类似于 GROUP BY），函数会分别作用于每个分区。这是可选的，如果省略，函数会作用于整个结果集。
• ORDER BY window_order_column: 定义窗口内行的顺序。注意：这个 ORDER BY 和 WITHIN GROUP 子句里的 ORDER BY 目的不同：
  • WITHIN GROUP (ORDER BY ...)： 指定聚合字符串中值的顺序。
  • OVER (ORDER BY ...)： 指定窗口函数计算时，窗口内行的顺序和滑动窗口的范围。通常可以只用一个 ORDER BY，但复杂场景下需要区分。
• ROWS/RANGE: 定义窗口的框架（Frame），即指定滑动窗口的范围（例如，“从当前行到之后一行”）。在 LISTAGG 中使用相对较少，但功能强大。

2. 开窗函数示例

让我们继续使用上面的 employees 表。

示例 1：为每个员工列出其所在部门的所有同事

我们想要一个结果，在每一行都显示该员工及其所有同部门同事的名单。

SELECT
    EMPLOYEE_ID,
    FIRST_NAME,
    DEPARTMENT_ID,
    LISTAGG(FIRST_NAME, ', ') 
        WITHIN GROUP (ORDER BY FIRST_NAME) 
        OVER (PARTITION BY DEPARTMENT_ID) AS DEPARTMENT_COLLEAGUES
FROM
    employees;

查询结果：

EMPLOYEE_ID	FIRST_NAME	DEPARTMENT_ID	DEPARTMENT_COLLEAGUES
101	Alice	10	Alice, Bob, David
102	Bob	10	Alice, Bob, David
104	David	10	Alice, Bob, David
103	Charlie	20	Charlie, Eve
105	Eve	20	Charlie, Eve

可以看到，DEPARTMENT_COLLEAGUES 列对于部门 10 的每一行都重复显示了相同的聚合字符串 “Alice, Bob, David”。这就是开窗函数的魅力——它保留了所有原始行，同时给出了基于分区的聚合信息。

示例 2：累积列表（Running List）

这是一个更高级的用法。我们不仅想分区，还想让列表随着行的顺序逐步累积。

需求： 按 EMPLOYEE_ID 排序，为每个员工列出到目前为止（包括自己）所有出现过的员工名字。

SELECT
    EMPLOYEE_ID,
    FIRST_NAME,
    LISTAGG(FIRST_NAME, ' -> ') 
        WITHIN GROUP (ORDER BY EMPLOYEE_ID) 
        OVER (ORDER BY EMPLOYEE_ID) AS RUNNING_LIST
FROM
    employees;

查询结果：

EMPLOYEE_ID	FIRST_NAME	RUNNING_LIST
101	Alice	Alice
102	Bob	Alice -> Bob
103	Charlie	Alice -> Bob -> Charlie
104	David	Alice -> Bob -> Charlie -> David
105	Eve	Alice -> Bob -> Charlie -> David -> Eve

这里，我们省略了 PARTITION BY，窗口函数在整个结果集上操作。OVER (ORDER BY EMPLOYEE_ID) 创建了一个默认的窗口框架 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这意味着窗口包含从第一行到当前行的所有数据。LISTAGG 则将这些行中的 FIRST_NAME 按 EMPLOYEE_ID 顺序拼接起来。

---

三、重要注意事项和局限性

1. 字符串长度限制 (ORA-01489)

   • 这是使用 LISTAGG 时最常遇到的错误。LISTAGG 返回的字符串是 VARCHAR2 类型，受其最大长度（4000 字符）限制。如果聚合后的字符串超过这个限制，会报错 ORA-01489: result of string concatenation is too long。
   • 解决方案：
     • Oracle 12c R2 及以上：使用 ON OVERFLOW 子句。

       -- 截断超长部分并添加 '...'，不报错
       LISTAGG(FIRST_NAME, ', ' ON OVERFLOW TRUNCATE '...') WITHIN GROUP ...
       -- 遇到超长直接报错（默认行为）
       LISTAGG(FIRST_NAME, ', ' ON OVERFLOW ERROR) WITHIN GROUP ...
       

     • 所有版本：在聚合前使用 SUBSTR 限制每个字段的长度，或者使用 XMLAGG 等替代方案。

2. 去重 (DISTINCT)

   • 直接在 LISTAGG 的列参数中使用 DISTINCT 可以去除重复值。

   -- 只列出不同的名字
   LISTAGG(DISTINCT FIRST_NAME, ', ') WITHIN GROUP (ORDER BY FIRST_NAME) ...
   

3. 处理 NULL 值

   • LISTAGG 会忽略 measure_column 中的 NULL 值。

总结

特性	作为聚合函数 (与 GROUP BY 共用)	作为开窗函数 (与 OVER 共用)
目的	将一组行压缩成一行，并聚合字符串。	为每一行都计算其所在分区的聚合字符串，不压缩行。
结果行数	等于分组（GROUP BY）的数量。	等于原始查询结果的行数。
典型用法	生成报表，显示每个组的成员列表。	在详细数据中提供上下文信息（如同事列表、累积路径）。

掌握 LISTAGG 的基础和开窗用法，能极大地提升你在 Oracle 中进行数据转换和报表输出的能力。