LISTAGG 窗口函数

对于查询中的每个组，LISTAGG 窗口函数根据 ORDER BY 表达式对该组的行进行排序，然后将值串联成一个字符串。

语法

LISTAGG( [DISTINCT] expression [, 'delimiter' ] ) 
[ WITHIN GROUP (ORDER BY order_list) ] 
OVER ( [PARTITION BY partition_expression] )     

参数

DISTINCT

（可选）用于在串联之前消除指定表达式中重复值的子句。尾部空格将被忽略，因此会将字符串 'a' 和 'a ' 视为重复值。LISTAGG 将使用遇到的第一个值。有关更多信息，请参阅 尾部空格的意义。

aggregate_expression

提供要聚合的值的任何有效表达式（如列名称）。忽略 NULL 值和空字符串。

分隔符

（可选）将用于分隔串联的值的字符串常数。默认值为 NULL。

WITHIN GROUP (ORDER BY order_list)

（可选）用于指定聚合值的排序顺序的子句。仅在 ORDER BY 提供唯一排序时是确定性的。默认为聚合所有行并返回一个值。

OVER

一个指定窗口分区的子句。OVER 子句不能包含窗口排序或窗口框架规范。

PARTITION BY partition_expression

（可选）设置 OVER 子句中每个组的记录范围。

返回值

VARCHAR(MAX)。如果结果集大于最大 VARCHAR 大小（64K – 1 或 65535），则 LISTAGG 返回以下错误：

Invalid operation: Result size exceeds LISTAGG limit

示例

以下示例使用 WINSALES 表。有关 WINSALES 表的说明，请参阅窗口函数示例的示例表。

以下示例返回卖家 ID 的列表（按卖家 ID 排序）。

select listagg(sellerid) 
within group (order by sellerid)
over() from winsales;

  listagg
------------
 11122333344
...
...
 11122333344
 11122333344
   (11 rows)   

以下示例返回买家 B 的卖家 ID 的列表（按日期排序）。

select listagg(sellerid) 
within group (order by dateid)
over () as seller
from winsales
where buyerid = 'b' ;

  seller
---------
    3233
    3233
    3233
    3233

以下示例返回买家 B 的销售日期的逗号分隔的列表。

select listagg(dateid,',') 
within group (order by sellerid desc,salesid asc)
over () as dates
from winsales
where buyerid  = 'b';

             dates                                      
-------------------------------------------
2003-08-02,2004-04-18,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-04-18,2004-02-12

以下示例使用 DISTINCT 返回买家 B 的唯一销售日期的列表。

select listagg(distinct dateid,',') 
within group (order by sellerid desc,salesid asc)
over () as dates
from winsales
where buyerid  = 'b';

           dates
--------------------------------
2003-08-02,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-02-12

以下示例返回每个买家 ID 的销售 ID 的逗号分隔的列表。

select buyerid, 
listagg(salesid,',')
within group (order by salesid)
over (partition by buyerid) as sales_id
from winsales
order by buyerid;

+---------+-------------------------+
| buyerid |        sales_id         |
+---------+-------------------------+
| a       | 10005,40001,40005       |
| a       | 10005,40001,40005       |
| a       | 10005,40001,40005       |
| b       | 20001,30001,30003,30004 |
| b       | 20001,30001,30003,30004 |
| b       | 20001,30001,30003,30004 |
| b       | 20001,30001,30003,30004 |
| c       | 10001,10006,20002,30007 |
| c       | 10001,10006,20002,30007 |
| c       | 10001,10006,20002,30007 |
| c       | 10001,10006,20002,30007 |
+---------+-------------------------+