Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。请点击 Amazon AWS 入门,可查看中国地区的具体差异

LISTAGG 开窗函数示例

以下示例使用 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 (4 rows)

以下示例返回买家 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 (4 rows)

以下示例使用 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 (4 rows)

以下示例返回每个买家 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,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)