Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

LISTAGG 开窗函数示例

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

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

Copy
select listagg(sellerid) within group (order by sellerid) over() from winsales; listagg ------------ 11122333344 ... ... 11122333344 11122333344   (11 rows)

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

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

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

Copy
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 (4 rows)

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

Copy
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,30004,30003 b |20001,30001,30004,30003 b |20001,30001,30004,30003 b |20001,30001,30004,30003 c |10001,20002,30007,10006 c |10001,20002,30007,10006 c |10001,20002,30007,10006 c |10001,20002,30007,10006 (11 rows)