Oracle LISTAGG函数及开窗函数详解

LISTAGG 是 Oracle 中一个非常强大且常用的字符串聚合函数，它用于将多行数据合并（或“串联”）成一行中的单个字符串。

---

一、LISTAGG 基础语法与功能

1. 基本语法（作为聚合函数）

LISTAGG(measure_column [, 'delimiter']) 
WITHIN GROUP (ORDER BY order_column [ASC | DESC])

• measure_column: 你想要合并的列（或表达式）。
• ‘delimiter’: 用于分隔各个值的字符串。这是一个可选参数，默认为 NULL（即无分隔符）。
• WITHIN GROUP (ORDER BY …): 必须的子句，用于指定合并结果中值的排序方式。

2. 基本功能示例

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

EMPLOYEE_ID	DEPARTMENT_ID	LAST_NAME
101	10	King
102	20	Kochhar
103	20	De Haan
104	20	Hunold
105	30	Austin

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

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

结果：

DEPARTMENT_ID	EMPLOYEES
10	King
20	De Haan; Hunold; Kochhar
30	Austin

这就是 LISTAGG 作为标准聚合函数的经典用法：与 GROUP BY 子句配合，将每个分组内的行合并成一个字符串。

---

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

开窗函数允许你为查询结果集中的每一行都执行计算，而不是将多行合并为一行。LISTAGG 支持这种模式，这使得它的功能更加灵活。

1. 语法（作为开窗函数）

LISTAGG(measure_column [, 'delimiter']) 
WITHIN GROUP (ORDER BY order_column [ASC | DESC])
OVER ([PARTITION BY partition_column])

核心变化是增加了 OVER (...) 子句：

• PARTITION BY partition_column: 定义了开窗的“窗口”或“分组”。函数会在每个分区内独立执行聚合。如果省略，函数会在整个结果集上操作。

2. 开窗函数功能示例

场景 1：列出每个部门的员工，同时在每一行都显示该部门的完整员工列表

SELECT 
    DEPARTMENT_ID,
    LAST_NAME,
    LISTAGG(LAST_NAME, '; ') 
        WITHIN GROUP (ORDER BY LAST_NAME) 
        OVER (PARTITION BY DEPARTMENT_ID) AS DEPT_EMPLOYEES
FROM 
    employees
ORDER BY 
    DEPARTMENT_ID, LAST_NAME;

结果：

DEPARTMENT_ID	LAST_NAME	DEPT_EMPLOYEES
10	King	King
20	De Haan	De Haan; Hunold; Kochhar
20	Hunold	De Haan; Hunold; Kochhar
20	Kochhar	De Haan; Hunold; Kochhar
30	Austin	Austin

关键点：

• 注意，结果的行数没有减少，仍然是 5 行。
• 对于部门 20 的每一行员工记录，DEPT_EMPLOYEES 列都重复显示了该部门所有员工的聚合字符串。
• 这非常有用，因为你可以在看到明细数据（单个员工）的同时，看到其所属分组（部门）的汇总信息。

场景 2：没有 PARTITION BY 的开窗
如果省略 PARTITION BY，LISTAGG 会在整个结果集上操作。

SELECT 
    DEPARTMENT_ID,
    LAST_NAME,
    LISTAGG(LAST_NAME, '; ') 
        WITHIN GROUP (ORDER BY LAST_NAME) 
        OVER () AS ALL_EMPLOYEES
FROM 
    employees;

结果（片段）：
每一行的 ALL_EMPLOYEES 列都会是 Austin; De Haan; Hunold; King; Kochhar。这展示了所有员工的排序列表。

---

三、处理溢出：ON OVERFLOW 子句

LISTAGG 的一个主要限制是返回的字符串长度不能超过 VARCHAR2 的最大长度（4000字节）。在 Oracle 19c 及更高版本中，引入了 ON OVERFLOW 子句来处理这个问题。

LISTAGG(measure_column [, 'delimiter'] [ON OVERFLOW {ERROR | TRUNCATE}])
WITHIN GROUP (ORDER BY ...)

• ON OVERFLOW ERROR (默认): 如果结果超过最大长度，抛出 ORA-01489: 错误。
• ON OVERFLOW TRUNCATE: 截断结果并添加省略号。
  • TRUNCATE ‘…’： 指定自定义的省略符号，例如 TRUNCATE ‘ <etc>’。
  • WITH COUNT： 在省略号后显示被截断的字符数。

示例：

-- 如果聚合后的字符串太长，会被截断并以 ‘...’ 显示，并提示被截掉的字符数
LISTAGG(VERY_LONG_TEXT, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
WITHIN GROUP (ORDER BY ID)

---

四、高级开窗技巧

你可以在 OVER() 子句中使用更多开窗函数的特性，但 LISTAGG 通常与 PARTITION BY 结合最为常见。像 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 这样的框架子句虽然语法上可能支持，但用于 LISTAGG 的实际场景较少，而且行为可能不符合直觉（它通常用于计算累加和、移动平均等，而非字符串聚合）。

一个更有用的模式是结合 DISTINCT。虽然 LISTAGG 本身不支持在函数内使用 DISTINCT，但你可以通过子查询或内联视图先去重，然后再聚合。

示例：实现去重聚合

假设员工表里可能有重复的姓氏，我们只想列出不重复的姓氏。

方法：使用子查询先去重

SELECT 
    DEPARTMENT_ID,
    LISTAGG(LAST_NAME, '; ') 
        WITHIN GROUP (ORDER BY LAST_NAME) AS UNIQUE_EMPLOYEES
FROM (
    SELECT DISTINCT
        DEPARTMENT_ID,
        LAST_NAME
    FROM 
        employees
) 
GROUP BY 
    DEPARTMENT_ID;

---

总结

特性	作为聚合函数	作为开窗函数
目的	将一组行合并为一行（减少行数）	为每一行都计算其所在分组的聚合结果（不减少行数）
语法	与 GROUP BY 一起使用	与 OVER (PARTITION BY ...) 一起使用
结果	每个分组返回一行	原查询的每一行都返回，并附加聚合结果列
典型用例	制作分组汇总报告（如逗号分隔的列表）	在明细数据中查看其所属分组的上下文信息

核心要点：

1. LISTAGG 是强大的行转字符串工具。
2. 作为开窗函数使用时，它不会减少行数，而是为每一行添加上下文信息，这在制作报表时极其有用。
3. 始终记得用 WITHIN GROUP (ORDER BY ...) 来保证结果的顺序。
4. 在 Oracle 19c+ 中，使用 ON OVERFLOW TRUNCATE 优雅地处理长字符串溢出错误，而不是让查询直接失败。
5. 需要通过子查询技巧来实现 DISTINCT 聚合。