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

SUM 开窗函数示例

累积和(运行总计)

创建按日期和销售 ID 排序的销售数量的累积 (移动) 和:

Copy
select salesid, dateid, sellerid, qty, sum(qty) over (order by dateid, salesid rows unbounded preceding) as sum from winsales order by 2,1; salesid | dateid | sellerid | qty | sum ---------+------------+----------+-----+----- 30001 | 2003-08-02 | 3 | 10 | 10 10001 | 2003-12-24 | 1 | 10 | 20 10005 | 2003-12-24 | 1 | 30 | 50 40001 | 2004-01-09 | 4 | 40 | 90 10006 | 2004-01-18 | 1 | 10 | 100 20001 | 2004-02-12 | 2 | 20 | 120 40005 | 2004-02-12 | 4 | 10 | 130 20002 | 2004-02-16 | 2 | 20 | 150 30003 | 2004-04-18 | 3 | 15 | 165 30004 | 2004-04-18 | 3 | 20 | 185 30007 | 2004-09-07 | 3 | 30 | 215 (11 rows)

有关 WINSALES 表的说明,请参阅开窗函数示例

按日期创建销售数量的累积(移动)和,按卖家 ID 对结果进行分区,并按日期和销售 ID 对该分区中的结果进行排序:

Copy
select salesid, dateid, sellerid, qty, sum(qty) over (partition by sellerid order by dateid, salesid rows unbounded preceding) as sum from winsales order by 2,1; salesid | dateid | sellerid | qty | sum ---------+------------+----------+-----+----- 30001 | 2003-08-02 | 3 | 10 | 10 10001 | 2003-12-24 | 1 | 10 | 10 10005 | 2003-12-24 | 1 | 30 | 40 40001 | 2004-01-09 | 4 | 40 | 40 10006 | 2004-01-18 | 1 | 10 | 50 20001 | 2004-02-12 | 2 | 20 | 20 40005 | 2004-02-12 | 4 | 10 | 50 20002 | 2004-02-16 | 2 | 20 | 40 30003 | 2004-04-18 | 3 | 15 | 25 30004 | 2004-04-18 | 3 | 20 | 45 30007 | 2004-09-07 | 3 | 30 | 75 (11 rows)

按顺序对行进行编号

对结果集中的所有行进行编号 (按 SELLERID 和 SALESID 列进行排序):

Copy
select salesid, sellerid, qty, sum(1) over (order by sellerid, salesid rows unbounded preceding) as rownum from winsales order by 2,1; salesid | sellerid | qty | rownum --------+----------+------+-------- 10001 | 1 | 10 | 1 10005 | 1 | 30 | 2 10006 | 1 | 10 | 3 20001 | 2 | 20 | 4 20002 | 2 | 20 | 5 30001 | 3 | 10 | 6 30003 | 3 | 15 | 7 30004 | 3 | 20 | 8 30007 | 3 | 30 | 9 40001 | 4 | 40 | 10 40005 | 4 | 10 | 11 (11 rows)

有关 WINSALES 表的说明,请参阅开窗函数示例

对结果集中的所有行进行编号,按 SELLERID 对结果进行分区,并按 SELLERID 和 SALESID 对该分区中的结果进行排序:

Copy
select salesid, sellerid, qty, sum(1) over (partition by sellerid order by sellerid, salesid rows unbounded preceding) as rownum from winsales order by 2,1; salesid | sellerid | qty | rownum ---------+----------+-----+-------- 10001 | 1 | 10 | 1 10005 | 1 | 30 | 2 10006 | 1 | 10 | 3 20001 | 2 | 20 | 1 20002 | 2 | 20 | 2 30001 | 3 | 10 | 1 30003 | 3 | 15 | 2 30004 | 3 | 20 | 3 30007 | 3 | 30 | 4 40001 | 4 | 40 | 1 40005 | 4 | 10 | 2 (11 rows)