LISTAGG 聚集函数
LISTAGG 函数可通过并置字符串来将一组字符串元素聚合为一个字符串。 (可选)可以提供要插入相邻输入字符串之间的分隔符字符串。
该模式是 SYSIBM。
LISTAGG 函数可通过根据 WITHIN GROUP 子句中指定的顺序附加 string-expression 值,将组中的一组字符串值聚合为一个字符串。
该函数将应用于派生自第一个自变量且消除了空值的值集。 如果指定了 DISTINCT,那么将消除重复的 string-expression 值。 如果指定的 separator 自变量不是空值,那么将在每对非空 string-expression 值之间插入该值。
- string-expression
- 用于指定要聚合的字符串值的表达式。 表达式必须返回内置字符串,图形字符串,二进制字符串,数字值,布尔值或日期时间值:
- 如果该值不是字符、图形或二进制字符串,或者如果该值是 CLOB,那么在对该函数进行求值之前,它将隐式强制转换为 VARCHAR。
- 如果该值是 DBCLOB,那么在对该函数进行求值之前,它将隐式强制转换为 VARGRAPHIC。
- 该值不能是 BLOB (SQLSTATE 42815)。
- separator
- 这是一个常量表达式,用于定义在非空 string-expression 值之间使用的分隔字符串。 该表达式必须返回内置字符串、数字或日期时间数据类型的值。 如果该值不是字符串数据类型,那么在对该函数进行求值之前,它将隐式强制转换为 VARCHAR。 通过隐式强制类型转换来支持 CLOB 和 DBCLOB。 如果该值是 CLOB,那么在对该函数进行求值之前,它将隐式强制转换为 VARCHAR。 如果该值是 DBCLOB,那么在对该函数进行求值之前,它将隐式强制转换为 VARGRAPHIC。 separator 的数据类型不能是 BLOB (SQLSTATE 42815)。
分隔符可以是常量、专用寄存器、变量或者是基于常量、专用寄存器或变量的表达式(前提是此表达式不包含非确定性函数或用于执行外部操作的函数)。
- WITHIN GROUP
- 指示聚集将遵循分组集中的指定顺序。
如果未指定 WITHIN GROUP,并且在使用指定顺序的同一个 SELECT 子句中未包含其他 LISTAGG、ARRAY_AGG 或 XMLAGG,那么结果中字符串的顺序是不确定的。 如果未指定 WITHIN GROUP,并且同一个 SELECT 子句中多次出现了使用指定顺序的 XMLAGG、ARRAY_AGG 或 LISTAGG,那么将对 LISTAGG 函数调用的结果使用相同的顺序。
- ORDER BY
- 指定位于同一分组集中且在聚集中处理的行的顺序。 如果 ORDER BY 子句不能区分列数据的顺序,那么同一分组集中的行将采用任意排序顺序。
- sort-key
- 排序键可以是列名或 sort-key-expression。 如果排序键是常量,那么它不会引用输出列的位置(如查询的 ORDER BY 子句中所示);它是一个常量,这意味着没有排序键。
- ASC
- 按升序顺序处理 sort-key。 这是缺省选项。
- DESC
- 按降序顺序处理 sort-key。
结果
LISTAGG 的结果数据类型基于 string-expression 的数据类型:
结果数据类型的字符串单元与 string-expression 数据类型的字符串单元相同。
string-expression 的数据类型 | 结果数据类型和长度 |
---|---|
CHAR(n) 或 VARCHAR(n) | VARCHAR(MAX(4000, n)) |
GRAPHIC(n) 或 VARGRAPHIC(n) | VARGRAPHIC (MAX (2000 , n)) |
BINARY(n) 或 VARBINARY(n) | VARBINARY(MAX(4000, n)) |
如果使用派生大小来确定结果的大小,那么结果数据类型可以超过 VARCHAR (4000) , VARBINARY (4000) 或 VARGRAPHIC (2000) ,达到结果数据类型的最大值 。 以下示例成功生成了返回数据类型 VARCHAR(10000):
LISTAGG(CAST(NAME AS VARCHAR(10000)), ',')
如果聚集结果字符串的实际长度超过结果数据类型的最大值,那么将返回一个错误 (SQLSTATE 22001)。
结果可以为空值。 如果该函数应用于空集或集合中的所有 string-expression 值都为空值,那么结果为空值。
规则
- 如果针对 LISTAGG 指定了 DISTINCT,那么 ORDER BY 规范的 sort-key 必须与 string-expression 匹配 (SQLSTATE 42822)。 如果 string-expression 是隐式强制类型转换,那么 sort-key 必须显式包含相应的匹配强制类型转换规范。
- 如果 SELECT 子句包含 ARRAY_AGG 函数,那么同一个 SELECT 子句中对 ARRAY_AGG、LISTAGG、XMLAGG 和 XMLGROUP 函数的所有调用必须满足以下条件之一 (SQLSTATE 428GZ):
- 指定相同的顺序
- 未指定顺序
- 使带有 DISTINCT 的 LISTAGG 的 string-expression 自变量与 ARRAY_AGG 中 ORDER BY 子句的 sort-key 表达式匹配
- LISTAGG 不能用作 OLAP 规范的一部分 (SQLSTATE 42887)。
示例
生成按字母顺序排序的逗号分隔名称列表(按部门分组)。
SELECT workdept,
LISTAGG(lastname, ', ') WITHIN GROUP(ORDER BY lastname)
AS employees
FROM emp
GROUP BY workdept